(no subject)

Here is a bit of performance information I would like to pass on. Platform: 3B2/400 Unix V 3.1 OLD Informix: 2.10.03K NEW Informix: 4.0 In running the query described below on my OLD version performance was completely acceptable. On version 4.0, ouch!!!! Queries that took seconds were now taking lots........ of minutes like 15,20,25 minutes. After sending tech support a sample database with data, they also observed the same slow query. The result is that: 1. This is not a bug. 2. It is a problem with the optimizer picking the wrong thing. 3. It is only on the 3B2's. 4. It will probably not be fixed until version 5.0 5. It can probably be fixed by splitting the query into to two parts, Selecting the "matches" into a temporary table and then selecting from that table. 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. The other way around it would choose dozens of row from an INDEX SCAN and then eliminate them based on a join and a SEQUENTIAL SCAN. REPEAT: This is not a bug only a problem with the optimizer picking the wrong thing. **** 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