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