pingcap / pingcap/tidb

index merge partial including should be cost based

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

Description

## Enhancement
```
plan1
mysql> explain format="verbose" SELECT /*+ use_index_merge(items, domains, image_signatures) */ item_primary_key FROM items WHERE "c5de2" member of (domains) AND "30e84yt734" member of (image_signatures) AND json_contains(feed_profile_ids, '[63532428,83825474,87933365]');
+---------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+---------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.00 | 141.72 | root | | test.items.item_primary_key |
| └─IndexMerge_9 | 0.00 | 141.72 | root | | type: intersection |
| ├─IndexRangeScan_5(Build) | 1.03 | 291.94 | cop[tikv] | table:items, index:domains(cast(`domains` as char(253) array)) | range:["c5de2","c5de2"], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| ├─IndexRangeScan_6(Build) | 1.00 | 324.43 | cop[tikv] | table:items, index:image_signatures(cast(`image_signatures` as char(32) array)) | range:["30e84yt734","30e84yt734"], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| └─Selection_8(Probe) | 0.00 | 0.00 | cop[tikv] | | json_contains(test.items.feed_profile_ids, cast("[63532428,83825474,87933365]", json BINARY)) |
| └─TableRowIDScan_7 | 0.00 | 0.00 | cop[tikv] | table:items | keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
+---------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.05 sec)

plan2
mysql> explain format="verbose" SELECT item_primary_key FROM items WHERE "c5de2" member of (domains) AND "30e84yt734" member of (image_signatures) AND json_contains(feed_profile_ids, '[63532428,83825474,87933365]');
+---------------------------------+---------+---------+-----------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+---------------------------------+---------+---------+-----------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.00 | 100.66 | root | | test.items.item_primary_key |
| └─IndexMerge_11 | 0.00 | 100.66 | root | | type: union |
| ├─IndexRangeScan_8(Build) | 1.03 | 291.94 | cop[tikv] | table:items, index:domains(cast(`domains` as char(253) array)) | range:["c5de2","c5de2"], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| └─Selection_10(Probe) | 0.00 | 702.32 | cop[tikv] | | json_contains(test.items.feed_profile_ids, cast("[63532428,83825474,87933365]", json BINARY)), json_memberof(cast("30e84yt734", json BINARY), test.items.image_signatures) |
| └─TableRowIDScan_9 | 1.03 | 599.86 | cop[tikv] | table:items | keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
+---------------------------------+---------+---------+-----------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set, 1 warning (0.05 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | [items,IndexMerge{domains},IndexMerge{image_signatures},IndexMerge{feed_profile_ids,feed_profile_ids,feed_profile_ids},IndexMerge{domains,image_signatures,feed_profile_ids,feed_profile_ids,feed_profile_ids}] remain after pruning paths for items given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

plan3
mysql> explain format="verbose" SELECT /*+ use_index_merge(items, domains, image_signatures, feed_profile_ids) */ item_primary_key FROM items WHERE "c5de2" member of (domains) AND "30e84yt734" member of (image_signatures) AND json_contains(feed_profile_ids, '[63532428,83825474,87933365]');
+----------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.00 | 1087.07 | root | | test.items.item_primary_key |
| └─IndexMerge_11 | 0.00 | 1087.07 | root | | type: intersection |
| ├─IndexRangeScan_5(Build) | 1.03 | 291.94 | cop[tikv] | table:items, index:domains(cast(`domains` as char(253) array)) | range:["c5de2","c5de2"], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| ├─IndexRangeScan_6(Build) | 1.00 | 324.43 | cop[tikv] | table:items, index:image_signatures(cast(`image_signatures` as char(32) array)) | range:["30e84yt734","30e84yt734"], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| ├─IndexRangeScan_7(Build) | 8.10 | 2096.51 | cop[tikv] | table:items, index:feed_profile_ids(cast(`feed_profile_ids` as unsigned array)) | range:[63532428,63532428], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| ├─IndexRangeScan_8(Build) | 8.10 | 2096.51 | cop[tikv] | table:items, index:feed_profile_ids(cast(`feed_profile_ids` as unsigned array)) | range:[83825474,83825474], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| ├─IndexRangeScan_9(Build) | 8.10 | 2096.51 | cop[tikv] | table:items, index:feed_profile_ids(cast(`feed_profile_ids` as unsigned array)) | range:[87933365,87933365], keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
| └─TableRowIDScan_10(Probe) | 0.00 | 0.00 | cop[tikv] | table:items | keep order:false, stats:partial[domains:unInitialized, image_signatures:unInitialized, feed_profile_ids:unInitialized] |
+----------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.07 sec)
```

currently, IndexMerge{domains,image_signatures} is not generated as an independent index merge path, instead, generating IndexMerge{domains,image_signatures,feed_profile_ids,feed_profile_ids,feed_profile_ids}, the choice of index merge partial path shouldn't be determined at deriveStats staged, it should be cost-based.

from the plan description, obviously, plan1 is better than plan2(default choice), and both better than plan3

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the three EXPLAIN FORMAT="verbose" queries and comparing their index-merge plans, costs, and pruning warning. The payload names no implementation files or tests; done should demonstrate that partial index-merge choices are selected according to cost rather than producing the shown suboptimal plan.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, performance
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.