[#CORE-5965] FB3 Optimiser chooses less efficient plan than FB2.5 optimiser

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:

optimizer_test_setup.sql (2 kB)

Environment:

Tested on MacOS and Debian9, both 3.0.4 and 2.5.8

Issue Links:

Relate

This issue is related to:

~~CORE-5795~~ ORDER BY clause on compound index may disable usage of other indices

~~CORE-5070~~ Compound index cannot be used for filtering in some ORDER/GROUP BY queries

~~CORE-5481~~ Available indices are not used in some cases if ORDER BY expression is a filtered one

~~CORE-5845~~ ORDER BY on index can cause suboptimal index choices

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:

[ Permalink | « Hide ]

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

[ Permalink | « Hide ]

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