Re: Optimize Query for slow responce

In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) says: > > 26-Oct-1994 > >I can not understand the Informix Query Optimizer ! >--------------------------------------------------- > > >I have a fine large table and I create a query, where I select all fields from >the table. To lookup the records I have an unique index on a field called >'gd_kontonr' and as a second want, I have a filter 'gd_prod_kode < 90000'. > >select * from ks_gdkonto >where gd_kontonr = "0003573508" >and gd_prod_kode < 90000;> >This one takes loooooooong time to run.................... > > >select * from ks_gdkonto >where gd_kontonr = "0003573508";> >This one is very very fast, you can't start it before it is finished, but >I want to have 'gd_prod_kode < 90000' on the where part. > > > >Now look at the Optimizer Query Plan: > > >QUERY: (LOW) >------ >select * from ks_gdkonto >where gd_kontonr = "0003573508" >and gd_prod_kode < 90000> > >Estimated Cost: 2 >Estimated # of Rows Returned: 1 > >1) kundedb.ks_gdkonto: INDEX PATH > > Filters: kundedb.ks_gdkonto.gd_kontonr = '0003573508' INFORMIX uses a filter , NOT an index (i.e ks_gdk1) ! > > (1) Index Keys: gd_prod_kode > Upper Index Filter: kundedb.ks_gdkonto.gd_prod_kode < 90000 INFORMIX uses the index on gd_prod_kode ! > > > >It is totally upside down of what I expected ! > > >create unique index "kundedb".ks_gdk1 on "kundedb".ks_gdkonto (gd_kontonr); >create index "kundedb".ks_gdk2 on "kundedb".ks_gdkonto (gd_aftale_type); >create index "kundedb".ix454_4 on "kundedb".ks_gdkonto (gd_prod_kode); >create index "kundedb".ks_gdk4 on "kundedb".ks_gdkonto (gd_tanktype); >create index "kundedb".ks_gdk5 on "kundedb".ks_gdkonto (gd_fast_kfaktor); > > > >Why can't the Optimizer use a better Query Plan on this simple select ? > It seems that you 'confuse' the optimizer with too much indices. Sometimes less is more ! Try the following: DROP INDEX ix454_4;and run your query (with explain on) again. You will see a better result. Some background about optimizers: If there is a situation with two independent indices (like in your example ks_gdk1 and ix454_4) optimizers choose the one with less 'index depth'. ######################################################## # ____ ____ ____ # # | _ | | | | | Michael Lackowski # # | (_)| | | | | # # | ___| _| _|__| _| IVU GmbH # # |( (_ \\ ( (_ | (_ | Bundesallee 129 # # | | | \\ \\/ /| | | | D-12161 Berlin # # | | | \\ /|| |__| | # # |(____) |\\_/ |\\________) phone : +49 30 850006-37 # # | | | | | | fax : +49 30 850006-66 # # |____| |____| |____| e-mail: ml@ivu-berlin.de # # # ########################################################