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

FB3 Optimiser chooses less efficient plan than FB2.5 optimiser [CORE5965] #6219

Closed
firebird-issue-importer opened this issue Nov 16, 2018 · 16 comments

Comments

@firebird-issue-importer

Submitted by: Haakon Nergaard (haakon)

Is related to CORE5795
Is related to CORE5070
Is related to CORE5481
Is related to CORE5845

Attachments:
optimizer_test_setup.sql

The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and populating table in attachment. Example code below:

/**
* Here's the select. Please test with and without "sysid"
*/
select * from opt_test
where
--sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

Commits: 077a2a3 3103f72

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2018

Commented by: Haakon Nergaard (haakon)

Code for creating and populating table to reproduce the issue

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2018

Modified by: Haakon Nergaard (haakon)

Attachment: optimizer_test_setup.sql [ 13311 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2018

Commented by: Haakon Nergaard (haakon)

Moved code to create and populate table to a file attachment

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2018

Modified by: Haakon Nergaard (haakon)

description: The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Example code below:

/**
* creating the table and indices
*/

create generator gen_opt_test_id;
create generator gen_order_no;

create table opt_test (
id bigint not null,
sysid integer not null,
clid integer not null,
cust_type integer not null,
cust_id integer not null,
order_no bigint not null
);

alter table opt_test add constraint pk_opt_test primary key (id);

set term ^ ;

create or alter trigger opt_test_bi for opt_test
active before insert position 0
as
begin
if (http://new.id is null) then
http://new.id = gen_id(gen_opt_test_id,1);
end
^

set term ; ^

commit;
/**
* gererating data (approx. )
* this could take a while
* time for coffee
*/

set term ^ ;

execute block
as
declare max_rows integer = 150000;
declare sysid_dist integer = 1;
declare clid_dist integer = 50;
declare cust_type_dist integer = 2;
declare cust_id_dist integer = 500;
declare row_pos integer = 1;
declare sysid integer;
declare clid integer;
declare cust_type integer;
declare cust_id integer;
begin
while (:row_pos <= :max_rows) do begin
sysid = ceil(rand()*:sysid_dist);
clid = ceil(rand()*:clid_dist);
cust_type = ceil(rand()*:cust_type_dist);
cust_id = ceil(rand()*:cust_id_dist);

    insert into opt\_test \(sysid, clid, cust\_type, cust\_id, order\_no\) values \(:sysid, :clid, :cust\_type, :cust\_id, gen\_id\(gen\_order\_no, 1\)\);
    row\_pos = :row\_pos \+ 1;
end

end

^

set term ; ^
commit;

/**
* creating indices
*/

create index opt_test_idx1 on opt_test (clid, cust_type, cust_id);
create index opt_test_idx2 on opt_test (sysid, clid);
create descending index opt_test_idx3 on opt_test (sysid, clid, order_no);

set statistics index opt_test_idx1;
set statistics index opt_test_idx2;
set statistics index opt_test_idx3;
set statistics index pk_opt_test;

/**
* Here's the select. Please test with and without "sysid"
*/
select * from opt_test
where
--sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

=>

The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and polluting table in attachment. Example code below:

/**
* Here's the select. Please test with and without "sysid"
*/
select * from opt_test
where
--sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2018

Commented by: @dyemanov

Please post query plans for both v2.5.8 and v3.0.4.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2018

Commented by: @livius2

I can confirm this on FB3

query:
------------------------------------
select * from opt_test
where
sysid+0 = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

PLAN SORT (OPT_TEST INDEX (OPT_TEST_IDX1))
22 fetches
---------------------

query:
------------------------------------
select * from opt_test
where
sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

PLAN (OPT_TEST ORDER OPT_TEST_IDX3)
6707 fetches
---------------------

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2018

Commented by: Haakon Nergaard (haakon)

As the data is "random" the exact amount of fetches will vary slightly, but the difference should be pretty easy to spot.

FB2.5.8:

Query
------------------------------------------------
select * from opt_test
where
sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

Plan
------------------------------------------------
PLAN SORT ((OPT_TEST INDEX (OPT_TEST_IDX1)))
22 fetches
------------------------------------------------

FB3.0.4:

Query
------------------------------------------------
select * from opt_test
where
sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

Plan
------------------------------------------------
PLAN (OPT_TEST ORDER OPT_TEST_IDX3)
6409 fetches
------------------------------------------------

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2018

Modified by: Haakon Nergaard (haakon)

description: The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and polluting table in attachment. Example code below:

/**
* Here's the select. Please test with and without "sysid"
*/
select * from opt_test
where
--sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

=>

The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and populating table in attachment. Example code below:

/**
* Here's the select. Please test with and without "sysid"
*/
select * from opt_test
where
--sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 18, 2018

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

Link: This issue is related to CORE5795 [ CORE5795 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

Link: This issue is related to CORE5070 [ CORE5070 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

Link: This issue is related to CORE5481 [ CORE5481 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

Link: This issue is related to CORE5845 [ CORE5845 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

Fix Version: 3.0.5 [ 10885 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 9, 2018

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 9, 2018

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

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

Successfully merging a pull request may close this issue.

None yet
2 participants