Re: Informix Online 5.0 Query Optimizer

Now here's a juicy one! I'm going to ask a lot of questions here, but I'm posting rather than mailing in the hope that it will generate an interesting discussion on the optimiser: In <236hvoINNqrv@emory.mathcs.emory.edu> jmcclees@hpbs2670.boi.hp.com (Jeff McClees) writes: >We have a problem concerning Informix Online 5.0 query optimization. Informix >seems to be choosing extremely inefficient paths for no apparent reason. >The part_ref table originally had only one index, a composite of partnumber and >option. When a query was performed that joined to this table, it took an >excessive amount of time. We added another index to the part_ref table, this How long? >time only on the partnumber field. When the query that joined to part_ref was >run again, Informix then used the original partnumber/option index, and ran >in several seconds. >This would be understandable if the new optimized query used the new index. >However, according to sqexplain.out, it didn't even look at it. Note in the Misleading. Just because it chose not to use it, does not necessarily mean that it did not look at it. It seems unlikely that it is actually ignoring indexes, unless you have some kind of catalog corruption. Presumably tbcheck gives the catalogs a clean bill of health? >comparison below how the same query was organized differently (refer to the >attached sqexplain.out listing for details). Also note that the query >recognized and used the underlying table's origdatetime index. >Below is a summary of the queries and the sqlexplain.out before and after >adding an index on partnumber. >1) Query: > select * from sfdmdefect2_vu where > origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" > Before adding additional index on partnumber (part_ref is indexed by the > composite partnumber and option only) - > Table Path Index Used > --------------- --------------- --------------------- > 1 part_ref sequential scan n/a > 2 serialpart index partnumber and option > 3 gljjtest1 index serialnumber > 4 serialcomponent index componentsn > 5 serialcomponent index printersn > **** note that the gljjtest1.origdatetime column is indexed by isn't > used by Informix. >2) Query: > select * from sfdmdefect2_vu where > origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00"> > After adding additional index on partnumber (part_ref is indexed by partnumber > AND the composite partnumber and option) - > Table Path Index Field(s) > --------------- --------------- --------------------- > 1 gljjtest1 index origdatetime > 2 serialpart index serialnumber > 3 part_ref index partnumber and option > 4 serialcomponent index componentsn > 5 serialcomponent index printersn > **** note that in this case Informix uses the gljjtest1.origdatetime > indexed column (and we are happy...) >- Informix engine version 5.0 >- Update Statistics was run prior to performing the query >________________________________________________________________________________ >--------------------------------DETAILS----------------------------- >First QUERY: >----------- >select * from sfdmdefect2_vu where>origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" >Estimated Cost: 2447 Now this cost is lower than Query2, which means that OnLine expects it to execute (about 30%) faster, and that it *should* therefore be chosen over the strategy used in Query2 >Estimated # of Rows Returned: 5 It would be useful to know how many rows actually are returned (also how many rows in each of the underlying tables). >1) informix.part_ref: SEQUENTIAL SCAN How come there is no primary key on this table? Is part_ref_ix a candidate? How many rows/blocks is it electing to scan? (SELECT nrows, npused FROM systables WHERE tabname = "part_ref";) Does it still choose this strategy with SET OPTIMIZATION LOW? >2) informix.serialpart: INDEX PATH >select * from sfdmdefect2_vu where>origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" > (1) Index Keys: partnumber option > Lower Index Filter: (informix.serialpart.option = > informix.part_ref.option AND > informix.serialpart.partnumber = > informix.part_ref.partnumber ) Reasonable enough given the starting premise. It has presumably decided that it is more efficient to use part_ix for the join than part_ref_ix, which I assume means that serialpart is large relative to part_ref. >3) informix.gljjtest1: INDEX PATH > Filters: (informix.gljjtest1.origdatetime >= > '1993-06-01 00:00:00' AND informix.gljjtest1.origdatetime <= > '1993-06-01 06:00:00' ) > (1) Index Keys: serialnumber > Lower Index Filter: informix.gljjtest1.serialnumber = > informix.serialpart.serialnumber Having gone this route, I would expect ixgj2 to be a better choice than ixgj3. Again, no primary key! >4) informix.serialcomponent: INDEX PATH > (1) Index Keys: componentsn > Lower Index Filter: informix.serialcomponent.componentsn = > informix.gljjtest1.serialnumber Fair enough. >5) informix.serialcomponent: INDEX PATH > Filters: (informix.serialcomponent.componentsn MATCHES 'HDR*' OR > informix.serialcomponent.componentsn MATCHES 'GDR*' ) > (1) Index Keys: printersn > Lower Index Filter: informix.serialcomponent.printersn = > informix.serialcomponent.printersn I would expect ix131_3 to be more efficient than ix131_1. >Second QUERY: >------------ >select * from sfdmdefect2_vu where>origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" >Estimated Cost: 3583 Now it has decided that there is no plan with a cost of 2477 available to it. So it must have revised it's estimate of the cost of the Query1 strategy. I think this must be a bug, as otherwise it would suggest that there is new data in sysindexes.nunique for ixgj0 or syscolumns.colmin,colmax for part_ref.partnumber. The question is: Is the bug in the optimiser or UPDATE STATISTICS? Without seeing all the values in the optimisation columns in the catalogs, it is difficult to surmise why it originally thought the Query1 route was cheaper. And even more difficult to surmise why the new index might make a difference. What values are in the optimisation columns in systables, syscolumns and sysindexes? Did they change for Query2? Casual inspection of other databases would indicate that sysindexes.nunique should be equal for part_ref_ix and ixgj0. Is this in fact the case? @@