Informix Optimizer Ignores Index

Hi to all, I faced an optimizer problem in Informix. Why does Informix _not_ use an index which would increase the performance very much? The query is select kvnnr, mbkvnr_k from mbkvnr order by mbkvnr_k This is the index which shoud be used: mbkvnr_ges_auf informix dupls/No btree mbkvnr_k Update Statistics has been done. If I omitt kvnnr in the select-list, then Informix does use the Index. Informix Version is 11.50.FC2 The Informix server and dbaccess both run on Microsoft Windows Server 2003 R2 Standart x64 Edition Servie Pack 2 I know that Optimizer Directives are a workarround for this problem, so we ar not in hurry for an answer. I just would like to know, why Informix does not use this index. Thanks Mathias Cukina

Because the query has no WHERE clause filters so the optimizer calculates that it will have to read all of the tables data pages anyway, so it eliminates the overhead of reading the index pages also because it can sort the data so fast that it is a better choice to scan the data pages sequentially reading all rows than to read the index pages also then read the data rows 'randomly' having to visit each data page multiple times. If you eliminate the kvnnr column from the query then the optimizer decides that it can get all of the data it needs from the many fewer index pages without having to read ANY data pages at all, so it uses the index performing an index only scan. Run the query with and without the optimizer hints and with and without the second column under SET EXPLAIN and look at the costs for all versions of the query and time each also and then decide if the optimizer is doing its job correctly or not. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jan 15, 2010 at 6:03 AM, Mathias <cukina@comline.de> wrote: > Hi to all, > > I faced an optimizer problem in Informix. > Why does Informix _not_ use an index which would increase the > performance very much? > > > The query is > select kvnnr, mbkvnr_k > from mbkvnr > order by mbkvnr_k> > This is the index which shoud be used: > mbkvnr_ges_auf informix dupls/No btree mbkvnr_k > > Update Statistics has been done.> > > If I omitt kvnnr in the select-list, then Informix does use the Index. > > > Informix Version is 11.50.FC2 > > The Informix server and dbaccess both run on > Microsoft Windows Server 2003 R2 > Standart x64 Edition > Servie Pack 2 > > I know that Optimizer Directives are a workarround for this problem, > so we ar not in hurry for an answer. > > I just would like to know, why Informix does not use this index. > > Thanks > Mathias Cukina > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hello Art, thank you very much for your explanations. You are right, Informix indeed ist faster with this selects, if it does a full table scan. We had been in this error: As we did test this statements with dbaccess we estimatet the time they took from hitting run until we saw the first results. Unfortunately we forgot, these were just the _first_ results and 1050440 more rows should follow. The index path is much faster, if it ist the goal to get the first results quickly. This we can achieve by the optimizer directive +FIRST_ROWS. But this is not or over all goal. So now we know, the full table scan which informix selects is the best solution. Thanks from Germany Mathias Cukina