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.
left outer join
rdb$relations.rdb$relation_id as tempid
) 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.