Serious Flaws in 7.23's Optimizer - Not using indexes
In a nutshell:
The Optimizer does not usee indexes even if EVERY join is an indexed
field.
Lots of problems in other examples including every time I use EXISTS it
does a SEQUENTIAL search where an index is available.
Informix 5 did not have these problems.
In the following SQL:
The Optimizer does a SEQUENTIAL SCAN on the largest table, dunau, when
it should use the index on dun
and the index on id
" " " " tel
" " " " fax
" " " " name
" " " " zip
select
DISTINCT
a.id,
max(b.id)
from
dunsoft a,
OUTER
( dunau b,
dunsoldto c )
where
(
( a.dun != '000000000'
and b.dun != '000000000'
and b.dun = a.dun )
or
( b.name[1,15] = a.name[1,15]
and b.zip = a.zip )
or b.tel = a.tel
or b.fax = a.fax
)
and c.id = b.id
group by a.id"
GIVES:
Estimated Cost: 36838924 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Estimated # of Rows Returned: 1
1) eadmin.a: INDEX PATH
(1) Index Keys: id
2) eadmin.b: SEQUENTIAL SCAN
Filters: (((((eadmin.a.dun != '000000000' AND eadmin.b.dun !=
'000000000' )
AND eadmin.b.dun = eadmin.a.dun ) OR (eadmin.b.name[1,15] =
eadmin.a.name[1,15]
AND eadmin.b.zip = eadmin.a.zip ) ) OR eadmin.b.tel =
eadmin.a.tel ) OR eadmin.b
.fax = eadmin.a.fax )
3) eadmin.c: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: eadmin.c.id = eadmin.b.id
----------------
FILE SIZES:
dunsoft 3,400
dunau 88,000
dunsoldto 67,000
INDEXES: All columns in the joins, each its own index
UPDATE STATISTICS:
Ran HIGH on all columns
update statistics HIGH for table dunau ;
update statistics HIGH for table dunsoft ;
update statistics HIGH for table dunsoldto ;
update statistics HIGH for table dunsoft (id);
update statistics HIGH for table dunsoft (dun)
update statistics HIGH for table dunau (id) ;
update statistics HIGH for table dunau (dun) ;
update statistics HIGH for table dunsoldto (id);
update statistics HIGH for table dunau (name) ;
update statistics HIGH for table dunau (zip) ;
update statistics HIGH for table dunau (tel) ;
update statistics HIGH for table dunau (fax) ;
update statistics HIGH for table dunsoft (name);
update statistics HIGH for table dunsoft (zip);
update statistics HIGH for table dunsoft (tel);
update statistics HIGH for table dunsoft (fax);
Tried MEDIUM on all columns and got a minimal reduction:
Estimated Cost: 36780192 (weird)
Tried LOW on all columns and same as MEDIUM
Even:
update statistics LOW
for table dunau DROP DISTRIBUTIONS;
update statistics LOW
for table dunsoft DROP DISTRIBUTIONS;
update statistics LOW
for table dunsoldto DROP DISTRIBUTIONS; no change
---------------------------------
Only got it to work by Totally Converting to UNIONS
(not Teamsters):
QUERY:
---------------
select DISTINCT a.id, max(b.id)
from dunsoft a,
OUTER ( dunau b, dunsoldto c )
where a.dun != '000000000'
and b.dun != '000000000'
and b.dun = a.dun
and c.id = b.id
group by a.id
---------
UNION
select DISTINCT a.id, max(b.id)
from dunsoft a,
OUTER ( dunau b, dunsoldto c )
where ( a.dun = '000000000'
or b.dun = '000000000'
or b.dun != a.dun )
and b.tel = a.tel
and c.id = b.id
group by a.id
---------
UNION
select DISTINCT a.id, max(b.id)
from dunsoft a,
OUTER ( dunau b, dunsoldto c )
where ( ( a.dun = '000000000'
or b.dun = '000000000'
or b.dun != a.dun )
and b.tel != a.tel
)
and
( b.name[1,15] = a.name[1,15]
and b.zip = a.zip )
and c.id = b.id
group by a.id
Estimated Cost: 58853 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Estimated # of Rows Returned: 3
1) eadmin.a: INDEX PATH
Filters: eadmin.a.dun != '000000000'
(1) Index Keys: id
2) eadmin.b: INDEX PATH
Filters: eadmin.b.dun != '000000000'
(1) Index Keys: dun
Lower Index Filter: eadmin.b.dun = eadmin.a.dun
3) eadmin.c: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: eadmin.c.id = eadmin.b.id
Union Query:
------------
1) eadmin.a: INDEX PATH
(1) Index Keys: id
2) eadmin.b: INDEX PATH
Filters: ((eadmin.a.dun = '000000000' OR eadmin.b.dun = '000000000' )
OR ead
min.b.dun != eadmin.a.dun )
(1) Index Keys: tel
Lower Index Filter: eadmin.b.tel = eadmin.a.tel
3) eadmin.c: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: eadmin.c.id = eadmin.b.id
Union Query:
------------
Temporary Files Required For: Group By
1) eadmin.a: SEQUENTIAL SCAN
2) eadmin.b: INDEX PATH
Filters: (eadmin.b.zip = eadmin.a.zip AND (eadmin.b.tel !=
eadmin.a.tel AND
((eadmin.a.dun = '000000000' OR eadmin.b.dun = '000000000' ) OR
eadmin.b.dun !=
eadmin.a.dun ) ) )
(1) Index Keys: name
Lower Index Filter: eadmin.b.name[1,15] = eadmin.a.name[1,15]
3) eadmin.c: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: eadmin.c.id = eadmin.b.id
Wish I were back with v5
Alan Cowan