planner: regression after #63077 causes severe underestimation for out-of-range block_time and wrong index choice
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step (Required)
I reproduced this with a plan replayer captured from `v8.5.4-nextgen.202510.13` and then used `git bisect` to narrow down the regression.
Query from the replayer:
```sql
SELECT
...
FROM `t_table`
WHERE `blockchain` = 'TRX'
AND `token` = 'USDT'
AND `state` = 2
AND `block_time` >= 1773388608
AND `from_address` = 'abc...xyz'
LIMIT 1;
```
Observed regression with bisect:
- first bad commit: `72a540b804224ef5e19f06c449874d33f7b50cbc`
- subject: `Planner: Add min/max for out of range (#63077)`
- previous good commit: `09f1070a31aa8c00f23b0c4b127d5cff8d36b448`
- subject: `statistics: implement refresh stats command (#63748)`
`git bisect` range:
- bad end: `38a7550aa9c42a64b83e60a18941b70af1ed145b`
- good base: `999befe4931f29919b9e6b50f419724685e0c832`
Behavior difference:
- on good commits, `EXPLAIN FORMAT='verbose'` chooses `idx_from_address(from_address)`
- starting from `72a540b80422`, it switches to `idx_block_time_order(block_time, order_no)`
- the estimate on the `block_time >= 1773388608` range becomes severely underestimated
Bad-side plan fragment:
```text
IndexRangeScan_24(Build) 1.12 281.22 cop[tikv] table:t_table, index:idx_block_time_order(block_time, order_no) range:[1773388608,+inf]
```
Good-side plan fragment:
```text
IndexRangeScan_21(Build) 3.89 908.47 cop[tikv] table:t_table, index:idx_from_address(from_address) range:["abc...xyz","abc...xyz"]
```
From the original `EXPLAIN ANALYZE` captured in the replayer, the bad-side `idx_block_time_order` range scan has estimated rows `1.12` but actual rows `17723`, which is the core symptom.
I can provide the plan replayer zip privately if needed.
### 2. What did you expect to see? (Required)
The optimizer should not severely underestimate the out-of-range predicate on `block_time`, and it should keep choosing the better access path instead of regressing to `idx_block_time_order` because of the underestimated cardinality.
### 3. What did you see instead (Required)
After `72a540b80422` (`Planner: Add min/max for out of range (#63077)`), the query switches from `idx_from_address` to `idx_block_time_order`, and the `block_time >= 1773388608` range is estimated as only `1.12` rows even though the actual row count is about `17723`.
This leads to a clearly wrong plan choice.
### 4. What is your TiDB version? (Required)
Bad version where the regression is visible:
```text
Release Version: v8.5.4-nextgen.202510.13
Edition: Enterprise
Git Commit Hash: 38a7550aa9c42a64b83e60a18941b70af1ed145b
```
Good comparison point:
```text
Release Version: v8.5.4
Edition: Community
Git Commit Hash: e4e814fdc0afe9c3a6e5e96f129d83df802ab820
```
Contributor guide
Assessment
This issue has not been assessed yet.