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
Comments
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. In Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS. In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS. 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 In Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS. In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS. 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. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovVersion: 2.5.0 [ 10221 ] Version: 2.5.2 [ 10450 ] Component: Engine [ 10000 ] |
Modified by: @dyemanovsummary: Firebird 2.5 Optimiser Issue => Derived fields may not be optimized via an index |
Modified by: @asfernandesassignee: 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 ] |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: