Informix - Problem description
Problem IT32859 | Status: Closed |
QUERY USING FIRST AND ORDER BY MIGHT PICK A NON-OPTIMAL QUERY PLAN | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
It seems like the use of the "first" keyword may be weighted too much in some way, causing a plan that's generated to use an index that prevents a sort, but ends up actually causing the server to have to examine many more rows than a different query plan. Here's 2 query plans, 1 using first and order by and the 2nd just using order by, and from the explain statistics you can see the 1st query takes longer and has to examine many more rows even though it's using the "first" keyword. Here's the table schema: create table mytab2 ( rec_key serial not null , process_dtime datetime year to fraction(3) ) in data lock mode row; create index ix_mytab_1 on mytab2 (process_dtime) ; create unique index pux_mytab on mytab2 (rec_key) ; Then 1 million rows are put into the table, and here is the 1st query with first and order by that appears to be taking a bad path: QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2020 11:21:38) ------ select skip 10 first 20 rec_key from mytab2 where process_dtime is null order by rec_key Estimated Cost: 134 Estimated # of Rows Returned: 10496 1) mytab2: INDEX PATH Filters: mytab2.process_dtime IS NULL (1) Index Name: pux_mytab Index Keys: rec_key (Serial, fragments: ALL) Table map : ---------------------------- Internal name Table name ---------------------------- t1 mytab2 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 30 10496 989534 00:00.72 135 So in the bad plan we see the scan has to look at 989,534 of the 1 million rows to satisfy the skip/first to get 30 rows. 2nd query that leaves off skip/first and takes different path that makes server examine much fewer rows, but has to perform sort: QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2020 11:21:39) ------ select rec_key from mytab2 where process_dtime is null order by rec_key Estimated Cost: 6110 Estimated # of Rows Returned: 10496 Temporary Files Required For: Order By 1) mytab2: INDEX PATH (1) Index Name: ix_mytab_1 Index Keys: process_dtime (Serial, fragments: ALL) Lower Index Filter: mytab2.process_dtime IS NULL Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 mytab2 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 10496 10496 10496 00:00.00 1747 type rows_sort est_rows rows_cons time est_cost ------------------------------------------------------------ sort 10496 10496 10496 00:00.01 4363 This case the server only has to examine 10496 rows and then sort those rows. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description. * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC15 (when available) or * * 14.10.xC5. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description. * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC15 (when available) or * * 14.10.xC5. * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC15 and 14.10.xC5. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.05.2020 23.04.2021 23.04.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |