Hi All
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 CC. 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. So
my main concern here is what's the extent of this problem? Is this pattern the
only one that is affected, or are there others? Depending on the answers, this
issue may or may not become a show stopper for us.
Thanks
Huan
