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

prioritize low selectivity indexes in the greedy search procedure of Selectivity() #19904

Closed
eurekaka opened this issue Sep 9, 2020 · 0 comments · Fixed by #20038
Closed
Assignees
Labels
component/statistics sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.

Comments

@eurekaka
Copy link
Contributor

eurekaka commented Sep 9, 2020

Development Task

Consider this case:

create table t(a int, b int, c int, d int, key ab(a,b), key cd(c,d), key bc(b,c));
explain select * from t where a = 1 and b = 1 and c = 1 and d = 1;

When computing the total selectivity of this filter, current greedy algorithm would choose index ab and cd to cover the filters, and multiply these 2 selectivities computed.

However, if column b and c have some explicit or implicit functional dependency, specifically, there is no record satisfying b = 1 and c = 1 at all, the final selectivity computed would be too high. Instead, if we can put index bc into the covering index set, then the final selectivity would be the correct 0.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
component/statistics sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
1 participant