pingcap / pingcap/tidb

planner: remove redundant filter conditions

Open
#39,182 0 comments 0 reactions 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
```mysql
mysql> explain select count(*) from customer where c_id > 1000 and c_d_id > 100 and c_id > 0 and c_id > 1 and c_id > 2 and c_id > 3 and c_id > 10;
+--------------------------------+------------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+------------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_32 | 1.00 | root | | funcs:count(Column#27)->Column#23 |
| └─TableReader_34 | 1.00 | root | | data:ExchangeSender_33 |
| └─ExchangeSender_33 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_10 | 1.00 | mpp[tiflash] | | funcs:count(1)->Column#27 |
| └─Selection_31 | 681113.19 | mpp[tiflash] | | gt(chbenchmark.customer.c_d_id, 100), gt(chbenchmark.customer.c_id, 0), gt(chbenchmark.customer.c_id, 1), gt(chbenchmark.customer.c_id, 10), gt(chbenchmark.customer.c_id, 1000), gt(chbenchmark.customer.c_id, 2), gt(chbenchmark.customer.c_id, 3) |
| └─TableFullScan_30 | 3000000.00 | mpp[tiflash] | table:customer | keep order:false |
+--------------------------------+------------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

# obviously, it is equal to
mysql> explain select count(*) from customer where c_id > 1000 and c_d_id > 100;
+--------------------------------+------------+--------------+----------------+---------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+------------+--------------+----------------+---------------------------------------------------------------------------+
| HashAgg_32 | 1.00 | root | | funcs:count(Column#27)->Column#23 |
| └─TableReader_34 | 1.00 | root | | data:ExchangeSender_33 |
| └─ExchangeSender_33 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_10 | 1.00 | mpp[tiflash] | | funcs:count(1)->Column#27 |
| └─Selection_31 | 681113.19 | mpp[tiflash] | | gt(chbenchmark.customer.c_d_id, 100), gt(chbenchmark.customer.c_id, 1000) |
| └─TableFullScan_30 | 3000000.00 | mpp[tiflash] | table:customer | keep order:false |
+--------------------------------+------------+--------------+----------------+---------------------------------------------------------------------------+
6 rows in set (0.00 sec)
```

`and c_id > 0 and c_id > 1 and c_id > 2 and c_id > 3 and c_id > 10` can be removed.

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.