pingcap / pingcap/tidb

index join probe should detect partition key to do some dynamic pruning

Open
#67,440 0 comments 0 reactions 0 assignees View on GitHub
report/customer sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
```
| id | estRows | estCost | actRows | task | access object | execution info | operator info
| memory | disk |
| Insert_1 | 0 | 0 | 0 | root | | total_time: ~30m | insert into test.t3
| 0 | N/A |
| └─Projection_1 | ~1.0M | ~1.5e8 | 0 | root | | total_time: ~30m, concurrency: off | test.t1.c1, test.t1.c2, test.t1.c3, test.t1.c4,
test.t2.c1, test.t2.c5, test.t2.c6, coalesce(test.t2.c7, ?), test.t2.c8, test.t2.c9, test.t2.c4, coalesce(json_extract(test.t1.c10, ?), ?), abs(timestampdiff(?, test.t1.c4, test.t2.c4)), ? | ~46KB | N/A |
| └─IndexHashJoin_1 | ~1.0M | ~1.2e8 | 0 | root | | total_time: ~30m, inner_fetch_time: ~1h | inner join; outer key:test.t1.c2; inner key:test.t2.c2;
eq(test.t1.c2, test.t2.c2); time window on test.t2.c4 around test.t1.c4 | ~4MB | N/A |
| ├─Projection_2(Build) | ~5K | ~4.6e7 | ~5K | root | | total_time: ~1s | test.t1.c1, test.t1.c2, test.t1.c3, test.t1.c4,
test.t1.c10 | ~3MB | N/A |
| │ └─IndexLookUp_1 | ~5K | ~4.6e7 | ~5K | root | partition:p_a,p_b | index_task: ~1s, table_task: very small | limit embedded(offset: large, count: small)
| ~60MB | N/A |
| │ ├─Limit_1 | ~2.1M | ~5.5e8 | ~2.1M | cop[tikv] | | remote scan: ~1s | offset: ?, count: ?
| N/A | N/A |
| │ │ └─IndexRangeScan | ~2.1M | ~5.5e8 | ~2.1M | cop[tikv] | table:t1, index:idx_a(c4, c2) | keep order: true | range:[?, ?)
| N/A | N/A |
| │ └─TableRowIDScan | ~5K | ~2.2e6 | ~5K | cop[tikv] | table:t1 | total_time: small | keep order: false
| N/A | N/A |
| └─IndexLookUp_2(Probe) | ~1.0M | ~4.0e5 | ~7e2 | root | partition:all | total_time: ~1h, index_side dominates |
| ~256MB | N/A |
| ├─IndexRangeScan | ~1.0M | ~5.2e4 | ~7e2 | cop[tikv] | table:t2, index:idx_b(c2, c9, c4) | remote fetch: very slow | range decided by [eq(test.t2.c2, test.t1.c2),
eq(test.t2.c9, ?), le(test.t2.c4, date_add(test.t1.c4, ?, ?)), ge(test.t2.c4, date_sub(test.t1.c4, ?, ?))] | N/A | N/A |
| └─Selection_1 | ~1.0M | ~9.4e4 | ~7e2 | cop[tikv] | | total_time: small | ne(test.t2.c5, ?)
| N/A | N/A |
| └─TableRowIDScan | ~1.0M | ~8.4e4 | ~7e2 | cop[tikv] | table:t2 | total_time: small | keep order: false
| N/A | N/A |

```
bg:
t2 is a partition table with 600 partition
t2's partition key is DAY(t2.c4)
t2.idx_b is a local index

as you see here, t2 is a partition table , and t2.c4 is his partition key, and t2.c4 dynamic range can be decided by index join's outer row via predicate: le(test.t2.c4, date_add(test.t1.c4, ?, ?)), ge(test.t2.c4, date_sub(test.t1.c4, ?, ?)), it's range of datetime, so maybe we could catch this dynamic info to do some dynamic pruning rather than send all the rpc info to partitions

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.