Query Optimizer

What follows are some excerts from my previous posting and an update: > > In running the query described below on my OLD version 2.10.03K performance > was completely acceptable. On version 4.0, ouch!!!! Queries that took seconds > were now taking lots........ of minutes like 15,20,25 minutes. > > The one thing that you can notice is that with the set explain on, > all of the queries (I deleted the other two to save space here) show the > first level query as a SEQUENTIAL SCAN, the second level as INDEX PATH. > > In this example the optimizer always chooses a SEQUENTIAL SCAN of a table > over an INDEX SCAN. They tell me this is not a bug. In my case with a full > database it returns thousands of rows from a squentional scan only to > eliminate them by choosing a few dozen rows from an indexed scan. > > **** Sample tables ***** > > create table pinfo ( > pno char(8) not null, > pname char(30), > ptype char(1));> > create table pn ( > caseno char(8) not null, > pno char(8), > ptype char(1));> > create index xpn1 on pn (caseno); > create index xpn2 on pn (pno);> > create unique index xpinfo on pinfo (pno); > create index xpinfo1 on pinfo (pname);> **** ***** > > > QUERY: From set explain in Ver 4.0 > ------ > select caseno, pn.pno, pname, pn.ptype from pinfo, pn > where pn.ptype = "M" and pname matches "ANDERSON, K*" > and pinfo.pno = pn.pno;> > Estimated Cost: 185 > Estimated # of Rows Returned: 52 > > 1) kuhn.pn: SEQUENTIAL SCAN > > Filters: kuhn.pn.ptype = 'M' > > 2) kuhn.pinfo: INDEX PATH > > Filters: kuhn.pinfo.pname MATCHES 'ANDERSON, K*' > > (1) Index Keys: pno > Lower Index Filter: kuhn.pinfo.pno = kuhn.pn.pno > ***************************************************************** UPDATE ON PERFORMANCE Informix Tech Support sent me a document (TechInfo #599) which described how the optimizer works. Document was translated from German and orginally appeared in a Informix German newsletter. One trick was to include a dummy filter. In my case the pinfo table actually has other fields not described above. I used a date field labeled "born". Everyone in my database was born after 1776. Wow! what a difference. Definitely worked better than Geritol. QUERY: ------ select caseno, pn.pno, pname, pn.ptype from pinfo, pn where pn.ptype = "M" and pname matches "ANDERSON, K*" and pinfo.pno = pn.pno and pinfo.born > date("01-01-1776"); Estimated Cost: 27935 Estimated # of Rows Returned: 9734 1) kuhn.pinfo: INDEX PATH Filters: kuhn.pinfo.born > DATE ('01-01-1776' ) (1) Index Keys: pname Lower Index Filter: kuhn.pinfo.pname MATCHES 'ANDERSON, K*' 2) kuhn.pn: INDEX PATH Filters: kuhn.pn.ptype = 'M' (1) Index Keys: pno Lower Index Filter: kuhn.pn.pno = kuhn.pinfo.pno Michael J. Kuhn Consultant phone :301-254-7060 e-mail :c/o Baltimore Rh Typing Laboratory, Inc. phone :301-225-9595 uunet!rhlab!kuhn