|
Problem IT36799
|
Status: Closed |
QUERY PLAN USING NESTED LOOP JOINS HAS LOWER EST COST BUT IS SLOWER THAN ANOTHER PLAN THAT LEVERAGES HASH JOIN WITH HIGHER COST
|
| product: |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 |
| Problem description: |
After upgrading to 12.10.FC14 from a 11.70.FC8 special build on
AIX, customer observed a query was
running roughly 100x slower (11.9 seconds versus 0.10 seconds).
The query is a 5 table join with one subquery and 2 â ˜not
existsâ ™ subqueries.
The slower 12.10 plan uses nested loop and indexes for all joins
while the faster 11.70 join leverages a hash join.
The estimated cost of the slower plan in this particular case
was about 1/3 of the cost of the faster
plan. The slower plan had an estimated cost of 1910 versus an
estimated cost of 5685 for the faster plan.
Rewriting the query to use directives and force the hash join
plan in 12.10 produces similar execution
time as the fast 11.70 query |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC7. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 12.10.xC15 or 14.10.xC7 (when *
* available). *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC7. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 12.10.xC15 or 14.10.xC7 (when *
* available). *
**************************************************************** |
| Comment |
Fixed in Informix Server 12.10.xC15 and 14.10.xC7. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.05.2021
27.08.2021
27.08.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
