Informix - Problem description
Problem IT29971 | Status: Closed |
PERFORMANCE PROBLEM WITH LVARCHAR INDEX WITH SELECT FIRST QUERY | |
product: | |
INFORMIX SERVER / 5725A3900 / E10 - | |
Problem description: | |
When doing a "select first n" query using a lvarchar index and like or matches compariosn. the query will take a long time when n is greater then the number of results returns in this example: select search_name, regcode from companies where search_name like 'CAA%' order by search_name asc we will get 5 results select first 5 search_name, regcode from companies where search_name like 'CAA%' order by search_name asc will return quick: select first 5 search_name, regcode from companies where search_name like 'CAA%' order by search_name asc Estimated Cost: 170 Estimated # of Rows Returned: 200000 1) informix.companies: INDEX PATH Filters: informix.companies.search_name LIKE 'CAA%' (1) Index Name: informix.idx__companies__search_name Index Keys: search_name (Serial, fragments: ALL) UDRs in query: -------------- UDR id : -722 UDR name: compare Query statistics: ----------------- The final cost of the plan is reduced because of the FIRST n specification in the query. Table map : ---------------------------- Internal name Table name ---------------------------- t1 companies type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 5 200000 34709 00:00.70 170 select first 10 search_name, regcode from companies where search_name like 'CAA%' order by search_name asc QUERY: (OPTIMIZATION TIMESTAMP: 07-17-2019 15:09:36) ------ select first 10 search_name, regcode from companies where search_name like 'CAA%' order by search_name asc Estimated Cost: 340 Estimated # of Rows Returned: 200000 1) informix.companies: INDEX PATH Filters: informix.companies.search_name LIKE 'CAA%' (1) Index Name: informix.idx__companies__search_name Index Keys: search_name (Serial, fragments: ALL) UDRs in query: -------------- UDR id : -722 UDR name: compare Query statistics: ----------------- The final cost of the plan is reduced because of the FIRST n specification in the query. Table map : ---------------------------- Internal name Table name ---------------------------- t1 companies type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 5 200000 1000000 00:28.29 341 If we use > and < syntax, it will be fast again, as index filters will be applied. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS 14.10.xC1. * **************************************************************** * PROBLEM DESCRIPTION: * * When doing a "select first n" query using a lvarchar index * * and * * like or matches comparison, * * the query will take a long time when n is greater then the * * number of results returned. * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.08.2019 19.09.2019 19.09.2019 |
Problem solved at the following versions (IBM BugInfos) | |
14.10.xC2 | |
Problem solved according to the fixlist(s) of the following version(s) |