pingcap / pingcap/tidb

reduce the statistics collected for non-predicate columns during ANALYZE

Open
#69,668 0 comments 0 reactions 0 assignees View on GitHub
type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**

ANALYZE collects the same number of TopN values and histogram buckets for every column of a table (default 100 TopN / 256 buckets). For wide tables, most columns are never used in query predicates, yet their statistics cost the same to collect, store, load, and maintain as the columns that actually drive plan selection. This inflates analyze duration, stats storage, and stats cache memory for no estimation benefit.

**Describe the feature you'd like:**

Reduce the size of statistics collected for columns that are not predicate columns. A global variable `tidb_analyze_non_predicate_column_ratio` (default 0.1, range [0,1]) scales the TopN and bucket counts collected for a column that is not a predicate column, e.g. with the defaults a non-predicate column collects 10 TopN values and 25 buckets instead of 100 and 256.

Columns that keep the fully configured numbers:
- predicate columns recorded in `mysql.column_stats_usage`, when any exist for the table;
- otherwise (no usage recorded yet) the handle column and the first column of each index, as the most likely future predicate columns;
- columns explicitly specified in `ANALYZE TABLE ... COLUMNS`.

Index statistics are not reduced. Setting the variable to 1 disables the reduction.

**Describe alternatives you've considered:**

`tidb_analyze_column_options = 'PREDICATE'` skips non-predicate columns entirely, but leaves them with no statistics at all (falling back to pseudo estimates). A reduced-size collection keeps NDV/null counts and a coarse distribution for every column while still cutting the collection cost.

**Teachability, Documentation, Adoption, Migration Strategy:**

The behavior is controlled by a single documented global variable with a conservative escape hatch (`set global tidb_analyze_non_predicate_column_ratio = 1` restores the previous behavior). Auto-analyze inherits the setting automatically.

Contributor guide

Open the contributing guide

Research direction

Start by tracing ANALYZE TABLE handling, the global variable tidb_analyze_non_predicate_column_ratio, and predicate usage from mysql.column_stats_usage. Verify how TopN and histogram bucket counts are chosen for predicate, fallback, and explicitly selected columns. Done means non-predicate columns use the configured ratio, index statistics remain unchanged, and setting the ratio to 1 preserves the previous behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.