pingcap / pingcap/tidb

planner: regression after #63077 causes severe underestimation for out-of-range block_time and wrong index choice

Open
#67,093 0 comments 0 reactions 1 assignee Claimed by @terry1purcell View on GitHub
contribution
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

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.