planner: Optimizer uses total table size for partition table scan cost estimation even when query can be pruned
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
I encountered an issue regarding the cost estimation logic for partitioned tables. When querying a partitioned table with a specific partition key, the estCost for the IndexFullScan operator is calculated based on the global table statistics (Total Rows) rather than the statistics of the pruned partition(s), resulting in an extremely inflated cost.
The examples below are simulations to illustrate the concept (not real SQL or plan output).
Schema Context:
Table: DB.TBL_PART_DATA (Range Partition by P_DATE, 1 partition per day).
Data Size:
Total Rows: ~240 Million.
Rows per Partition (e.g., 20250301): ~1.5 Million.
SQL (example):
```
-- Example SQL (simplified for illustration)
EXPLAIN FORMAT='verbose'
SELECT user_id
FROM DB.TBL_PART_DATA
WHERE P_DATE = '20250301';
```
Plan:
```
+----------------------+------------+--------------+----------+-------------------------+----------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------+------------+--------------+----------+-------------------------+----------------------+
| IndexReader | 1500000 | 2800000000 | root | partition:P20250301 | index:Projection |
| └─Projection | 1500000 | 42000000000 | cop[tikv]| | tbl_part_data.user_id|
| └─Selection | 1500000 | 42000000000 | cop[tikv]| | eq(P_DATE, 20250301) |
| └─IndexFullScan | 240000000 | 35000000000 | cop[tikv]| index:IDX_USER_ID | keep order:false |
+----------------------+------------+--------------+----------+-------------------------+----------------------+
```
**Analysis & Root Cause**
As shown in the output:
1. Partition Pruning works: The access object correctly shows partition:P20250301.
2. Selectivity works: Selection estimates ~1.5M rows, which matches the partition size.
3. The Issue: IndexFullScan still uses the global row count (~240M), which inflates its estCost.
This is likely due to partition-aware statistics not being incorporated into the base scan cost, possibly related to dynamic partition pruning. Even when the predicate is a constant value and the pruned partition is known at planning time, the optimizer still bases scan cost on the entire logical table.
**Impact**
The inflated scan cost negatively affects plan selection:
1. Bias against Scan Operators: Even when a partition scan is efficient, the optimizer perceives it as extremely expensive and favors less optimal operators.
2. Join Strategy Mis-selection: Queries that could benefit from a hash join on a small partition (1.5M rows) may instead get an index join because the optimizer assumes a huge scan input (240M rows).
**Expected Behavior**
The estCost for partition-pruned scans should reflect the actual partition statistics, not the global table size. This would allow consistent and accurate comparison between access paths.
Contributor guide
Assessment
This issue has not been assessed yet.