Issue Details (XML | Word | Printable)

Key: CORE-3902
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Huan Ruan
Votes: 1
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Derived fields may not be optimized via an index

Created: 19/Aug/12 09:49 PM   Updated: 13/Jul/15 04:08 AM
Component/s: Engine
Affects Version/s: 2.5.0, 2.5.1, 2.5.2
Fix Version/s: 3.0 Alpha 1, 2.5.3

Environment: 32 bit Windows XP. Firebird Classic Server.
Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
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.

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.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.