Regression since 2.5.3: excessive indexed reads when applying WHERE-filter to
rows obtainded from DT and filtered column is result of some evaluation inside
this DT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4873
URL: http://tracker.firebirdsql.org/browse/CORE-4873
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Beta 2, 2.5.4, 2.5.3 Update 1, 3.0 Beta 1, 2.5.3, 3.0
Alpha 2, 3.0 Alpha 1, 3.0 RC 1, 2.5.5
Reporter: Pavel Zotov
Query-1:
=======
select *
from (
select r.rdb$relation_name, r.rdb$relation_id as id
from rdb$relations r
left
join rdb$relation_fields rf on r.rdb$relation_name = rf.rdb$relation_name
where rdb$relation_id < 128
)
where id = 0;
Query-2:
=======
select *
from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r
left
join rdb$relation_fields rf on r.rdb$relation_name = rf.rdb$relation_name
where rdb$relation_id < 128
)
where id = 0;
The only difference in these queries is presence of ABS() function inside Q-2,
i.e. column `ID` which will be filtered in outside of DT is result of trivial
EVALUATION.
Trace in WI-V2.5.2.26540 (both queries have identical PLAN JOIN (R INDEX
(RDB$INDEX_1), RF INDEX (RDB$INDEX_4)):
====================
1) for Q-1:
4 records fetched
0 ms, 14 fetch(es)
Table Natural Index Update
***************************************************************
RDB$RELATION_FIELDS 4
RDB$RELATIONS 1
2) for Q2:
4 records fetched
0 ms, 96 fetch(es)
Table Natural Index Update
**************************************************************
RDB$RELATION_FIELDS 4
RDB$RELATIONS 42
Trace in WI-V2.5.3.26780:
====================
1) for Q-1: the same as in 2.5.2, see above.
2) for Q-2:
4 records fetched
2 ms, 7 read(s), 856 fetch(es)
Table Natural Index Update
*************************************************************
RDB$RELATION_FIELDS 343 <<<<<<<<<<<<<<<<<<<<<< ??
RDB$RELATIONS 42
Trace in WI-V3.0.0.31931:
1) for Q-1 - the same as in 2.5.x;
2) for Q-2:
4 records fetched
186 ms, 14 read(s), 1116 fetch(es)
Table Natural Index
*****************************************************
RDB$RELATION_FIELDS 457 <<<<<<<<<<<<<<<<<<<<<<<< ??
RDB$RELATIONS 50
--
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
------------------------------------------------------------------------------
Don't Limit Your Business. Reach for the Cloud.
GigeNET's Cloud Solutions provide you with the tools and support that
you need to offload your IT needs and focus on growing your business.
Configured For All Businesses. Start Your Cloud Today.
https://www.gigenetcloud.com/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
