pingcap / pingcap/tidb

Two related mv indexes of the same column are selected at the same time

Open
#50,291 0 comments 0 reactions 1 assignee Claimed by @time-and-fate View on GitHub
affects-7.6 feature/developing may-affects-5.4 may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

/** Q5 **/
SELECT
--hide--
FROM
items
WHERE
(
? member of (`normalized_short_link_clusters`)
AND (`items`.`point_of_sale_country` = 'JP')
AND NOT (? member of (`product_sources`))
AND NOT (? member of (`product_sources`))
AND NOT (? member of (`product_sources`))
)
LIMIT
100

### 2. What did you expect to see? (Required)
```
+------------------------------------+---------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+---------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_7 | 1000.00 | 585 | root | | time:106ms, loops:2, RU:1071.004015, Concurrency:OFF | test.items.item_primary_key, test.items.created_time, test.items.arrival_time, test.items.updated_time, test.items.item_id, test.items.item_set_id, test.items.product_id, test.items.product_set_id, test.items.reconciled_data, test.items.source_data, test.items.timestamp_data, test.items.point_of_sale_country, test.items.product_sources, test.items.merchant_id, test.items.merchant_item_id, test.items.merchant_item_set_id, test.items.domains, test.items.image_signatures, test.items.normalized_short_link_clusters, test.items.canonical_links, test.items.feed_item_ids, test.items.feed_profile_ids, test.items.cdc_new_values, test.items.cdc_old_values, test.items.cdc_change_indicator | 12.4 MB | N/A |
| └─Limit_9 | 1000.00 | 585 | root | | time:105.9ms, loops:2 | offset:0, count:1000 | N/A | N/A |
| └─IndexMerge_21 | 10.00 | 585 | root | | time:105.9ms, loops:2, index_task:{fetch_handle:6.394442ms, merge:740.242µs}, table_task:{num:2, concurrency:5, fetch_row:148.038061ms, wait_time:12.082352ms} | type: union | 13.3 MB | N/A |
| ├─IndexRangeScan_17(Build) | 12.50 | 3007 | cop[tikv] | table:items, index:normalized_short_link_clusters(cast(`normalized_short_link_clusters` as char(1000) array), point_of_sale_country) | time:5.41ms, loops:5, cop_task: {num: 4, max: 1.94ms, min: 999.5µs, avg: 1.41ms, p95: 1.94ms, max_proc_keys: 1311, p95_proc_keys: 1311, tot_proc: 3.23ms, tot_wait: 320.3µs, rpc_num: 4, rpc_time: 5.62ms, copr_cache_hit_ratio: 0.00, build_task_duration: 334µs, max_distsql_concurrency: 1}, tikv_task:{proc max:1ms, min:1ms, avg: 1ms, p80:1ms, p95:1ms, iters:18, tasks:4}, scan_detail: {total_process_keys: 3007, total_process_keys_size: 414652, total_keys: 3011, get_snapshot_time: 266.6µs, rocksdb: {key_skipped_count: 3007, block: {cache_hit_count: 7, read_count: 10, read_byte: 108.9 KB, read_time: 54.7µs}}} | range:[0x4F4338703039343458546B742E6F72672F732F6C696E6B "EN",0x4F4338703039343458546B742E6F72672F732F6C696E6B "EN"], keep order:false, stats:partial[point_of_sale_country:unInitialized, product_sources:unInitialized, normalized_short_link_clusters:unInitialized] | N/A | N/A |
| └─Limit_20(Probe) | 10.00 | 585 | cop[tikv] | | time:140.2ms, loops:4, cop_task: {num: 2038, max: 17.2ms, min: 0s, avg: 1.9ms, p95: 10.5ms, max_proc_keys: 5, p95_proc_keys: 3, tot_proc: 1.01s, tot_wait: 7.36s, rpc_num: 998, rpc_time: 3.92s, copr_cache_hit_ratio: 0.00, build_task_duration: 6.26ms, max_distsql_concurrency: 1, max_extra_concurrency: 102, store_batch_num: 1130, store_batch_fallback_num: 251}, backoff{regionMiss: 4ms}, tikv_task:{proc max:12ms, min:0s, avg: 576.1µs, p80:1ms, p95:1ms, iters:2038, tasks:2038}, scan_detail: {total_process_keys: 3007, total_process_keys_size: 62107435, total_keys: 6014, get_snapshot_time: 5.18s, rocksdb: {key_skipped_count: 3007, block: {cache_hit_count: 6325, read_count: 5703, read_byte: 110.3 MB, read_time: 206.4ms}}} | offset:0, count:1000 | N/A | N/A |
| └─Selection_19 | 10.00 | 585 | cop[tikv] | | tikv_task:{proc max:12ms, min:0s, avg: 575.6µs, p80:1ms, p95:1ms, iters:2038, tasks:2038} | not(istrue_with_null(json_memberof(cast("FEED_MERCHANT", json BINARY), test.items.product_sources))), not(istrue_with_null(json_memberof(cast("MERCHANT_PLATFORM", json BINARY), test.items.product_sources))), not(istrue_with_null(json_memberof(cast("MERCHANT_UI", json BINARY), test.items.product_sources))) | N/A | N/A |
| └─TableRowIDScan_18 | 12.50 | 3007 | cop[tikv] | table:items | tikv_task:{proc max:12ms, min:0s, avg: 557.9µs, p80:1ms, p95:1ms, iters:2038, tasks:2038} | keep order:false, stats:partial[point_of_sale_country:unInitialized, product_sources:unInitialized, normalized_short_link_clusters:unInitialized] | N/A | N/A |
+------------------------------------+---------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
```
### 3. What did you see instead (Required)
[0f00268d1806ce9238f621b41e1f36cb875adfae83aa493f9ec4c792362733ab.txt](https://github.com/pingcap/tidb/files/13886622/0f00268d1806ce9238f621b41e1f36cb875adfae83aa493f9ec4c792362733ab.txt)
### 4. What is your TiDB version? (Required)

master

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.