Optimizer ignores effective plan with NL + indexed scans when INNER join
involves VIEW which DDL is union of several indexed tables
-----------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4926
URL: http://tracker.firebirdsql.org/browse/CORE-4926
Project: Firebird Core
Issue Type: Bug
Components: Engine
Reporter: Pavel Zotov
Preparing data:
============
set term ^;
execute block as begin execute statement 'drop sequence g'; when any do begin
end end
^ set term ;^
commit;
create sequence g;
create or alter procedure sp_tdat as begin end;
create or alter view v_union as select 1 x from rdb$database;
recreate table tmain(id int, x int, y int, z int);
recreate table tdat1(id int, x int, y int, z int);
recreate table tdat2(id int, x int, y int, z int);
recreate table tdat3(id int, x int, y int, z int);
recreate table tdat4(id int, x int, y int, z int);
recreate table tdat5(id int, x int, y int, z int);
create or alter view v_union as
select * from tdat1 union all
select * from tdat2 union all
select * from tdat3 union all
select * from tdat4 union all
select * from tdat5
;
insert into tdat1(id, x, y, z) select gen_id(g,1), 1000, 1, rand()*10 from
rdb$types, rdb$types rows 50000;
insert into tdat2(id, x, y, z) select gen_id(g,1), 2000, 2, rand()*10 from
rdb$types, rdb$types rows 50000;
insert into tdat3(id, x, y, z) select gen_id(g,1), 3000, 3, rand()*10 from
rdb$types, rdb$types rows 50000;
insert into tdat4(id, x, y, z) select gen_id(g,1), 4000, 4, rand()*10 from
rdb$types, rdb$types rows 50000;
insert into tdat5(id, x, y, z) select gen_id(g,1), 5000, 5, rand()*10 from
rdb$types, rdb$types rows 50000;
commit;
create index tdat1_xy on tdat1(x, y);
create index tdat2_xy on tdat2(x, y);
create index tdat3_xy on tdat3(x, y);
create index tdat4_xy on tdat4(x, y);
create index tdat5_xy on tdat5(x, y);
commit;
delete from tmain;
insert into tmain(id, x, y) values( gen_id(g,1), 2000, 2);
commit;
Query-1:
#######
select m.id, sum(u.z)
from tmain m
LEFT --------------------- <<< we do NOT give optimizer to choose driving
data source here
join v_union u on m.x = u.x and m.y = u.y
where u.x is not null
group by m.id;
Trace plan and statistics in 3.0:
########################
Select Expression
-> Aggregate
-> Filter
-> Sort (record length: 112, key length: 8)
-> Nested Loop Join (outer)
-> Table "TMAIN" as "M" Full Scan
-> Filter
-> Union
-> Filter
-> Table "TDAT1" as "U TDAT1" Access By ID
-> Bitmap
-> Index "TDAT1_XY" Range Scan (full
match)
-> Filter
-> Table "TDAT2" as "U TDAT2" Access By ID
-> Bitmap
-> Index "TDAT2_XY" Range Scan (full
match)
-> Filter
-> Table "TDAT3" as "U TDAT3" Access By ID
-> Bitmap
-> Index "TDAT3_XY" Range Scan (full
match)
-> Filter
-> Table "TDAT4" as "U TDAT4" Access By ID
-> Bitmap
-> Index "TDAT4_XY" Range Scan (full
match)
-> Filter
-> Table "TDAT5" as "U TDAT5" Access By ID
-> Bitmap
-> Index "TDAT5_XY" Range Scan (full
match)
1 records fetched
633 ms, 100044 fetch(es)
Table Natural Index Update Insert
Delete
************************************************************************************
TMAIN 1
TDAT2 50000
This is good plan: NO any tables except TDAT2 were affected by execution. All
works fast.
Trace plan and statistics in 2.5:
########################
PLAN SORT (JOIN (M NATURAL(U TDAT1 INDEX (TDAT1_XY))
PLAN (U TDAT2 INDEX (TDAT2_XY))
PLAN (U TDAT3 INDEX (TDAT3_XY))
PLAN (U TDAT4 INDEX (TDAT4_XY))
PLAN (U TDAT5 INDEX (TDAT5_XY))))
1 records fetched
749 ms, 410 read(s), 100044 fetch(es)
Table Natural Index
******************************************************
TMAIN 1
TDAT2 50000
Also all fine.
Query-2:
#######
select m.id, sum(u.z)
from tmain m
INNER ------------------------------- <<< we allow optimizer to choose
driving data source here
join v_union u on m.x = u.x and m.y = u.y
-- where u.x is not null
group by m.id;
Trace plan and statistics in 3.0:
########################
Select Expression
-> Aggregate
-> Sort (record length: 112, key length: 8)
-> Filter
-> Hash Join (inner)
-> Table "TMAIN" as "M" Full Scan
-> Record Buffer (record length: 33)
-> Union
-> Table "TDAT1" as "U TDAT1" Full Scan
-> Table "TDAT2" as "U TDAT2" Full Scan
-> Table "TDAT3" as "U TDAT3" Full Scan
-> Table "TDAT4" as "U TDAT4" Full Scan
-> Table "TDAT5" as "U TDAT5" Full Scan
1 records fetched
2867 ms, 1139 read(s), 503800 fetch(es)
Table Natural Index Update Insert
**************************************************************************
TMAIN 1
TDAT1 50000
TDAT2 50000
TDAT3 50000
TDAT4 50000
TDAT5 50000
Trace plan and statistics in 2.5:
########################
PLAN SORT (MERGE (SORT (M NATURAL)(U TDAT1 NATURAL)
PLAN (U TDAT2 NATURAL)
PLAN (U TDAT3 NATURAL)
PLAN (U TDAT4 NATURAL)
PLAN (U TDAT5 NATURAL)))
1 records fetched
3269 ms, 1896 read(s), 503800 fetch(es)
Table Natural Index
******************************************************
TMAIN 1
TDAT1 50000
TDAT2 50000
TDAT3 50000
TDAT4 50000
TDAT5 50000
So, when we give to optimizer freedom to choose driving datasource, it goes in
wrong way.
PS. Can`t escape from thinking that I've already seen this in some ticket
before. Sorry if this is duplicate.
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
