planner: avoid unnecessary double-read on IndexMerge
- 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:
Contributor 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