pingcap / pingcap/tidb

palnner: Optimizer overestimates row count for empty future partitions

Open
#64,638 1 comment 0 reactions 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
I have a table partitioned by a generic time column (e.g., DATA_DATE), with partitions pre-created for one year in advance (one partition per day).

Global statistics are collected on this table. The statistics (Histogram and TopN) accurately reflect the existing data up to a certain date. However, when querying for a date range that is outside the current statistics' upper bound but falls into the pre-created empty partitions, the optimizer incorrectly estimates a significant number of rows.

It appears that instead of returning 0 (or a minimal value) for these empty partitions, the optimizer falls back to `estimateRowCountWithUniformDistribution`, assuming the data density in these empty partitions is similar to the historical data. This leads to inaccurate execution plans.

Since the relevant data is not publicly available, I won't post it here. You can contact me to obtain it, or you can construct it yourself; it should be relatively easy to create such an example.

### Diagnostic Information provided
1. Histogram Data (Global Stats) The histogram covers data up to 2025-11-15 (Upper Bound).
```
| database_name | table_name | global | DATA_DATE | 0 | 49 | 59347896 | 1144917 | 2025-10-23 00:00:00 | 2025-10-23 00:00:00 | 0 |
| database_name | table_name | global | DATA_DATE | 0 | 50 | 60545749 | 1197853 | 2025-10-24 00:00:00 | 2025-10-24 00:00:00 | 0 |
| database_name | table_name | global | DATA_DATE | 0 | 51 | 60845749 | 300000 | 2025-11-08 00:00:00 | 2025-11-08 00:00:00 | 0 |
| database_name | table_name | global | DATA_DATE | 0 | 52 | 61125749 | 280000 | 2025-11-09 00:00:00 | 2025-11-09 00:00:00 | 0 |
| database_name | table_name | global | DATA_DATE | 0 | 53 | 62325979 | 101 | 2025-11-09 00:00:00 | 2025-11-15 00:00:00 | 0 |
+---------+-------------------+----------------+-------------+----------+-----------+----------+---------+---------------------+---------------------+------+
```

2. TopN Data (Global Stats) The TopN values extend slightly further, up to 2025-11-18.
```
| database_name | table_name | global | DATA_DATE | 0 | 2025-11-12 00:00:00 | 1200130 |
| database_name | table_name | global | DATA_DATE | 0 | 2025-11-13 00:00:00 | 1200130 |
| database_name | table_name | global | DATA_DATE | 0 | 2025-11-16 00:00:00 | 1200231 |
| database_name | table_name | global | DATA_DATE | 0 | 2025-11-17 00:00:00 | 1200236 |
| database_name | table_name | global | DATA_DATE | 0 | 2025-11-18 00:00:00 | 1200130 |
+---------+-------------------+----------------+-------------+----------+---------------------+---------+
```

3. stats_meta. Now we haven't insert the data after the partition `P20251119`(The time `2025-11-21 10:53:42` indicates the update time. The partition name `P20251119` indicates the real date).
```
| database_name | table_name | P20251103 | 2025-11-21 10:53:42 | 0 | 1200230 | 2025-11-21 10:53:42 |
| database_name | table_name | P20251104 | 2025-11-21 10:53:42 | 0 | 1200230 | 2025-11-21 10:53:42 |
| database_name | table_name | P20251105 | 2025-11-21 10:53:42 | 0 | 1200231 | 2025-11-21 10:53:42 |
| database_name | table_name | P20251106 | 2025-11-21 10:53:42 | 0 | 1200230 | 2025-11-21 10:53:42 |
| database_name | table_name | P20251107 | 2025-11-21 10:53:42 | 0 | 1200230 | 2025-11-21 10:53:42 |
| database_name | table_name | P20251108 | 2025-11-21 10:53:42 | 0 | 300000 | 2025-11-21 10:53:42 |
| database_name | table_name | P20251109 | 2025-11-21 10:53:43 | 0 | 280000 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251110 | 2025-11-21 10:53:43 | 0 | 1200130 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251111 | 2025-11-21 10:53:43 | 0 | 1200130 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251112 | 2025-11-21 10:53:43 | 0 | 1200130 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251113 | 2025-11-21 10:53:43 | 0 | 1200130 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251114 | 2025-11-21 10:53:43 | 0 | 1200129 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251115 | 2025-11-21 10:53:43 | 0 | 101 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251116 | 2025-11-21 10:53:43 | 0 | 1200231 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251117 | 2025-11-21 10:53:43 | 0 | 1200236 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251118 | 2025-11-21 10:53:43 | 0 | 1200130 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251119 | 2025-11-21 10:53:43 | 0 | 0 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251120 | 2025-11-21 10:53:43 | 0 | 0 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251121 | 2025-11-21 10:53:43 | 0 | 0 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251122 | 2025-11-21 10:53:43 | 0 | 0 | 2025-11-21 10:53:43 |
| database_name | table_name | P20251123 | 2025-11-21 10:53:43 | 0 | 0 | 2025-11-21 10:53:43 |
```

4. The Issue When a query involves a predicate for dates after 2025-11-19 (e.g., DATA_DATE = '2025-11-20', the partitions for these dates exist but are empty.

Expected behavior: The optimizer should estimate close to 0 rows. Actual behavior: The optimizer uses average density to estimate a large row count.

### Root Cause Analysis
It seems that when the value falls out of the Histogram/TopN range, the logic triggers `estimateRowCountWithUniformDistribution`. For pre-created empty partitions, this logic is flawed because it assumes the "future" (empty) partitions share the same data distribution as the "past" (filled) partitions.

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.