pingcap / pingcap/tidb

planner: avoid unnecessary double-read on IndexMerge

Open
#70,563 1 comment 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
The 2 queries below should be able to avoid double-read, because these 2 queries don't need any columns from the table side.
```
CREATE TABLE t (
id BIGINT NOT NULL AUTO_INCREMENT,
tokens JSON,
KEY idx (
(CAST(tokens AS CHAR(64) ARRAY))
)
);

EXPLAIN SELECT /*+ USE_INDEX(o, idx) */ tokens
FROM t
WHERE 'xxx' MEMBER OF (tokens);
+--------------------------------+---------+-----------+------------------------------------------------------+-----------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-----------+------------------------------------------------------+-----------------------------------------------------+
| IndexMerge_11 | 10.00 | root | | type: union |
| ├─IndexRangeScan_9(Build) | 10.00 | cop[tikv] | table:t, index:idx(cast(`tokens` as char(64) array)) | range:["xxx","xxx"], keep order:false, stats:pseudo |
| └─TableRowIDScan_10(Probe) | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+--------------------------------+---------+-----------+------------------------------------------------------+-----------------------------------------------------+

EXPLAIN SELECT /*+ USE_INDEX(o, idx) */ COUNT(1)
FROM t
WHERE 'xxx' MEMBER OF (tokens);
+----------------------------------+---------+-----------+------------------------------------------------------+-----------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+------------------------------------------------------+-----------------------------------------------------+
| StreamAgg_12 | 1.00 | root | | funcs:count(1)->Column#6 |
| └─IndexMerge_35 | 10.00 | root | | type: union |
| ├─IndexRangeScan_33(Build) | 10.00 | cop[tikv] | table:t, index:idx(cast(`tokens` as char(64) array)) | range:["xxx","xxx"], keep order:false, stats:pseudo |
| └─TableRowIDScan_34(Probe) | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+------------------------------------------------------+-----------------------------------------------------+
```

Below is a real case from a customer's workload when testing:

Image

Contributor guide

Open the contributing guide

Research direction

Start by tracing the planner's IndexMerge plan generation and compare the two EXPLAIN outputs in the issue. The work is done when both queries avoid the unnecessary TableRowIDScan while preserving correct query results.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, performance
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.