Re: composite indexes and filter

bozon wrote: >>Depending on the avail statistics the database server might decide >>that a sequential scan or use of a different index is less costly. > > > Yes, the devil is in the details. If Informix decides that it would > return more records than some X % of the table it will do a sequential > scan. I have been told numbers as low as between 10 % and 15 %, which I > find reasonable. Truth. > Does Informix use a percent or does it factor in Seek and Rotational > Latency? If you assume that a B-Tree index is scattered arround on your > disk for every node (page) you read you have to wait for Seek and > Rotational Latency. For table scans the pages are grouped together so > you don't have to factor in Seek and Rotation Latency to every page > read just to the first read. No calculation is peformed for latency etc. IDS assumes that all data pages are in cache and that the cost of any page of index or data is identical. This is supported by the cache aging algorithms in the case of index pages since they have a higher priority in the cache than data pages (yes IDS 10 has a more sophisticated and also simpler aging algorithm, but the results are still that index pages tend to be found in memory). Also readahead by IDS, your arrays, your controllers, and your drives all make sure that nearby pages are in extremely fast access locations. Btree pages are NOT scattered unless the table is using old style (pre 7.3x) attached indexes. Detached and semi-attached indexes place index pages in their own extents so that related index pages have VERY high co-locality. Anyway, this all means that the optimizer can safely optimize to minimize the number of pages that have to be read regardless of whether they are data or index pages. > Another concern is that a compound index with 3 columns is of course > almost 3 times the size of an index with only 1 column. If the data is > structured such that b and c don't really add much to the > descrimination value of the index (low cardinality) and you have an > index on column "a" by itself it may never use the a,b,c index. An > example would be column "a" has 100,000 distinct values in a table of > 1,000,000 rows while column b only has 25 distinct values in that same > 1,000,000 rows. The first column gives you on average 10 rows to look > at a reduction of 1,000,000 to 10, adding column b can't reduce the 10 > rows much more at all so it is of no value in reducing the databases > work and just adds to the number of B-Tree pages that Informix will > have to fetch because a B-Tree on A will of course be much smaller than > a B-Tree on columns A and B. There's something to this. However, the optimizer does take these factors into account as part of the cost calculations, and beyond the costs of the general idea that shorter keys are cheaper if the selectivity of the longer key is low. If your data distributions are up-to-date, the IDS optimizer will decide SPECIFICALLY whether the particular values for b & c will result in sufficiently better selectivity to reduce the number of data page reads to compensate for the additional index page reads. There are very good reasons why us Informix Nuts tout IDS from the roof tops whenever we can. Art S. Kagel

> However, the optimizer does take these factors > into account as part of the cost calculations, and beyond the costs of the > general idea that shorter keys are cheaper if the selectivity of the longer > key is low. If your data distributions are up-to-date, the IDS optimizer > will decide SPECIFICALLY whether the particular values for b & c will result > in sufficiently better selectivity to reduce the number of data page reads > to compensate for the additional index page reads. Yes, I know Informix will because I have made the mistake before of creating a compound index that is nearly useless. :-)