16.08.2012 3:16, ruan_h wrote: > > 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.
Please enter this issue into the bug tracker at http://tracker.firebirdsql.org/ Dmitry
