index merge partial including should be cost based
- 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
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