pingcap / pingcap/tidb

planner, executor: support unique Limit operator

Open
#63,850 2 comments 0 reactions 0 assignees View on GitHub
plan-rewrite sig/execution sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
For queries like `select distinct ... from ... limit 100`, TiDB has to apply `Limit` after the `Agg` (see the plan below):
```
mysql> explain select distinct t1.a, t2.a from t1, t2 limit 10;
+---------------------------------+--------------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+--------------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
| Limit_12 | 10.00 | root | | offset:0, count:10 |
| └─HashAgg_16 | 10.00 | root | | group by:test.t1.a, test.t2.a, funcs:firstrow(test.t1.a)->test.t1.a, funcs:firstrow(test.t2.a)->test.t2.a |
| └─HashJoin_33 | 100000000.00 | root | | CARTESIAN inner join |
| ├─TableReader_38(Build) | 10000.00 | root | | data:TableFullScan_37 |
| │ └─TableFullScan_37 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_36(Probe) | 10000.00 | root | | data:TableFullScan_35 |
| └─TableFullScan_35 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------------+--------------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
```

In the case above, we need to aggregate `100000000.00` rows, but actually we only need `10` rows and their order doesn't matter.

MariaDB can rewrite such SQLs from `Agg` to `unique Limit`, which has a much better performance (see the picture below):
I guess what the `unique Limit` does is just return 50 different rows according to `distinct ...`. Compared with our original `HashAgg`, it's much lighter and faster for this case.

Image

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.