Firebird 2.5 Optimiser Issue
----------------------------
Key: CORE-3902
URL: http://tracker.firebirdsql.org/browse/CORE-3902
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.1
Environment: 32 bit Windows XP. Firebird Classic Server.
Reporter: Huan Ruan
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.
{code:sql}
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
{code}
In Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS.
{code:sql}
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS INDEX (RDB$INDEX_1))
{code}
In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS.
{code:sql}
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS NATURAL)
{code}
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
