Re: Optimizer Issues with 10.00.xC8
The poster hit an optimizer problem where Informix picked a poor index on a very large (185 million row, 17-way fragmented) table. He found it wasn't caused by the 10.00.xC8 upgrade, since FC6 behaves the same, and that it only reproduces with the full data set. He also noticed colmin/colmax values for ColA being blanked out after UPDATE STATISTICS HIGH on that column. Art Kagel explained that without distributions the older optimizer rules rely on index depth, distinct counts and colmin/colmax, which favours the composite unique index. Fernando suggested sending the statistics to support rather than the data. No fix is recorded; the thread ends with a PMR opened with IBM.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
A couple of updates on this. First, I've been able to replicate the problem on 10.00.FC6, which tells me that the problem is not specific to the FC8 upgrade. But I've only been able to do so with essentially a full set of data. If I create a table that contains only a few columns, but all of the rows (including the key data) I get this behavior. Second, I'm noticing an interesting quirk. If I just run UPDATE STATISTICS LOW FOR TABLE foo, then I get colmin and colmax values for both ColA and ColB (both of which, as you'll recall) head indexes. But when I then do the UPDATE STATISTICS HIGH FOR foo(ColA), its colmin and colmax values are blanked out. ColB retains its colmin and colmax values. And no, there aren't any null values in either column. So I have no idea what's going on there. In any case, I have a replicable test case, but IBM support isn't going to like it: It involves creating a table with 185 million rows (a smaller subset won't do it), compiling and ESQL/C program and then running that program. Oh, and the table's fragmented 17 ways, although I'm not sure if that makes a difference. Now that I think on it, though, the index on (ColA,ColC) is also fragmented, and I haven't tested whether or not that's factoring in to the optimizer's decision. It would be interesting to find out... Art S. Kagel (Oninit) wrote: > Makes sense to me. Without distributions it uses the older dumber > optimizer algorithms which are dependend on the relative depth and > number of unique values of the indexes (stored in sysindices) and the > colmin & colmax values (stored in syscolumns) which are the second > smallest and second greatest values for the column. Since zero is > likely the smallest value of colC and the number of unique values for > the colC only index is relatively low compared to the number of unique > keys in the unique index on colA & colB (which is somewhere shy of > 200million if I remember correctly) the colA, colB index is clearly the > more selective according to the algorithms used in the absence of > distributions.
tgirsch wrote: > A couple of updates on this. First, I've been able to replicate the > problem on 10.00.FC6, which tells me that the problem is not specific to > the FC8 upgrade. But I've only been able to do so with essentially a > full set of data. If I create a table that contains only a few columns, > but all of the rows (including the key data) I get this behavior. > > Second, I'm noticing an interesting quirk. If I just run UPDATE > STATISTICS LOW FOR TABLE foo, then I get colmin and colmax values for > both ColA and ColB (both of which, as you'll recall) head indexes. But > when I then do the UPDATE STATISTICS HIGH FOR foo(ColA), its colmin and > colmax values are blanked out. ColB retains its colmin and colmax > values. And no, there aren't any null values in either column. So I > have no idea what's going on there. > > In any case, I have a replicable test case, but IBM support isn't going > to like it: It involves creating a table with 185 million rows (a > smaller subset won't do it), compiling and ESQL/C program and then > running that program. Oh, and the table's fragmented 17 ways, although > I'm not sure if that makes a difference. > > Now that I think on it, though, the index on (ColA,ColC) is also > fragmented, and I haven't tested whether or not that's factoring in to > the optimizer's decision. It would be interesting to find out... > > Art S. Kagel (Oninit) wrote: >> Makes sense to me. Without distributions it uses the older dumber >> optimizer algorithms which are dependend on the relative depth and >> number of unique values of the indexes (stored in sysindices) and the >> colmin & colmax values (stored in syscolumns) which are the second >> smallest and second greatest values for the column. Since zero is >> likely the smallest value of colC and the number of unique values for >> the colC only index is relatively low compared to the number of unique >> keys in the unique index on colA & colB (which is somewhere shy of >> 200million if I remember correctly) the colA, colB index is clearly >> the more selective according to the algorithms used in the absence of >> distributions. Tech support will probably not need to create the table. You may offer to send them the statistics you have... It should be enough. They probably have a script to extract the info needed... Picking Cicero's sayings ('It is not enough for Caesar's wife to be honest; she must look honest.'), in this case it's probably enough to look big, but it doesn't have to be big :) Regards, -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Fernando Nunes wrote: > > Tech support will probably not need to create the table. > You may offer to send them the statistics you have... It should be enough. > They probably have a script to extract the info needed... > > Picking Cicero's sayings ('It is not enough for Caesar's wife to be > honest; she must look honest.'), in this case it's probably enough to > look big, but it doesn't have to be big :) > I opened a PMR today with all of the details on my test case. We'll see if it gets anywhere. Since I can now replicate the bug in FC6 as well as FC8, it's not anything to do with the upgrade, as I'd originally suspected.