Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.10
-
None
-
None
-
RHEL 8.9
Description
Hello everyone, I've been having performance issues with a SQL query and now I have several questions.
The query contains an IN clause inside the WHERE (DEPENDENT SUBQUERY). It has always taken only a few seconds, but in recent days has increased a lot, and I have to cancel it after several hours.
I analyzed and reorganized the tables involved, but the result is the same, it doesn't end. The reason?
The IN subquery returns only 11 records, and if I include those values directly in the query the result is immediate.
I changed the IN clause to EXISTS, and although it shows the same Execution Plan (identical), the query now is also immediate.
Another test: without changing the query (with the IN clause), I first executed optimizer_switch='in_to_exists=off', and the execution plan changed to MATERIALIZED. The query is also immediate.
Questions:
a) Why does the same Execution Plan (with IN or EXISTS) have such different performance results?
b) Why doesn't the execution plan use MATERIALIZED (I have to force it with optimizer_switch='in_to_exists=off')?
Thanks and regards,
Jon.