pingcap / pingcap/tidb

planner: remove useless selection when the column in where clause is the same as partition clause.

Open
#37,041 11 comments 1 reaction 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

table `github_events` partition by column `type`, so the return rows of table scan are all `WatchEvent` type. There is no need to do selection again in this case.
```sql
mysql> explain analyze SELECT action, count(*) FROM github_events WHERE type = 'WatchEvent' GROUP BY action;
+----------------------------------------+---------------+-----------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------+---------------+-----------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------+
| Projection_5 | 1.00 | 1 | root | | time:1.89s, loops:2, Concurrency:OFF | gharchive_dev.github_events.action, Column#33 | 1016 Bytes | N/A |
| └─TableReader_47 | 1.00 | 1 | root | partition:watch_event | time:1.89s, loops:2, cop_task: {num: 5, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_46 | N/A | N/A |
| └─ExchangeSender_46 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:1.89s, min:1.89s, avg: 1.89s, p80:1.89s, p95:1.89s, iters:1, tasks:4, threads:88} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_42 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:1.89s, min:1.89s, avg: 1.89s, p80:1.89s, p95:1.89s, iters:1, tasks:4, threads:88} | Column#33, gharchive_dev.github_events.action | N/A | N/A |
| └─HashAgg_43 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:1.89s, min:1.89s, avg: 1.89s, p80:1.89s, p95:1.89s, iters:1, tasks:4, threads:4} | group by:gharchive_dev.github_events.action, funcs:sum(Column#40)->Column#33, funcs:firstrow(gharchive_dev.github_events.action)->gharchive_dev.github_events.action | N/A | N/A |
| └─ExchangeReceiver_45 | 1.00 | 4 | mpp[tiflash] | | tiflash_task:{proc max:1.89s, min:1.89s, avg: 1.89s, p80:1.89s, p95:1.89s, iters:4, tasks:4, threads:88} | | N/A | N/A |
| └─ExchangeSender_44 | 1.00 | 4 | mpp[tiflash] | | tiflash_task:{proc max:1.89s, min:0s, avg: 471.9ms, p80:1.89s, p95:1.89s, iters:1024, tasks:4, threads:88} | ExchangeType: HashPartition, Hash Cols: [name: gharchive_dev.github_events.action, collate: utf8mb4_unicode_ci] | N/A | N/A |
| └─HashAgg_10 | 1.00 | 4 | mpp[tiflash] | | tiflash_task:{proc max:1.89s, min:0s, avg: 471.4ms, p80:1.89s, p95:1.89s, iters:1024, tasks:4, threads:4} | group by:gharchive_dev.github_events.action, funcs:count(1)->Column#40 | N/A | N/A |
| └─Selection_41 | 305500065.00 | 323645427 | mpp[tiflash] | | tiflash_task:{proc max:1.18s, min:0s, avg: 296.2ms, p80:1.18s, p95:1.18s, iters:5720, tasks:4, threads:88} | eq(gharchive_dev.github_events.type, "WatchEvent") | N/A | N/A |
| └─TableFullScan_40 | 4875369803.00 | 323645427 | mpp[tiflash] | table:github_events | tiflash_task:{proc max:218.8ms, min:0s, avg: 54.7ms, p80:218.8ms, p95:218.8ms, iters:5720, tasks:4, threads:88} | keep order:false, PartitionTableScan:true | N/A | N/A |
+----------------------------------------+---------------+-----------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------+
10 rows in set (1.90 sec)
```

In general, when the column in where clause is the same as partition clause, there is no need to do the selection.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.