Issue Details (XML | Word | Printable)

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

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

Inefficient optimization (regression)

Created: 22/Apr/11 03:42 PM   Updated: 30/Nov/16 08:24 AM
Component/s: Engine
Affects Version/s: 2.5.0
Fix Version/s: None

Issue Links:
Relate
 

QA Status: Done with caveats
Test Details:
29.11.2016:
Found poor performance in 4.0; waiting for fix (letter to dimitr, 29.11.2016 10:07)

30.11.2016:
Test added, but current FB 4.0 snapshots should FAIL on this test.
Commit was done after discuss with dimitr (letter 30.11.2016 10:21)


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



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 22/Apr/11 05:02 PM
I see a different plan for v1.5:
PLAN JOIN (TMP_SP1 NATURAL,JOIN (T2 INDEX (TMP_TBL2_FLD2),T1 INDEX (TMP_TBL1_FLD1),T3 INDEX (TMP_TBL3_FLD1)))

The one you have provided is reported by v2.5 accessing the ODS10 database.

Also, if the procedure generates 1000 records instead of one, number of fetches changes:
v1.5 = 72000
v2.5 = 43000

And this is quite expected, because the v1.5 plan looks actually worse, because stream T2 doesn't depend on SP1 and thus will be retrieved (along with T1 and T3) per every record returned by SP1.

Hence I'm not convinced this is a regression, especially provided that the engine cannot estimate cardinalities for stored procedures.