New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Wrong PLAN in Subquery [CORE1423] #1841
Comments
Commented by: Ivano Di Mizio (ivanodm) Creation script for the example database with 20.000 rows in each table. |
Modified by: Ivano Di Mizio (ivanodm)Attachment: db.zip [ 10531 ] |
Modified by: Ivano Di Mizio (ivanodm)Component: Engine [ 10000 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pcisarWorkflow: jira [ 12863 ] => Firebird [ 15538 ] |
Modified by: @pavel-zotovQA Status: No test |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Submitted by: Ivano Di Mizio (ivanodm)
Duplicates CORE1137
Attachments:
db.zip
Consider a database with the following structure:
CREATE TABLE TABLE1 (ID1 INTEGER NOT NULL);
CREATE TABLE TABLE2 (ID2 INTEGER NOT NULL);
CREATE INDEX TABLE2_IDX1 ON TABLE2 (ID2);
Run this query:
SELECT * FROM TABLE1 WHERE ( ID1 NOT IN ( SELECT ID2 FROM TABLE2 ) );
The output PLAN is:
PLAN (TABLE2 NATURAL)
PLAN (TABLE1 NATURAL)
So, index on TABLE2 is not used and a great loss of performance comes out.
Attached is the example database with about 20.000 rows in each table; in this case the server hungs at 99% cpu load and the query never ends.
This behaviour does not occur with Firebird 1.5.0.4306, that is the version we currently use with all our projects.
Using the attached database (20.000 rows per table) the query ends up in the less than one second and the PLAN is correctly chosen:
PLAN (TABLE2 INDEX (TABLE2_IDX1))
PLAN (TABLE1 NATURAL)
The text was updated successfully, but these errors were encountered: