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-automations opened this issue Aug 20, 2012 · 9 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.0 [ 10221 ]

Version: 2.5.2 [ 10450 ]

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA521 [ QA521 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

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