Re: composite indexes and filter

bozon schrieb: >>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. > > 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. > > 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. > Nice discussion, let me tell you about my main problem. I have a composite index defined and i am facing to performance problems for inrange queries. My situation : - rows in table 2.000.000 - unique composite index idx1 (a,b,c) - column a is none distinct (currently, one value occured only ) - column b is none distinct (currently, one value occured only ) - column c is high distinct - select a,b,c from table t where a >= :a.from and a <= :a.to and b >= :b.from and b <= :b.to and c >= :c.from and c <= :c.to ':from' and ':to' are values which will be entered from user to create a report of data. e.g. : exexecution plan shows : 1) informix.t_test: INDEX PATH Filters: (((informix.t_test.c <= 100103 AND informix.t_test.c >= 100103 ) AND informix.t_test.b <= 1 ) AND informix.t_test.b >= 1 ) (1) Index Keys: a b c (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.t_test.a >= 'PICKED' Upper Index Filter: informix.t_test.a <= 'PICKED' This confirms what you have wrote. Column 'a' is used to access the data from index. Columns 'b' and 'c' will be filtered, which takes time with my data distirbution. Of course i could define an index to 'c' to increase the performance, but as application developer you never know how the data distribution will be on a customer system finaly. The main intention for a composite index was to have a quick orderby, but i thought it should be fine for fast data access with inrange queries also. I expected 'b' and 'c' can be used in Lower and Upper Index Filter which would be a direct access to the correct result. I don't understand why this can not be done from Informix databases. I think this is related to internals of Informix. It looks like Oracle handle this fine.

I am not sure that I understand. Does column "a" contain lots of different values or only one value? Same question for "b" If "a" only has one value then it is a really bad thing to have at the beginning of your index or in your index at all. What is the real life likely distributions of data? Also I just noticed that informix.t_test.c <= 100103 AND informix.t_test.c >= 100103 isn't really a good way to write "=" because that is the only value that will solve this equation. This may be your problem. You can flip the index to c,b,a or c,a,b. Your most distinct column should be your first column in a composite index unless there is an overriding reason not to do it. I can only think of 1 case: The most distinct column doesn't always occur in your queries but you want it in there for when it is included. This really only works when the next most distinct isn't that bad. In your case I can't see why a and b are even in the index. If it is a problem with your test data you need to fix your test data to more accurately reflect the data that you expect in the real world. This will give you a more accurate performance test. And may lead to a better indexing scheme or a better table structure.

bozon schrieb: > I am not sure that I understand. Does column "a" contain lots of > different values or only one value? Same question for "b" Column 'a' contains one value ('PICKED') only. This means column 'c' is distinct only. > > If "a" only has one value then it is a really bad thing to have at the > beginning of your index or in your index at all. What is the real life > likely distributions of data? > This is the situation i saw on my customer system. > Also I just noticed that > informix.t_test.c <= 100103 AND informix.t_test.c >= 100103 > isn't really a good way to write "=" because that is the only value > that will solve this equation. This may be your problem. > I agree with you. This is a specific case when :from and :to will have the same bindings. Informix optimizer will not optimize the query to a = 'PICKED'and b = 1 and c = 100103 A defect is reported to IBM and a fix will be available soon. But this fix will not help in a situation like e.g. where a >= 'PICKED' and a <='PICKED' and b >= 1 and b <= 1 and c >= 100103 and c <= 100104 > You can flip the index to c,b,a or c,a,b. Your most distinct column > should be your first column in a composite index unless there is an > overriding reason not to do it. Index (a,b,c) is used for fast orderby from other queries. I could add additional indexes but since i don't known about data distibution on customer systems i need several indexes defined to get best performance. (a,b,c),(a,c,b),(b,a,c),(b,c,a),(c,a,b),(c,b,a) This needs a lot of space. The given table is an example only. I have tables with multiple composite indexes on different columns. I think you can imagine that this not an option for tables with more than 2.000.000 rows. I still not understand why column 'b' and 'c' needs to be filtered. I assume a composite index (a,b,c) is ordered in btree. I expect database can set the start point and end point to scan an index. If my assumtion is true something like this should be possible Lower Index Filter: informix.t_test.a >= 'PICKED' and b >= 1 and c >= 100103 If the key is ordered the database could return all rows until Upper Index Filter: informix.t_test.a <= 'PICKED' and b <= 1 and c <= 100104 is reached. No filter should be necessary. However, it seems this is not possible to do with Informix. Regards ... Joerg

create table dave (a char(20), b integer, c integer) insert into dave values('PICKED',0,100102) insert into dave values('PICKED',0,100103) insert into dave values('PICKED',0,100104) insert into dave values('PICKED',1,100102) insert into dave values('PICKED',1,100103) insert into dave values('PICKED',1,100104) insert into dave values('PICKED',2,100102) insert into dave values('PICKED',2,100103) insert into dave values('PICKED',2,100104) create index dave1 on dave (a,b,c); update statistics high for table dave; select --+AVOID_FULL(dave) explain * from dave where a >= 'PICKED' and a <='PICKED' and b >= 1 and b <= 1 and c >= 100103 and c <= 100104 DIRECTIVES FOLLOWED: AVOID_FULL ( dave )EXPLAIN DIRECTIVES NOT FOLLOWED: .. 1) djw1.dave: INDEX PATH Filters: (((djw1.dave.c >= 100103 AND djw1.dave.b <= 1 ) AND djw1.dave.b >= 1 ) AND djw1.dave.c <= 100104 ) (1) Index Keys: a b c (Key-Only) (Serial, fragments: ALL) Lower Index Filter: djw1.dave.a >= 'PICKED' Upper Index Filter: djw1.dave.a <= 'PICKED' That is the problem? Well IDS starts down the a<=PICKED so find the leftmost position in the index to start with. So you are saying iit should start with a>='PICKED' and b>=1 and c<=100103 ?? and rather than stop at a>='PICKED' it should stop scanning the index at a>='PICKED' or A='PICKED' and B>=1 or A='PICKED' and B=1 and C>100104 ?? Is that the problem? If this is correct can please raise this as a test case with IBM support?? If the above problem is the situation then can you please raise a feature request with IBM and get me the number so I can add it to the list of new feature requests that I am maintaining? david ( david@smooth1.co.uk )