|
Problem IT35390
|
Status: Closed |
14.10.FC4W1 OPTIMIZER CHOSES ASSIGNS LOWER COST TO SLOWER QUERY PLAN THAT USES INDEX SCANS AND 6 NESTED LOOP JOINS
|
| product: |
INFORMIX SERVER / 5725A3900 / E10 - |
| Problem description: |
In 14.10.FC4W1, a lower cost plan is executing much longer than
a higher cost plan.
The customer reported this problem after upgrading from
11.70.FC9 to 14.10 and in their
environment the new plan was causing the query to execute in ~5
minutes in 14.10
versus a couple seconds in 11.70.
Using directives, I was able to see that the ideal 11.70 plan
had a higher estimated
cost in the repro I generated in 14.10 but even with generated
data the 11.70 plan
was much quicker than the 14.10 plan that the optimizer was
choosing.
Both plans are all nested loop joins and index scans for a 6
table join. The ideal
plan uses different indexes in another order but still all
nested loop joins. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server 14.10.xC6. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 14.10.xC6 (when available). *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server 14.10.xC6. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 14.10.xC6 (when available). *
**************************************************************** |
| Comment |
Fixed in Informix Server 14.10.xC6. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 30.12.2020
12.05.2021
12.05.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
