Skip to content
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

Closed
firebird-automations opened this issue Aug 21, 2007 · 9 comments
Closed

Wrong PLAN in Subquery [CORE1423] #1841

firebird-automations opened this issue Aug 21, 2007 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

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)

@firebird-automations
Copy link
Collaborator Author

Commented by: Ivano Di Mizio (ivanodm)

Creation script for the example database with 20.000 rows in each table.

@firebird-automations
Copy link
Collaborator Author

Modified by: Ivano Di Mizio (ivanodm)

Attachment: db.zip [ 10531 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Ivano Di Mizio (ivanodm)

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

1) This performance issue is documented in the release notes
2) Duplicate for CORE1137

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE1137 [ CORE1137 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12863 ] => Firebird [ 15538 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant