Index not used with MATCHES clause

Dear Informixers, until now I was under the impression that an index on a column will be used when this column is searched with a MATCHES clause in the following way: SELECT ... WHERE <column_name> MATCHES "Something*" While researching slow response times on a certain query, I used "set explain on" and got the following results: QUERY ------ select * from stamm where p_name = "Spitz" Estimated Cost: 4 Estimated # of Rows Returned: 1 1) spitz.stamm: INDEX PATH (1) Index Keys: p_name Lower Index Filter: spitz.stamm.p_name = 'Spitz' QUERY: ------ select * from stamm where p_name matches "Spitz*" Estimated Cost: 2734 Estimated # of Rows Returned: 30 1) spitz.stamm: SEQUENTIAL SCAN Filters: spitz.stamm.p_name MATCHES 'Spitz*' Why is the index not used for the MATCHES type query? I am not yet too familiar with the fine art of performance tuning under IDS 7.30, but I used Art S. Kagel's wonderful "dostats" utility to run the UPDATE STATISTICS commands. Platform: Siemens Reliant Unix 5.43C20, Informix Dynamic Server 7.30UC3 Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Richard Spitz wrote: > > Dear Informixers, > > until now I was under the impression that an index on a column will be > used when this column is searched with a MATCHES clause in the following > way: > > SELECT ... WHERE <column_name> MATCHES "Something*" > > While researching slow response times on a certain query, I used > "set explain on" and got the following results: [SNIPPED] > Why is the index not used for the MATCHES type query? I am not yet too > familiar with the fine art of performance tuning under IDS 7.30, but I > used Art S. Kagel's wonderful "dostats" utility to run the UPDATE STATISTICS > commands. The optimizer feels free to decide to NOT use an index if it will not reduce the cost of a query. If onstat -hd <database> shows that a large majority of rows contain the string for which you are searching, such that there is a substantial probability that almost all pages contain at least one such row, the optimizer will decide to save the I/Os needed to read the index pages and just do a table scan under the theory that it will have to read all the pages anyway. Art S. Kagel

Hallo, the answer of Mr. Kagel is certainly correct, but there are in numerous releases of 7.x ( f.i. 7.22 on SCO ) bugs in the optimizer which led to the described effect. It's pretty easy to see whether the optimizer is wrong . If you execute ... where name[1,5]="Spitz" and it uses the index there should be no reason for the optimizer to do it different with your matches statement. Klaus Nonne kn@nsi.de

Klaus Nonne wrote: > the answer of Mr. Kagel is certainly correct, but there are in numerous > releases of 7.x ( f.i. 7.22 on SCO ) bugs in the optimizer which led to the > described effect. I cannot really follow Art Kagel's line of reason. The database field in question contains names, and these are pretty randomly distributed in that table. "Spitz" and other names beginning with "Spitz" are not all that common around here. I cannot imagine why the optimizer should think that this string is contained in a majority of rows. > It's pretty easy to see whether the optimizer is wrong . If you execute ... > where name[1,5]="Spitz" and it uses the index there should be no reason for > the optimizer to do it different with your matches statement. Result: QUERY: ------ select * from stamm where p_name[1,5] = "Spitz" Estimated Cost: 4 Estimated # of Rows Returned: 2 1) spitz.stamm: INDEX PATH (1) Index Keys: p_name Lower Index Filter: spitz.stamm.p_name[1,5] = 'Spitz' Is it an optimizer bug or not? Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Richard Spitz wrote: > > Dear Informixers, > > until now I was under the impression that an index on a column will be > used when this column is searched with a MATCHES clause in the following > way: > > SELECT ... WHERE <column_name> MATCHES "Something*" > > While researching slow response times on a certain query, I used > "set explain on" and got the following results: [snip] Hi Richard, is p_stamm (var)char or n(var)char and what's your Locale? I realized similar behaviour in some versions regarding to NLS and GLS. mfg martin

Martin Berns wrote: > is p_stamm (var)char or n(var)char and what's your Locale? > I realized similar behaviour in some versions regarding to NLS and GLS. I've received some mails that point to a similar direction. The field is a nchar, and locale is de_de.8859-1. I searched the Web Bug Database and found several pointers. One is bug #89956 whose description is "currently not available". However, it is also mentioned in the release notes for IDS 7.30UC6 as still open. The description there talks about a Japanese locale, but apparently this happens also in other circumstances. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Richard Spitz wrote: > > Klaus Nonne wrote: > > > the answer of Mr. Kagel is certainly correct, but there are in numerous > > releases of 7.x ( f.i. 7.22 on SCO ) bugs in the optimizer which led to the > > described effect. > > I cannot really follow Art Kagel's line of reason. The database field in > question contains names, and these are pretty randomly distributed in that > table. "Spitz" and other names beginning with "Spitz" are not all that common > around here. I cannot imagine why the optimizer should think that this > string is contained in a majority of rows. OK, I meerly offered this as one possibility to be checked out in your investigations. I also see you tracked this to a GLS bug so this discussion is moot. Art S. Kagel

Richard Spitz wrote: > I searched the Web Bug Database and found several pointers. One is bug > #89956 whose description is "currently not available". However, it is > also mentioned in the release notes for IDS 7.30UC6 as still open. The > description there talks about a Japanese locale, but apparently this > happens also in other circumstances. Hallo Richard, i did some additional investigations with 7.30.UC2 on Solaris: When you use optimizer hint (--+ Index tablename indexname) it is getting worse! I have created a table with 300000 rows and a clustered index on a nchar-column. If you direct the optimizer to use the index he does this by reading the whole btree (page by page), which is extremly slower than a sequential scan of the data. As the index is built appropriate, i can't imagine why informix isn't able to use it this way. I know this don't help martin

Martin Berns wrote: > i did some additional investigations with 7.30.UC2 on Solaris: > When you use optimizer hint (--+ Index tablename indexname) it is > getting > worse! I have created a table with 300000 rows and a clustered index on > a nchar-column. If you direct the optimizer to use the index he does > this > by reading the whole btree (page by page), which is extremly slower than > a sequential scan of the data. As the index is built appropriate, i > can't > imagine why informix isn't able to use it this way. I made a support call to Siemens out of this (they are our OEM supplier of Informix), and the result was rather shocking to me: There was a bug somewhere in earlier releases of Online 7.x that resulted in incorrect results when querying against N(VAR)CHAR columns. Somehow special characters like "ß" were not treated correctly when the index was used. As a workaround, the optimizer now ignores indexes when a query with MATCHES is issued against a N(VAR)CHAR column and does a sequential scan. This ensures correct results, but ruins performance. A real solution for the problem has not yet been implemented, and the support guy couldn't tell me when this will be fixed. I urged him to make a feature request to Informix to fix this bug ASAP. I consider this a killer. In my environment, the performance degradation on such queries is still tolerated by our users, since the tables in question are rather small (<< 100000 rows). But what happens in large database environ- ments? Going back to (VAR)CHAR instead of N(VAR)CHAR is no real alternative if you want to take advantage of non-english locales. I found several related Bug Entries in the TechInfo database: 99152: INDEX ON NCHAR COLUMN IN AN 8-BIT GLS DATABASE SEQUENTIALLY SCANNED Index is only taken when OPTCOMPIND is set to 0 and setting OPTCOMPIND to 0 doubles processing time. 76180:QUERY ON NCHAR FIELD RETURNS WRONG RESULT IN CONNECTION WITH SEARCH ON BLANK. AN INDEX ON THE FIELD MUST BE PRESENT. When DB_LOCALE set to anything but en_US.8859-1, certain queries on an indexed NCHAR field containing a blank will return the wrong results. 70328: MAJOR PERFORMANCE PROBLEM ON NCHAR AND NVARCHAR FIELDS WITH LIKE SEARCH ON INDEXED TABLE Similar bugs #32931 #44201 doesnt fix this in 7.2x see UK case#130226 -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+