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

Firebird Core

Compound index cannot be used for filtering in some ORDER/GROUP BY queries

Created: 08/Jan/16 11:25 AM Updated: 09/Dec/18 01:56 PM

Component/s:

Engine

Affects Version/s:

3.0 RC1

Fix Version/s:

4.0 Beta 1, 3.0.5

Environment:

W10 x64 FB3 build 32268

Issue Links:

Relate

This issue relate to:

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

~~CORE-5965~~ FB3 Optimiser chooses less efficient plan than FB2.5 optimiser

QA Status:

Done successfully

Description

« Hide

create table test1 (ia integer not null,id integer not null, it integer not null, dt date not null, primary key (ia,id));
create table test2 (ia integer not null, it integer not null, dt date not null, primary key (ia,dt,it));
alter table test1 add CONSTRAINT fk_test2 FOREIGN key (ia,dt,it) REFERENCES test2 (ia,dt,it)

select * from test1
where ia=1 and dt='01/01/2015' and it=1
order by id;
plan : PLAN (TEST1 ORDER RDB$PRIMARY249)
performance very poor
the same problem with :
select id from test1
where ia=1 and dt='01/01/2015' and it=1
group by id

where without order or group works fine

All

Comments

Change History

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 08/Jan/16 05:46 PM

First of all, there's no point to post Beta 2 issues when RC1 is released two months ago. Please validate the issue still applies to RC1.
Then, if you think this plan is wrong then what you consider being the correct plan? Does any prior FB version works with a better plan?

[ Permalink | « Hide ]

Ryszard Skotarski added a comment - 08/Jan/16 06:07 PM

My mistake.
It should be RC2. Look at build number.

Yes in 2.5 it works fine.
Look at where clause, it contains all fields from foreign key .
but optymizer ignores where clause and chooses index for order.

Regards Ryszard

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 10/Jan/16 10:38 AM

First of all, the WHERE clause is not ignored. Primary key index is used for both filtering (IA = 1) and ordering (ORDER BY ID). Prior FB versions could never do such a trick. The problem you experience is that the condition IA = 1 seems to be not selective but the overall condition on {IA, DT, IT} fields is much more selective, so the plan SORT (TEST1 INDEX (FK_TEST2)) should be better. So it's not a bug but rather a downside of some other improvement - somebody wins, somebody loses. I will check whether the logic could be adjusted to cover all cases at once.

Dmitry Yemanov made changes - 10/Jan/16 10:38 AM

Field	Original Value	New Value
Assignee		Dmitry Yemanov [ dimitr ]

Dmitry Yemanov made changes - 10/Jan/16 10:41 AM

Summary	Wrong Plan Used	Compound index cannot be used for filtering in some ORDER/GROUP BY queries
Affects Version/s		3.0 RC1 [ 10584 ]
Affects Version/s	3.0 Beta 2 [ 10586 ]

Dmitry Yemanov made changes - 12/Feb/17 04:24 PM

Link

This issue relate to ~~CORE-5481~~ [ ~~CORE-5481~~ ]

Dmitry Yemanov made changes - 20/Nov/18 04:43 PM

Link

This issue relate to ~~CORE-5965~~ [ ~~CORE-5965~~ ]

Dmitry Yemanov made changes - 20/Nov/18 06:20 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 01:56 PM

Status	Resolved [ 5 ]	Resolved [ 5 ]
QA Status	No test	Done successfully

Pavel Zotov made changes - 09/Dec/18 01:56 PM

Status

Resolved [ 5 ]

Closed [ 6 ]