Skip to content
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

Closed
firebird-automations opened this issue Jan 8, 2016 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Ryszard Skotarski (rysios)

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 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 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5481 [ CORE5481 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5965 [ CORE5965 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

Fix Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants