Optimizer of Online 7.22 selects wrong index
--------------5A1C91DF9FE1AC5087D28943
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
I am running Informix Online 7.22 on Bull Escala (=J30 or J40). In some
cases the optimizers chooses a wrong index for a often used query query.
Here is one case:
My table MSTUECK has about 745000 rows and several indexes, some of them
having the first two or three columns in common. My query (WHERE columns
match ORDER BY columns and the columns of one index) runs ok as long as
I do not run an UPDATE STATISTICS against this table. It delivers about
30 rows within 0.x seconds.
After the execution af an UPDATE STATISTICS (whatever kind, with or
without specifying columns) the optimizer get confused and makes a
SEQUENTIAL SCAN followed by a SORT. Now the same query (with the same
result) takes more than 5 minutes.
Playing with OPTCOMPIND does not change the behaviour. Also, I cannot
drop some indexes as they reflect the selection criteria/order criteria
of other important queries. Up to now, my only bypass is to avoid
running an UPDATE STATISTICS against the table (also a general UPDATE
STATISTICS without a table specification must not be run).
Does anyone have an idea?
#################
Here are the index definitions:
#
create unique index c_MSTUECK on MSTUECK (db__seq asc );
create unique index MSTUECK on MSTUECK (
st_firma asc ,
st_snr asc ,
st_stufe asc ,
st_lfd asc ,
st_posnr asc )
;#
create unique index MSTUECK2 on MSTUECK (
st_firma asc ,
st_zeichnr asc ,
st_snr asc ,
st_stufe asc ,
st_lfd asc ,
st_posnr asc , db__seq asc )
;#
create unique index MSTUECK3 on MSTUECK (
st_firma asc ,
st_tnr asc ,
st_snr asc ,
st_stufe asc ,
st_lfd asc ,
st_posnr asc , db__seq asc )
;#
create unique index MSTUECK4 on MSTUECK (
st_firma asc ,
st_matnr asc ,
st_snr asc ,
st_stufe asc ,
st_lfd asc ,
st_posnr asc , db__seq asc )
;#
create unique index MSTUECK5 on MSTUECK (
st_firma asc ,
st_snr asc ,
st_stufe asc ,
st_lfd asc ,
st_kost asc ,
st_lgort asc ,
st_posnr asc , db__seq asc )
;#
create unique index MSTUECK6 on MSTUECK (
st_firma asc ,
st_snr asc ,
st_lnr asc ,
st_tg asc ,
st_zeichnr asc ,
st_stufe asc ,
st_lfd asc ,
st_posnr asc , db__seq asc )
;#
create unique index MSTUECK8 on MSTUECK (
st_firma asc ,
st_snr asc ,
st_kost asc ,
st_lgort asc ,
st_stufe asc ,
st_lfd asc ,
st_posnr asc )
;
#######################
This is the query:
set explain on;
SELECT st_firma , st_snr , st_stufe , st_lfd , st_posnr , st_stufeh ,
st_lfdh , st_posnrh , st_stufea , st_lfda , st_reserviert ,
st_zeichnr , st_tnr , st_menge , st_rmenge , st_restmenge , st_vdatum
,
st_matnr , st_bem , st_kost , st_lgort , st_kopte , st_lnr , st_tg ,
st_status , st_mengepe , db__seq FROM MSTUECK
WHERE st_firma = 1 AND st_zeichnr = "A113" AND st_snr > 1223
ORDER BY st_firma ASC ,st_zeichnr ASC ,st_snr ASC ,st_stufe ASC ,
st_lfd ASC ,st_posnr ASC ,db__seq ASC;
set explain off;
#########################
This is the result of the optimizer:
====> Table loaded, no update statistics executed
Beginn: Thu Jun 5 10:28:48 MEST 1997
QUERY:
------
SELECT st_firma , st_snr , st_stufe , st_lfd , st_posnr , st_stufeh ,
st_lfdh , st_posnrh , st_stufea , st_lfda , st_reserviert ,
st_zeichnr , st_tnr , st_menge , st_rmenge , st_restmenge , st_vdatum
,
st_matnr , st_bem , st_kost , st_lgort , st_kopte , st_lnr , st_tg ,
st_status , st_mengepe , db__seq FROM MSTUECK
WHERE st_firma = 1 AND st_zeichnr = "A113" AND st_snr > 1223
ORDER BY st_firma ASC ,st_zeichnr ASC ,st_snr ASC ,st_stufe ASC ,
st_lfd ASC ,st_posnr ASC ,db__seq ASC
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) lein.mstueck: INDEX PATH
(1) Index Keys: st_firma st_zeichnr st_snr st_stufe st_lfd st_posnr
db__seq
Lower Index Filter: (lein.mstueck.st_firma = 1.0000000000000000
AND (lein.mstueck.st_zeichnr = 'A113' AND lein.mstueck.st_snr >
1223.0000000000000000 ) )
Ende: Thu Jun 5 10:28:51 MEST 1997
====> UPDATE STATISTICS executed
Beginn: Thu Jun 5 10:35:40 MEST 1997
QUERY:
------
SELECT st_firma , st_snr , st_stufe , st_lfd , st_posnr , st_stufeh ,
st_lfdh , st_posnrh , st_stufea , st_lfda , st_reserviert ,
st_zeichnr , st_tnr , st_menge , st_rmenge , st_restmenge , st_vdatum
,
st_matnr , st_bem , st_kost , st_lgort , st_kopte , st_lnr , st_tg ,
st_status , st_mengepe , db__seq FROM MSTUECK
WHERE st_firma = 1 AND st_zeichnr = "A113" AND st_snr > 1223
ORDER BY st_firma ASC ,st_zeichnr ASC ,st_snr ASC ,st_stufe ASC ,
st_lfd ASC ,st_posnr ASC ,db__seq ASC
Estimated Cost: 64797
Estimated # of Rows Returned: 99318
Temporary Files Required For: Order By
1) lein.mstueck: INDEX PATH
Filters: lein.mstueck.st_zeichnr = 'A113'
(1) Index Keys: st_firma st_snr st_stufe st_lfd st_posnr
Lower Index Filter: (lein.mstueck.st_firma = 1.0000000000000000
AND lein.mstueck.st_snr > 1223.0000000000000000 )
Ende: Thu Jun 5 10:40:46 MEST 1997
#######
Thanks for help
--------------5A1C91DF9FE1AC5087D28943
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<HTML>
I am running Informix Online 7.22 on Bull Escala (=J30 or J40). In some
cases the optimizers chooses a wrong index for a often used query query.
Here is one case:
<P>My table MSTUECK has about 745000 rows and several indexes, some of
them having the first two or three columns in common. My query (WHERE columns
match ORDER BY columns and the columns of one index) runs ok as long as
I do not run an UPDATE STATISTICS against this table. It delivers about
30 rows within 0.x seconds.
<BR>After the execution af an UPDATE STATISTICS (whatever kind, with or
without specifying columns) the optimizer get confused and makes a SEQUENTIAL
SCAN followed by a SORT. Now the same query (with the same result) takes
more than 5 minutes.
<BR>Playing with OPTCOMPIND does not change the behaviour. Also, I cannot
drop some indexes as they reflect the selection criteria/order criteria
of other important queries. Up to now, my only bypass is to avoid running
an UPDATE STATISTICS against the table (also a general UPDATE STATISTICS
without a table specification must not be run).
<P>Does anyone have an idea?
<P>#################
<BR>Here are the index definitions:
<P><TT>#</TT>
<BR><TT>create unique index c_MSTUECK on MSTUECK (db__seq asc );</TT>
<BR><TT>create unique index MSTUECK on MSTUECK (</TT>
<BR><TT> st_firma asc ,</TT>
<BR><TT> st_snr asc ,</TT>
<BR><TT> st_stufe asc ,</TT>
<BR><TT> st_lfd asc ,</TT>
<BR><TT> st_posnr asc )</TT>
<BR><TT> ;</TT>
<BR><TT>#</TT>
<BR><TT>create unique index MSTUECK2 on MSTUECK (</TT>
<BR><TT> st_firma