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
Compound index cannot be used for filtering in some ORDER/GROUP BY queries [CORE5070] #5357
Comments
Commented by: @dyemanov 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. |
Commented by: Ryszard Skotarski (rysios) My mistake. Yes in 2.5 it works fine. Regards Ryszard |
Commented by: @dyemanov 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. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovVersion: 3.0 RC1 [ 10584 ] summary: Wrong Plan Used => Compound index cannot be used for filtering in some ORDER/GROUP BY queries Version: 3.0 Beta 2 [ 10586 ] => |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 Beta 1 [ 10750 ] Fix Version: 3.0.5 [ 10885 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Ryszard Skotarski (rysios)
Relate to CORE5481
Relate to CORE5965
Votes: 1
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
Commits: 077a2a3 3103f72
The text was updated successfully, but these errors were encountered: