Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inefficient optimization (regression) [CORE3450] #3811

Closed
firebird-automations opened this issue Apr 22, 2011 · 6 comments
Closed

Inefficient optimization (regression) [CORE3450] #3811

firebird-automations opened this issue Apr 22, 2011 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pcisar

Is related to CORE3451

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

====== 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)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to CORE3451 [ CORE3451 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: Deferred

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: Deferred => Done with caveats

Test Details: Found poor performance in 4.0; waiting for fix (letter to dimitr, 29.11.2016 10:07) => 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)

@dyemanov
Copy link
Member

dyemanov commented Oct 6, 2022

Closing as "won't fix", as the reported plan is generally considered good.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants