Issue Details (XML | Word | Printable)

Key: CORE-4873
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

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

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

Created: 12/Jul/15 02:40 PM   Updated: 13/Jul/15 09:37 AM
Component/s: Engine
Affects Version/s: 3.0 Alpha 1, 3.0 Alpha 2, 2.5.3, 3.0 Beta 1, 2.5.3 Update 1, 2.5.4, 3.0 Beta 2
Fix Version/s: None

Issue Links:
Relate
 

QA Status: No test


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



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 13/Jul/15 09:37 AM
Regression introduced with the bugfix for CORE-4083.