pingcap / pingcap/tidb

The estimation of string/bytes types mv index within a single bucket of the histogram is not accurate

Open
#50,598 4 comments 0 reactions 1 assignee View on GitHub

@time-and-fate is already working on this.

Since Jan 19, 2024.

affects-7.6 affects-8.1 severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.6k
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
-> *
-> FROM
-> items
-> WHERE
-> 57260686 member of (feed_profile_ids)
-> AND "[OC8p1763XTkt.org/s/link](http://oc8p1763xtkt.org/s/link)" member of (normalized_short_link_clusters)
-> AND json_overlaps(
-> product_sources,
-> '["M1","M2","M3"]'
-> )
-> LIMIT
-> 1 ;
```
### 2. What did you expect to see? (Required)
```

| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
| Limit_10 | 0.00 | 673.26 | 0 | root | | time:13.4ms, loops:1 | offset:0, count:10 | N/A | N/A |
| └─Selection_11 | 0.00 | 673.26 | 0 | root | | time:13.4ms, loops:1 | json_overlaps(test.items.product_sources, cast("["FEED_MERCHANT","MERCHANT_UI","CAROUSEL_CRAWLER"]", json BINARY)) | 33.3 KB | N/A |
| └─IndexMerge_18 | 0.00 | 673.26 | 1 | root | | time:13.3ms, loops:2, index_task:{fetch_handle:3.455965ms, merge:5.423µs}, table_task:{num:1, concurrency:5, fetch_row:9.799043ms, wait_time:3.514198ms} | type: union | 49.9 KB | N/A |
| ├─IndexRangeScan_15(Build) | 8.30 | 2145.61 | 10 | cop[tikv] | table:items, index:feed_profile_ids(cast(`feed_profile_ids` as unsigned array)) | time:3.45ms, loops:3, cop_task: {num: 1, max: 3.42ms, proc_keys: 10, tot_proc: 569.6µs, tot_wait: 2.56ms, rpc_num: 1, rpc_time: 3.41ms, copr_cache_hit_ratio: 0.00, build_task_duration: 13.2µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 10, total_process_keys_size: 1020, total_keys: 11, get_snapshot_time: 9.56µs, rocksdb: {key_skipped_count: 10, block: {cache_hit_count: 1, read_count: 1, read_byte: 13.4 KB, read_time: 445.9µs}}} | range:[59405969,59405969], keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized] | N/A | N/A |
| └─Selection_17(Probe) | 0.00 | 5271.80 | 1 | cop[tikv] | | time:9.68ms, loops:2, cop_task: {num: 10, max: 7.95ms, min: 0s, avg: 1.45ms, p95: 7.95ms, max_proc_keys: 1, p95_proc_keys: 1, tot_proc: 3.66ms, tot_wait: 10.4ms, rpc_num: 6, rpc_time: 14.4ms, copr_cache_hit_ratio: 0.00, build_task_duration: 60µs, max_distsql_concurrency: 1, max_extra_concurrency: 3, store_batch_num: 4}, tikv_task:{proc max:1ms, min:0s, avg: 300µs, p80:1ms, p95:1ms, iters:10, tasks:10}, scan_detail: {total_process_keys: 10, total_process_keys_size: 206864, total_keys: 20, get_snapshot_time: 115.4µs, rocksdb: {key_skipped_count: 10, block: {cache_hit_count: 117, read_count: 4, read_byte: 97.2 KB, read_time: 674.7µs}}} | json_memberof(cast("OC8p1171XTkt.net/s/link", json BINARY), test.items.normalized_short_link_clusters) | N/A | N/A |
| └─TableRowIDScan_16 | 8.30 | 4857.52 | 10 | cop[tikv] | table:items | tikv_task:{proc max:1ms, min:0s, avg: 300µs, p80:1ms, p95:1ms, iters:10, tasks:10} | keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized] | N/A | N/A |

```

### 3. What did you see instead (Required)
```

| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
| Limit_10 | 0.00 | 762.16 | 0 | root | | time:2.49s, loops:1 | offset:0, count:10 | N/A | N/A |
| └─Selection_11 | 0.00 | 762.16 | 0 | root | | time:2.49s, loops:1 | json_overlaps(test.items.product_sources, cast("["FEED_MERCHANT","MERCHANT_UI","CAROUSEL_CRAWLER"]", json BINARY)) | 33.3 KB | N/A |
| └─IndexMerge_18 | 0.00 | 762.16 | 1 | root | | time:2.49s, loops:2, index_task:{fetch_handle:1.986440012s, merge:279.519351ms}, table_task:{num:28, concurrency:5, fetch_row:8.100928191s, wait_time:4.101294918s} | type: union | 30.5 MB | N/A |
| ├─IndexRangeScan_15(Build) | 10.88 | 2883.70 | 492380 | cop[tikv] | table:items, index:normalized_short_link_clusters(cast(`normalized_short_link_clusters` as char(1000) array), point_of_sale_country) | time:1.86s, loops:485, cop_task: {num: 18, max: 427ms, min: 640.3µs, avg: 110.3ms, p95: 427ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 505.5ms, tot_wait: 54.5ms, rpc_num: 18, rpc_time: 1.98s, copr_cache_hit_ratio: 0.00, build_task_duration: 1.08ms, max_distsql_concurrency: 1}, tikv_task:{proc max:400ms, min:0s, avg: 103.3ms, p80:192ms, p95:400ms, iters:552, tasks:18}, scan_detail: {total_process_keys: 492380, total_process_keys_size: 66039117, total_keys: 504126, get_snapshot_time: 127.8µs, rocksdb: {delete_skipped_count: 101, key_skipped_count: 504209, block: {cache_hit_count: 252, read_count: 1552, read_byte: 17.2 MB, read_time: 109.2ms}}} | range:["OC8p1632XTkt.net/s/link","OC8p1632XTkt.net/s/link"], keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized] | N/A | N/A |
| └─Selection_17(Probe) | 0.00 | 4615.94 | 1 | cop[tikv] | | time:7.38s, loops:29, cop_task: {num: 265142, max: 2.39ms, min: 0s, avg: 7.14ms, p95: 22.3ms, max_proc_keys: 9, p95_proc_keys: 4, tot_proc: 3m44.6s, tot_wait: 1h18m23.6s, rpc_num: 53197, rpc_time: 31m33s, copr_cache_hit_ratio: 0.00, build_task_duration: 563ms, max_distsql_concurrency: 1, max_extra_concurrency: 320, store_batch_num: 211945}, tikv_task:{proc max:0s, min:0s, avg: 6.41ms, p80:8ms, p95:32ms, iters:265142, tasks:265142}, scan_detail: {total_process_keys: 492380, total_process_keys_size: 10172790635, total_keys: 984760, get_snapshot_time: 24.3s, rocksdb: {key_skipped_count: 492380, block: {cache_hit_count: 8125571, read_count: 645430, read_byte: 13.7 GB, read_time: 1m46.1s}}} | json_memberof(cast(80638367, json BINARY), test.items.feed_profile_ids) | N/A | N/A |
| └─TableRowIDScan_16 | 10.88 | 4073.03 | 492380 | cop[tikv] | table:items | tikv_task:{proc max:0s, min:0s, avg: 6.41ms, p80:8.1ms, p95:32ms, iters:265142, tasks:265142} | keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized] | N/A | N/A |

```
The estimation of string/bytes types within a single bucket of the histogram is not accurate

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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.