|Subject||Re: Firebird 2.5 Optimiser Issue|
16.08.2012 3:16, ruan_h wrote:
>Please enter this issue into the bug tracker at
> 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.
> from rdb$database
> 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.