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.

Sign up for GitHub

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

Jump to bottom

Remove redundant branches in the OR list in filters #58998

Open
time-and-fate opened this issue Jan 16, 2025 · 0 comments · May be fixed by #58962
Open

Remove redundant branches in the OR list in filters #58998

time-and-fate opened this issue Jan 16, 2025 · 0 comments · May be fixed by #58962
Assignees
@time-and-fate
Labels
sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.

Comments

@time-and-fate
Copy link
Member

Enhancement

Though users don't deliberately write redundant expressions in the SQL, there are still redundant expressions, such as those from generated SQLs.

In our current implementation, we have RemoveDupExprs() and PropagateConstant() for AND lists. However, the correspondence for the OR list is lacking. So, there could be redundant filters in the OR list in the final plan, which can sometimes cause an inefficient plan.

For example:

create table t(a int, b int, c int, index ia(a), index ib(b), index ic(c));
explain select * from t where a = 1 or a = 1 or b = 2 or b = 2 or c = 3 or c = 3;
> explain select * from t where a = 1 or a = 1 or b = 2 or b = 2 or c = 3 or c = 3;
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
| id                             | estRows | task      | access object        | operator info                               |
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
| IndexMerge_15                  | 29.97   | root      |                      | type: union                                 |
| ├─IndexRangeScan_8(Build)      | 10.00   | cop[tikv] | table:t, index:ia(a) | range:[1,1], keep order:false, stats:pseudo |
| ├─IndexRangeScan_9(Build)      | 10.00   | cop[tikv] | table:t, index:ia(a) | range:[1,1], keep order:false, stats:pseudo |
| ├─IndexRangeScan_10(Build)     | 10.00   | cop[tikv] | table:t, index:ib(b) | range:[2,2], keep order:false, stats:pseudo |
| ├─IndexRangeScan_11(Build)     | 10.00   | cop[tikv] | table:t, index:ib(b) | range:[2,2], keep order:false, stats:pseudo |
| ├─IndexRangeScan_12(Build)     | 10.00   | cop[tikv] | table:t, index:ic(c) | range:[3,3], keep order:false, stats:pseudo |
| ├─IndexRangeScan_13(Build)     | 10.00   | cop[tikv] | table:t, index:ic(c) | range:[3,3], keep order:false, stats:pseudo |
| └─TableRowIDScan_14(Probe)     | 29.97   | cop[tikv] | table:t              | keep order:false, stats:pseudo              |
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
@time-and-fate time-and-fate added sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement. labels Jan 16, 2025
@time-and-fate time-and-fate self-assigned this Jan 16, 2025
@time-and-fate time-and-fate linked a pull request Jan 16, 2025 that will close this issue
Open
13 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@time-and-fate time-and-fate

Labels
sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

planner: remove redundant branches in the OR list | tidb-test=pr/2470 time-and-fate/tidb
1 participant
@time-and-fate