Inefficient optimization (regression)
-------------------------------------
Key: CORE-3450
URL: http://tracker.firebirdsql.org/browse/CORE-3450
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.0
Reporter: Pavel Cisar
Test case:
/* INIT */
create table tmp_tbl1 (fld1 integer, fld2 integer, fld3 integer);
create index tmp_tbl1_fld1 on tmp_tbl1(fld1);
create index tmp_tbl1_fld2 on tmp_tbl1(fld2);
commit;
create table tmp_tbl2 (fld1 integer, fld2 integer);
create index tmp_tbl2_fld1 on tmp_tbl2(fld1);
create index tmp_tbl2_fld2 on tmp_tbl2(fld2);
commit;
create table tmp_tbl3 (fld1 integer);
create index tmp_tbl3_fld1 on tmp_tbl3(fld1);
commit;
set term ^;
create or alter procedure tmp_sp1
returns (fld1 integer)
as begin
fld1=1;
suspend;
end ^
create or alter procedure tmp_sp2
as
declare variable I integer;
begin
i=0;
while (i<10000) do begin
i=i+1;
insert into tmp_tbl1 values (:i, 1, 3);
end
i=0;
while (i<10) do begin
i=i+1;
insert into tmp_tbl2 values (:i, 2);
end
insert into tmp_tbl3 values (3);
end ^
set term ;^
commit;
execute procedure tmp_sp2;
commit;
SET STATISTICS INDEX TMP_TBL1_FLD1;
SET STATISTICS INDEX TMP_TBL1_FLD2;
SET STATISTICS INDEX TMP_TBL2_FLD1;
SET STATISTICS INDEX TMP_TBL2_FLD2;
SET STATISTICS INDEX TMP_TBL3_FLD1;
commit;
-- Test:
select t2.fld1
from tmp_tbl2 t2
join tmp_tbl1 t1 on t1.fld1=t2.fld1
join tmp_sp1 p1 on p1.fld1=t1.fld2
join tmp_tbl3 t3 on t3.fld1=t1.fld3
where t2.fld2=2;
FB 1.5:
PLAN JOIN (TMP_SP1 NATURAL,JOIN (T2 INDEX (TMP_TBL2_FLD2),T1 INDEX
(TMP_TBL1_FLD2,TMP_TBL1_FLD1),T3 INDEX (TMP_TBL3_FLD1)))
Reads = 0
Writes 0
Fetches = 366
FB 2.5:
PLAN JOIN (JOIN (TMP_SP1 NATURAL, T1 INDEX (TMP_TBL1_FLD2)), T2 INDEX
(TMP_TBL2_FLD1), T3 INDEX (TMP_TBL3_FLD1)) <---- different plan
Reads = 0
Writes 0
Fetches = 40094 <----- causes significant performance degradation when executed
many times under load
--
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
------------------------------------------------------------------------------
Fulfilling the Lean Software Promise
Lean software platforms are now widely adopted and the benefits have been
demonstrated beyond question. Learn why your peers are replacing JEE
containers with lightweight application servers - and what you can gain
from the move. http://p.sf.net/sfu/vmware-sfemails
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
