Re: Optimizer of Online 7.22 selects wrong index

Scott Campbell wrote: > > Helmut Leininger wrote: > > > > I am running Informix Online 7.22 on Bull Escala (=J30 or J40). In > > some cases the optimizers chooses a wrong index for a often used query > > query. Here is one case: > > > > My table MSTUECK has about 745000 rows and several indexes, some of > > them having the first two or three columns in common. My query (WHERE > > columns match ORDER BY columns and the columns of one index) runs ok > > as long as I do not run an UPDATE STATISTICS against this table. It > > delivers about 30 rows within 0.x seconds. > > After the execution af an UPDATE STATISTICS (whatever kind, with or > > without specifying columns) the optimizer get confused and makes a > > SEQUENTIAL SCAN followed by a SORT. Now the same query (with the same > > result) takes more than 5 minutes. > > Playing with OPTCOMPIND does not change the behaviour. Also, I cannot > > drop some indexes as they reflect the selection criteria/order > > criteria of other important queries. Up to now, my only bypass is to > > avoid running an UPDATE STATISTICS against the table (also a general > > UPDATE STATISTICS without a table specification must not be run).> > > > Does anyone have an idea? > > > [SNIP] > contains all of the columns in your ORDER BY clause. I wouldn't be > overly concerned with performing a final sort on a result set of only 30 > rows. For this particular query, you should utilize an index comprised No. Helmut, I know your problem. I have seen it here and investigated extensively. First the description of the problem then a set of possible solutions. This will be long bear with me. The problem is that you have 745,000 rows in the table, your query contains an ORDER BY clause that matches an index. Several indexes contain the filter columns. The optimizer chooses the 'correct' index if you drop distributions but the 'wrong' one if you update stats high, or even medium on the entire table. There are a few thousand rows which match the filter values but you are only interested in the first 30 (or at least you need those first few quickly). Am I telling your life? Have you tried timing the query in DBACCESS with and without DISTRIBUTIONS fetching all matching rows? I think that you will find that the optimizer is actually choosing the 'right' index with DISTRIBUTIONS to meet its goal of minimizing total cost of fetching all rows! The query will fetch the full selected set faster with DISTRIBUTIONS and doing the sort than not. Read on. We have a similar problem: Table with 10Million rows. Query ultimately matches ~10,000 rows. Query with ORDER BY and matching index but other similar indexes. I need 1st 18 rows within 10 seconds or less. Informix 5.06 uses ORDER BY index and returns 18 rows in <2secs. Vers 7.13 without DISTRIBUTIONS uses ORDER BY index and returns 18 rows in <1sec. Ver 7.13 with DISTRIBUTIONS from statistics compiled according to the 7.13 release notes uses another index and sorts and returns 18 rows in 12.5 seconds (TOO LONG!) Ver 7.13 with DISTRIBUTIONS from statistics MEDIUM uses a third index and still sorts but returns 18 rows in 13.5 seconds (EVEN SLOWER!). However! Ver 5.06 returns all 10,000 rows in 45 seconds using the ORDER BY index. Ver 7.13 without DISTRIBUTIONS returns all 10,000 rows in 43 seconds using the ORDER BY index, no sort. Ver 7.13 with MEDIUM DISTRIBUTIONS returns all 10,000 rows in 14 seconds using that third index and perfoming a sort. Ver 7.13 with mixed MEDIUM and HIGH statistics, per the release notes, uses the second index and sorts and returns all 10,000 rows in 14 seconds. So the 7.13 optimizer, provided with complete statistics, uses the "correct" index and does the best job to reach its goal of optimizing TOTAL COST and the total query. My problem is that I want to optimize the cost of returning the first 18 rows! The sort takes <0.5 seconds it is the time needed to find 10,000 rows so that the sort can start that is causing the trouble and you can see that using the ORDER BY matching index takes even longer! Resetting the optimizer's goal from TOTAL COST to cost of FIRST FETCH is a scheduled feature enhancement in R7.3 which Bloomberg lobbied long an hard for. This will cause the optimizer to select the 'right' index for our needs. You're all very welcome. Meantime what to do? Two possibilities: 1) Your situation is EXACTLY like ours, you only need those first 30 rows but many more match the query. a) Try the recommended STATISTICS scheme in the 7.21 release notes: MEDIUM on the table, HIGH on any columns which are the first column of an index, HIGH on the first indexed columns that are different if several indexes begin with the same columns. (This last was not in the 7.13 release notes!) This worked for other tables where we had the same problem, though not for the one described. b) You can try reducing the level of statistics to MEDIUM or even reducing the confidence and percentage of keys per bucket. The optimizer is not as SMART if it does not know what's going on. This did not work for us for the query described but it did for other tables. c)If these do not work, you have already stumbled onto the solution, UPDATE STATISTICS LOW FOR table DROP DISTRIBUTIONS; By depriving the optimizer of DISTRIBUTIONS you force it to operate exactly as the optimizer in Ver5.0x did and assume that ANY index lookup is cheaper than ANY sort. This was the only solution for the stubborn table/query I described. Fortunately, we do not do a lot of complex joins so the lack of DISTRIBUTIONS has not hurt us too badly and at least performance is no worse than under 5.0x (often still better). This is the best that we can do until 7.3 comes out. Whew I'm winded! Art S. Kagel