pingcap / pingcap/tidb

`Limit` is not push down through IndexMerge

Open
#49,908 1 comment 0 reactions 1 assignee Claimed by @time-and-fate View on GitHub
affects-7.6 may-affects-5.4 may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 report/customer severity/moderate sig/planner type/bug
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)
```
SELECT
--hide
FROM
items
WHERE
(
(? member of (`normalized_short_link_clusters`))
AND (`items`.`point_of_sale_country` = ?)
)
LIMIT
1000 [arguments: ("OC8p0345XTkt.org/s/link", "JP")]

SELECT item_primary_key FROM items WHERE ? member of (domains) AND ? member of (image_signatures) AND ? member of (feed_profile_ids) AND ? member of (normalized_short_link_clusters) AND ? member of (canonical_links) AND ? member of (feed_item_ids) LIMIT 1000

```

### 2. What did you expect to see? (Required)
```
+----------------------------------+---------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+---------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_7 | 1000.00 | 1000 | root | | time:85.5ms, loops:2, RU:138.701127, 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 | 23.8 MB | N/A |
| └─IndexMerge_20 | 0.06 | 1000 | root | | time:85.5ms, loops:2, index_task:{fetch_handle:5.298859ms, merge:267.844µs}, table_task:{num:1, concurrency:5, fetch_row:45.085116ms, wait_time:5.693746ms} | type: union, limit embedded(offset:0, count:1000) | 23.9 MB | N/A |
| ├─Limit_19(Build) | 0.06 | 1696 | cop[tikv] | | time:4.97ms, loops:1, cop_task: {num: 3, max: 2.43ms, min: 928.3µs, avg: 1.62ms, p95: 2.43ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 1.52ms, tot_wait: 56.2µs, rpc_num: 3, rpc_time: 4.84ms, copr_cache_hit_ratio: 0.00, build_task_duration: 16.1µs, max_distsql_concurrency: 1}, tikv_task:{proc max:1ms, min:0s, avg: 333.3µs, p80:1ms, p95:1ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 244870, total_keys: 1699, get_snapshot_time: 22.4µs, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 12}}} | offset:0, count:1000 | N/A | N/A |
| │ └─IndexRangeScan_17 | 0.06 | 1696 | cop[tikv] | table:items, index:normalized_short_link_clusters(cast(`normalized_short_link_clusters` as char(1000) array), point_of_sale_country) | tikv_task:{proc max:1ms, min:0s, avg: 333.3µs, p80:1ms, p95:1ms, iters:12, tasks:3} | range:[0x4F4338703132333458546B742E636F6D2F732F6C696E6B "EN",0x4F4338703132333458546B742E636F6D2F732F6C696E6B "EN"], keep order:false, stats:partial[normalized_short_link_clusters:unInitialized] | N/A | N/A |
| └─TableRowIDScan_18(Probe) | 0.06 | 1000 | cop[tikv] | table:items | time:43.3ms, loops:2, cop_task: {num: 662, max: 5.32ms, min: 0s, avg: 376.2µs, p95: 2.6ms, max_proc_keys: 5, p95_proc_keys: 3, tot_proc: 81.9ms, tot_wait: 146.9ms, rpc_num: 134, rpc_time: 247.2ms, copr_cache_hit_ratio: 0.00, build_task_duration: 1.47ms, max_distsql_concurrency: 1, max_extra_concurrency: 52, store_batch_num: 528}, tikv_task:{proc max:9ms, min:0s, avg: 98.2µs, p80:0s, p95:1ms, iters:662, tasks:662}, scan_detail: {total_process_keys: 999, total_process_keys_size: 20641665, total_keys: 1998, get_snapshot_time: 48.4ms, rocksdb: {key_skipped_count: 999, block: {cache_hit_count: 5720}}} | keep order:false, stats:partial[normalized_short_link_clusters:unInitialized] | N/A | N/A |
+----------------------------------+---------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
```

### 3. What did you see instead (Required)
[1ee6a47a4e1ec389c0e43267b9a19ac8dfa57a699d7cf768a739695ddd9615b0.txt](https://github.com/pingcap/tidb/files/13791387/1ee6a47a4e1ec389c0e43267b9a19ac8dfa57a699d7cf768a739695ddd9615b0.txt)
[685e07f5609325d933c037bd6468bb7ae7dbe9e8cd2cbe9c7c8e7c265e7685d4.txt](https://github.com/pingcap/tidb/files/13791388/685e07f5609325d933c037bd6468bb7ae7dbe9e8cd2cbe9c7c8e7c265e7685d4.txt)

### 4. What is your TiDB version? (Required)

master
6e72492d4fc90cc4b2f2bcb48092ee84095710c0

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.