Issue Details (XML | Word | Printable)

Key: CORE-5965
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Haakon Nergaard
Votes: 0
Watchers: 4
Operations

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

FB3 Optimiser chooses less efficient plan than FB2.5 optimiser

Created: 16/Nov/18 08:48 PM   Updated: 09/Dec/18 08:59 AM
Component/s: Engine
Affects Version/s: 3.0.4
Fix Version/s: 4.0 Beta 1, 3.0.5

File Attachments: 1. File optimizer_test_setup.sql (2 kB)

Environment: Tested on MacOS and Debian9, both 3.0.4 and 2.5.8
Issue Links:
Relate
 

QA Status: Done successfully


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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Haakon Nergaard added a comment - 16/Nov/18 08:53 PM
Code for creating and populating table to reproduce the issue

Haakon Nergaard made changes - 16/Nov/18 08:53 PM
Field Original Value New Value
Attachment optimizer_test_setup.sql [ 13311 ]
Haakon Nergaard added a comment - 16/Nov/18 08:54 PM
Moved code to create and populate table to a file attachment

Haakon Nergaard made changes - 16/Nov/18 08:54 PM
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 (new.id is null) then
    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
Dmitry Yemanov added a comment - 17/Nov/18 06:34 AM
Please post query plans for both v2.5.8 and v3.0.4.

Karol Bieniaszewski added a comment - 17/Nov/18 07:51 AM
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
---------------------

Haakon Nergaard added a comment - 17/Nov/18 09:36 AM
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
------------------------------------------------

Haakon Nergaard made changes - 17/Nov/18 09:41 AM
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
Dmitry Yemanov made changes - 18/Nov/18 06:33 PM
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 20/Nov/18 04:43 PM
Link This issue is related to CORE-5795 [ CORE-5795 ]
Dmitry Yemanov made changes - 20/Nov/18 04:43 PM
Link This issue is related to CORE-5070 [ CORE-5070 ]
Dmitry Yemanov made changes - 20/Nov/18 04:43 PM
Link This issue is related to CORE-5481 [ CORE-5481 ]
Dmitry Yemanov made changes - 20/Nov/18 04:44 PM
Link This issue is related to CORE-5845 [ CORE-5845 ]
Dmitry Yemanov made changes - 20/Nov/18 06:21 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 4.0 Beta 1 [ 10750 ]
Fix Version/s 3.0.5 [ 10885 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 09/Dec/18 08:58 AM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status No test Done successfully
Pavel Zotov made changes - 09/Dec/18 08:59 AM
Status Resolved [ 5 ] Closed [ 6 ]