pingcap / pingcap/tidb

planner: large estimation error with large IN-list and low NDV

Open
#65,636 0 comments 0 reactions 0 assignees View on GitHub
AI-Testing epic/cardinality-estimation 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)

Use the plan replayer file to reproduce:

[replayer_vpR7H6n0CV73qSveIRkg2A==_1768830696414020107.zip](https://github.com/user-attachments/files/24759812/replayer_vpR7H6n0CV73qSveIRkg2A._1768830696414020107.zip)

```
id estRows actRows task access object execution info operator info memory disk
Projection_4 35090.14 0 root time:1.75ms, open:124.9µs, close:42.5µs, loops:1, RU:0.48, Concurrency:5 1->Column#5 16.6 KB N/A
└─TableReader_8 35090.14 0 root time:1.66ms, open:123.1µs, close:11.3µs, loops:1, cop_task: {num: 1, max: 1.39ms, proc_keys: 0, tot_proc: 2.54µs, tot_wait: 67.8µs, copr_cache_hit_ratio: 1.00, build_task_duration: 13.2µs, max_distsql_concurrency: 1}, fetch_resp_duration: 1.49ms, rpc_info:{Cop:{num_rpc:1, total_time:1.35ms}} data:Selection_7 245 Bytes N/A
└─Selection_7 35090.14 0 cop[tikv] tikv_task:{time:98ms, loops:111}, scan_detail: {get_snapshot_time: 32.8µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 2.54µs, total_wait_time: 67.8µs, tikv_grpc_process_time: 511.1µs, tikv_grpc_wait_time: 25.6µs, tikv_wall_time: 856.9µs} in(det_test_db.lowcard.lowcard_datetime, 2023-01-01 00:00:00.000000, 2023-01-03 00:00:00.000000, 2023-01-05 00:00:00.000000, 2023-01-07 00:00:00.000000, 2023-01-09 00:00:00.000000, 2023-01-11 00:00:00.000000, 2023-01-13 00:00:00.000000, 2023-01-15 00:00:00.000000, 2023-01-17 00:00:00.000000, 2023-01-19 00:00:00.000000, 2023-01-21 00:00:00.000000, 2023-01-23 00:00:00.000000, 2023-01-25 00:00:00.000000, 2023-01-27 00:00:00.000000, 2023-01-29 00:00:00.000000, 2023-01-31 00:00:00.000000, 2023-02-02 00:00:00.000000, 2023-02-04 00:00:00.000000, 2023-02-06 00:00:00.000000, 2023-02-08 00:00:00.000000, 2023-02-10 00:00:00.000000, 2023-02-12 00:00:00.000000, 2023-02-14 00:00:00.000000, 2023-02-16 00:00:00.000000, 2023-02-18 00:00:00.000000, 2023-02-20 00:00:00.000000, 2023-02-22 00:00:00.000000, 2023-02-24 00:00:00.000000, 2023-02-26 00:00:00.000000, 2023-02-28 00:00:00.000000, 2023-03-02 00:00:00.000000, 2023-03-04 00:00:00.000000, 2023-03-06 00:00:00.000000, 2023-03-08 00:00:00.000000, 2023-03-10 00:00:00.000000, 2023-03-12 00:00:00.000000, 2023-03-14 00:00:00.000000, 2023-03-16 00:00:00.000000, 2023-03-18 00:00:00.000000, 2023-03-20 00:00:00.000000, 2023-03-22 00:00:00.000000, 2023-03-24 00:00:00.000000, 2023-03-26 00:00:00.000000, 2023-03-28 00:00:00.000000, 2023-03-30 00:00:00.000000, 2023-04-01 00:00:00.000000, 2023-04-03 00:00:00.000000, 2023-04-05 00:00:00.000000, 2023-04-07 00:00:00.000000, 2023-04-09 00:00:00.000000, 2023-04-11 00:00:00.000000, 2023-04-13 00:00:00.000000, 2023-04-15 00:00:00.000000, 2023-04-17 00:00:00.000000, 2023-04-19 00:00:00.000000, 2023-04-21 00:00:00.000000, 2023-04-23 00:00:00.000000, 2023-04-25 00:00:00.000000, 2023-04-27 00:00:00.000000, 2023-04-29 00:00:00.000000, 2023-05-01 00:00:00.000000, 2023-05-03 00:00:00.000000, 2023-05-05 00:00:00.000000, 2023-05-07 00:00:00.000000, 2023-05-09 00:00:00.000000, 2023-05-11 00:00:00.000000, 2023-05-13 00:00:00.000000, 2023-05-15 00:00:00.000000, 2023-05-17 00:00:00.000000, 2023-05-19 00:00:00.000000, 2023-05-21 00:00:00.000000, 2023-05-23 00:00:00.000000, 2023-05-25 00:00:00.000000, 2023-05-27 00:00:00.000000, 2023-05-29 00:00:00.000000, 2023-05-31 00:00:00.000000, 2023-06-02 00:00:00.000000, 2023-06-04 00:00:00.000000, 2023-06-06 00:00:00.000000, 2023-06-08 00:00:00.000000, 2023-06-10 00:00:00.000000, 2023-06-12 00:00:00.000000, 2023-06-14 00:00:00.000000, 2023-06-16 00:00:00.000000, 2023-06-18 00:00:00.000000, 2023-06-20 00:00:00.000000, 2023-06-22 00:00:00.000000, 2023-06-24 00:00:00.000000, 2023-06-26 00:00:00.000000, 2023-06-28 00:00:00.000000, 2023-06-30 00:00:00.000000, 2023-07-02 00:00:00.000000, 2023-07-04 00:00:00.000000, 2023-07-06 00:00:00.000000, 2023-07-08 00:00:00.000000, 2023-07-10 00:00:00.000000, 2023-07-12 00:00:00.000000, 2023-07-14 00:00:00.000000, 2023-07-16 00:00:00.000000, 2023-07-18 00:00:00.000000, 2023-07-20 00:00:00.000000, 2023-07-22 00:00:00.000000, 2023-07-24 00:00:00.000000, 2023-07-26 00:00:00.000000, 2023-07-28 00:00:00.000000, 2023-07-30 00:00:00.000000, 2023-08-01 00:00:00.000000, 2023-08-03 00:00:00.000000, 2023-08-05 00:00:00.000000, 2023-08-07 00:00:00.000000, 2023-08-09 00:00:00.000000, 2023-08-11 00:00:00.000000, 2023-08-13 00:00:00.000000, 2023-08-15 00:00:00.000000, 2023-08-17 00:00:00.000000, 2023-08-19 00:00:00.000000, 2023-08-21 00:00:00.000000, 2023-08-23 00:00:00.000000, 2023-08-25 00:00:00.000000, 2023-08-27 00:00:00.000000, 2023-08-29 00:00:00.000000, 2023-08-31 00:00:00.000000, 2023-09-02 00:00:00.000000, 2023-09-04 00:00:00.000000, 2023-09-06 00:00:00.000000, 2023-09-08 00:00:00.000000, 2023-09-10 00:00:00.000000, 2023-09-12 00:00:00.000000, 2023-09-14 00:00:00.000000, 2023-09-16 00:00:00.000000, 2023-09-18 00:00:00.000000, 2023-09-20 00:00:00.000000, 2023-09-22 00:00:00.000000, 2023-09-24 00:00:00.000000, 2023-09-26 00:00:00.000000, 2023-09-28 00:00:00.000000, 2023-09-30 00:00:00.000000, 2023-10-02 00:00:00.000000, 2023-10-04 00:00:00.000000, 2023-10-06 00:00:00.000000, 2023-10-08 00:00:00.000000, 2023-10-10 00:00:00.000000, 2023-10-12 00:00:00.000000, 2023-10-14 00:00:00.000000, 2023-10-16 00:00:00.000000, 2023-10-18 00:00:00.000000, 2023-10-20 00:00:00.000000, 2023-10-22 00:00:00.000000, 2023-10-24 00:00:00.000000, 2023-10-26 00:00:00.000000, 2023-10-28 00:00:00.000000, 2023-10-30 00:00:00.000000, 2023-11-01 00:00:00.000000, 2023-11-03 00:00:00.000000, 2023-11-05 00:00:00.000000, 2023-11-07 00:00:00.000000, 2023-11-09 00:00:00.000000, 2023-11-11 00:00:00.000000, 2023-11-13 00:00:00.000000, 2023-11-15 00:00:00.000000, 2023-11-17 00:00:00.000000, 2023-11-19 00:00:00.000000, 2023-11-21 00:00:00.000000, 2023-11-23 00:00:00.000000, 2023-11-25 00:00:00.000000, 2023-11-27 00:00:00.000000, 2023-11-29 00:00:00.000000, 2023-12-01 00:00:00.000000, 2023-12-03 00:00:00.000000, 2023-12-05 00:00:00.000000, 2023-12-07 00:00:00.000000, 2023-12-09 00:00:00.000000, 2023-12-11 00:00:00.000000, 2023-12-13 00:00:00.000000, 2023-12-15 00:00:00.000000, 2023-12-17 00:00:00.000000, 2023-12-19 00:00:00.000000, 2023-12-21 00:00:00.000000, 2023-12-23 00:00:00.000000, 2023-12-25 00:00:00.000000, 2023-12-27 00:00:00.000000, 2023-12-29 00:00:00.000000, 2023-12-31 00:00:00.000000), in(det_test_db.lowcard.lowcard_int, 0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48, 50, 52, 54, 56, 58, 60, 62, 64, 66, 68, 70, 72, 74, 76, 78, 80, 82, 84, 86, 88, 90, 92, 94, 96, 98, 100, 102, 104, 106, 108, 110, 112, 114, 116, 118, 120, 122, 124, 126, 128, 130), in(det_test_db.lowcard.lowcard_varchar, "1", "3", "5", "7", "9", "11", "13", "15", "17", "19", "21", "23", "25", "27", "29", "31", "33", "35", "37", "39", "41", "43", "45", "47", "49", "51", "53", "55", "57", "59", "61", "63", "65", "67", "69", "71", "73", "75", "77", "79", "81", "83", "85", "87", "89", "91", "93", "95", "97", "99", "101", "103", "105", "107", "109", "111", "113", "115", "117", "119", "121", "123", "125", "127", "129", "131", "133", "135", "137", "139", "141", "143", "145", "147", "149", "151", "153", "155", "157", "159", "161", "163", "165", "167", "169", "171", "173", "175", "177", "179", "181", "183", "185", "187", "189", "191", "193", "195", "197", "199", "201", "203", "205", "207", "209", "211", "213", "215", "217", "219", "221", "223", "225", "227", "229", "231", "233", "235", "237", "239", "241", "243", "245", "247", "249", "251", "253", "255", "257", "259", "261", "263", "265", "267", "269", "271", "273", "275", "277", "279", "281", "283", "285", "287", "289", "291", "293", "295", "297", "299", "301", "303", "305", "307", "309", "311", "313", "315", "317", "319", "321", "323", "325", "327", "329", "331", "333", "335", "337", "339", "341", "343", "345", "347", "349", "351", "353", "355", "357", "359", "361", "363", "365", "367", "369", "371", "373", "375", "377", "379", "381", "383", "385", "387", "389", "391", "393", "395", "397", "399", "401", "403", "405", "407", "409", "411", "413", "415", "417", "419", "421", "423", "425", "427", "429", "431", "433", "435", "437", "439", "441", "443", "445", "447", "449", "451", "453", "455", "457", "459", "461", "463", "465", "467", "469", "471", "473", "475", "477", "479", "481", "483", "485", "487", "489", "491", "493", "495", "497", "499", "501", "503", "505", "507", "509", "511", "513", "515", "517", "519", "521", "523", "525", "527", "529", "531", "533", "535", "537", "539", "541", "543", "545", "547", "549", "551", "553", "555", "557", "559", "561", "563", "565", "567", "569", "571", "573", "575", "577", "579", "581", "583", "585", "587", "589", "591", "593", "595", "597", "599", "601", "603", "605", "607", "609", "611", "613", "615", "617", "619", "621", "623", "625", "627", "629", "631", "633", "635", "637", "639", "641", "643", "645", "647", "649", "651", "653", "655", "657", "659", "661", "663", "665", "667", "669", "671", "673", "675", "677", "679", "681", "683", "685", "687", "689", "691", "693", "695", "697", "699", "701", "703", "705", "707", "709", "711", "713", "715", "717", "719", "721", "723", "725", "727", "729", "731", "733", "735", "737", "739", "741", "743", "745", "747", "749", "751", "753", "755", "757", "759", "761", "763", "765", "767", "769", "771", "773", "775", "777", "779", "781", "783", "785", "787", "789", "791", "793", "795", "797", "799", "801", "803", "805", "807", "809", "811", "813", "815", "817", "819", "821", "823", "825", "827", "829", "831", "833", "835", "837", "839", "841", "843", "845", "847", "849", "851", "853", "855", "857", "859", "861", "863", "865", "867", "869", "871", "873", "875", "877", "879", "881", "883", "885", "887", "889", "891", "893", "895", "897", "899", "901", "903", "905", "907", "909", "911", "913", "915", "917", "919", "921", "923", "925", "927", "929", "931", "933", "935", "937", "939", "941", "943", "945", "947", "949", "951", "953", "955", "957", "959", "961", "963", "965", "967", "969", "971", "973", "975", "977", "979", "981", "983", "985", "987", "989", "991", "993", "995", "997", "999") N/A N/A
└─TableFullScan_6 108572.00 108572 cop[tikv] table:LowCard tikv_task:{time:79ms, loops:111} keep order:false N/A N/A
```

### 2. What did you expect to see? (Required)

An acceptable estimation error.

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

Large estimation error: `35090.14 0`

### 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.