You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 [CORE4873]
#5169
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
Submitted by: @pavel-zotov
Is related to CORE3981
Is related to CORE4083
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
The text was updated successfully, but these errors were encountered: