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

Derived fields may not be optimized via an index [CORE3902] #4238

Closed
firebird-issue-importer opened this issue Aug 20, 2012 · 9 comments
Closed

Comments

@firebird-issue-importer

Submitted by: Huan Ruan (huanruan)

Is related to QA521

Votes: 1

While working on upgrading our database from Firebird 2.1.2 to 2.5.1, we found a query that runs a lot slower in 2.5 than in 2.1. We got the query down to a minimum to reproduce the issue and the pattern seems to be related to using derived table with aliased field names.

Here is a small test case to reproduce. All comparisons are done on a 32bit Windows XP server. Firebird CS. The issue can be reproduced with 2.5.1 and the latest 2.5.2 snapshot.

select
rdb$database.rdb$relation_id
from rdb$database
left outer join
(
select
rdb$relations.rdb$relation_id as tempid
from rdb$relations
) temp (tempid)
on temp.tempid = rdb$database.rdb$relation_id

In Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS.
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS INDEX (RDB$INDEX_1))

In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS.
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS NATURAL)

However, the interesting thing is if I remove either the 'as tempid' or '(tempid)' alias, or don't use alias at all, the plan goes back to the 2.1 version.

Clearly, using an alias should not have caused a change in execution plan.

Commits: e315f5a 7e2aec6 50253e0 FirebirdSQL/fbt-repository@cb9f98c

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2012

Modified by: Huan Ruan (huanruan)

description: While working on upgrading our database from Firebird 2.1.2 to 2.5.1, we found a query that runs a lot slower in 2.5 than in 2.1. We got the query down to a minimum to reproduce the issue and the pattern seems to be related to using derived table with aliased field names.

Here is a small test case to reproduce. All comparisons are done on a 32bit Windows XP server. Firebird CS. The issue can be reproduced with 2.5.1 and the latest 2.5.2 snapshot.
{code:sql}
select
rdb$database.rdb$relation_id
from rdb$database
left outer join
(
select
rdb$relations.rdb$relation_id as tempid
from rdb$relations
) temp (tempid)
on temp.tempid = rdb$database.rdb$relation_id
{code}

In Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS.
{code:sql}
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS INDEX (RDB$INDEX_1))
{code}

In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS.
{code:sql}
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS NATURAL)
{code}

However, the interesting thing is if I remove either the 'as tempid' or '(tempid)' alias, or don't use alias at all, the plan goes back to the 2.1 version.

Clearly, using an alias should not have caused a change in execution plan.

=>

While working on upgrading our database from Firebird 2.1.2 to 2.5.1, we found a query that runs a lot slower in 2.5 than in 2.1. We got the query down to a minimum to reproduce the issue and the pattern seems to be related to using derived table with aliased field names.

Here is a small test case to reproduce. All comparisons are done on a 32bit Windows XP server. Firebird CS. The issue can be reproduced with 2.5.1 and the latest 2.5.2 snapshot.

select
rdb$database.rdb$relation_id
from rdb$database
left outer join
(
select
rdb$relations.rdb$relation_id as tempid
from rdb$relations
) temp (tempid)
on temp.tempid = rdb$database.rdb$relation_id

In Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS.
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS INDEX (RDB$INDEX_1))

In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS.
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS NATURAL)

However, the interesting thing is if I remove either the 'as tempid' or '(tempid)' alias, or don't use alias at all, the plan goes back to the 2.1 version.

Clearly, using an alias should not have caused a change in execution plan.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2012

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2012

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2012

Modified by: @dyemanov

Version: 2.5.0 [ 10221 ]

Version: 2.5.2 [ 10450 ]

Component: Engine [ 10000 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2012

Modified by: @dyemanov

summary: Firebird 2.5 Optimiser Issue => Derived fields may not be optimized via an index

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 21, 2012

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ] => Adriano dos Santos Fernandes [ asfernandes ]

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.3 [ 10461 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA521 [ QA521 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 10, 2014

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 13, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

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