Applying scalar function on selected column can cause performance problem when use derived table (comparing to a view with same query)

Create script:

set bail on;
shell if exist r:\temp\tmp4test.fdb del /q /f r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb';
set echo on;

recreate table rr(
   rel_name varchar(31)
   ,id int
   ,fid int
);

recreate table rf(
   rel_name varchar(31)
   ,fid int
   ,fnm varchar(31)
);
insert into rr
select a.rdb$relation_name, a.rdb$relation_id, a.rdb$field_id
from rdb$relations a
;
insert into rf select f.rdb$relation_name, f.rdb$field_id, f.rdb$field_name
from rdb$relation_fields f
;
commit;

alter table rr add constraint rr_rel_name_unq unique (rel_name);
create index rr_id on rr (id);

alter table rf add constraint rf_fnm_rel_name_unq unique(fnm, rel_name);
create index rf_rel_name on rf(rel_name);

recreate view v as
select r.rel_name, abs(r.id) as id
from rr r
left 
join rf on r.rel_name = rf.rel_name and r.fid = rf.fid
where r.id < 128;

set statistics index rr_rel_name_unq;
set statistics index rr_id;
set statistics index rf_fnm_rel_name_unq;
set statistics index rf_rel_name;
commit;
connect 'localhost:r:\temp\tmp4test.fdb';

set count on;
set list on;

select /* trace_tag: VIEW */ v.rel_name as v_rel_name, v.id as v_id from v
where id = 0
;

select /* trace_tag: DERIVED TABLE */ d.rel_name as d_rel_name, d.id as d_id
from (
    select r.rel_name, abs(r.id) as id
    from rr r
    left 
    join rf on r.rel_name = rf.rel_name and r.fid = rf.fid
    where r.id < 128
) d
where d.id = 0
;

Create config for trace:

database =
{
    enabled = true
    log_initfini = false
    log_errors = true
    log_sweep = true
    include_filter = "%trace_tag%"
    time_threshold = 0
    log_statement_finish = true
    explain_plan = true
    print_plan = true
    print_perf = true
    max_sql_length = 32000
}

Run trace. Run script.

Trace log will show: 1) for query to VIEW:

select /* trace_tag: VIEW */ v.rel_name as v_rel_name, v.id as v_id from v
where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Select Expression
    -> Filter
        -> Nested Loop Join (outer)
            -> Filter
                -> Table "RR" as "V R" Access By ID
                    -> Bitmap
                        -> Index "RR_ID" Range Scan (upper bound: 1/1)
            -> Filter
                -> Table "RF" as "V RF" Access By ID
                    -> Bitmap
                        -> Index "RF_REL_NAME" Range Scan (full match)
1 records fetched
      0 ms, 2 read(s), 66 fetch(es)

Table                              Natural     Index    Update    Insert
***************************************************************************
RR                                                56
RF                                                 4

2) for query that uses DERIVED TABLE:

select /* trace_tag: DERIVED TABLE */ d.rel_name as d_rel_name, d.id as d_id
from (
    select r.rel_name, abs(r.id) as id
    from rr r
    left
    join rf on r.rel_name = rf.rel_name and r.fid = rf.fid
    where r.id < 128
) d
where d.id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Select Expression
    -> Filter
        -> Nested Loop Join (outer)
            -> Filter
                -> Table "RR" as "D R" Access By ID
                    -> Bitmap
                        -> Index "RR_ID" Range Scan (upper bound: 1/1)
            -> Filter
                -> Table "RF" as "D RF" Access By ID
                    -> Bitmap
                        -> Index "RF_REL_NAME" Range Scan (full match)
1 records fetched
      0 ms, 6 read(s), 687 fetch(es)

Table                              Natural     Index    Update    Insert
****************************************************************************
RR                                                56
RF                                               511

If we replace query to derived table like this:

select /* trace_tag #3 */ d.rel_name as d_rel_name, abs(d.id) as d_id
from (
    select r.rel_name, r.id as id
    from rr r
    left 
    join rf on r.rel_name = rf.rel_name and r.fid = rf.fid
    where r.id < 128
) d
where d.id = 0
;

-- then trace show good stat:

Select Expression
    -> Filter
        -> Nested Loop Join (outer)
            -> Filter
                -> Table "RR" as "D R" Access By ID
                    -> Bitmap
                        -> Index "RR_ID" Range Scan (full match) --- CHANGED! Was: "upper bound: 1/1"
            -> Filter
                -> Table "RF" as "D RF" Access By ID
                    -> Bitmap
                        -> Index "RF_REL_NAME" Range Scan (full match)
1 records fetched
      0 ms, 9 fetch(es)

Table                              Natural     Index    Update    Inse
**********************************************************************
RR                                                 1
RF                                                 4

So, the problem somehow relates to evaluating of ABS(id) inside DT. But this trouble no affects on case when we query VIEW.

PS. Created after check old ticket CORE-3981 ( https://github.com/FirebirdSQL/firebird/issues/4314 ).

FirebirdSQL / firebird

Applying scalar function on selected column can cause performance problem when use derived table (comparing to a view with same query) #8217