Issue Details (XML | Word | Printable)

Key: CORE-5070
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Ryszard Skotarski
Votes: 1
Watchers: 3

If you were logged in you would be able to see more operations.
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:

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;
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: Ascending order - Click to sort in descending order
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?

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

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.