Tracking issue for analyze predicate/user-specified columns

[xuyifangreeneyes]

Proposal

related issue: #27358
design doc: #28878

Currently ANALYZ TABLE collects the statistics for all columns. However, only some columns' statistics are used in making query plans while others are not used. If some column occurs in filter conditions such as where/join conditions, its statistics will be used for calculating selectivity. We call such column predicate column. If we only collect the statistics for the predicate columns, the cost of ANALYZE TABLE can be reduced.

Hence we want to do the following things:

1. track which columns are predicate columns
2. support only analyze predicate columns
3. support only analyze user-specified columns.

Development Task

• Add syntax for analyzing predicate columns/user-specified columns #28845
• Add syntax for analyze all columns #29376
• Forbid invalid column name for analyze columns #29674
• Add system table for tracking predicate columns #28909
• Collect predicate columns from logical plan #29877
• Periodically dump information about predicate columns to disk #28916
• Add GC for mysql.column_stats_usage #29182
• Support show column_stats_usage #28978
• Implement ANALYZE COLUMNS c1, ..., cn #29025
• Implement analyze predicate columns #31102
• Make tidb_enable_column_tracking off by default #31205
• *: implement analyze predicate/user-specified columns #28787
• use analyze config to set all columns/predicate columns/user-specified columns for both manually-analyze and auto-analyze.
• Finer estimation of average row size when column stats are unavailable #28986

[xuyifangreeneyes]

/sig planner

[xuyifangreeneyes]

/assign

[feitian124]

Did some research and find in MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN.
together with predicate definition, predicate column is much clean now.

Is this an official terminology and added to docs?

[xuyifangreeneyes]

Did some research and find in MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN. together with predicate definition, predicate column is much clean now.

Is this an official terminology and added to docs?

The design doc in #28878 has description on predicate column. You can also take a look at redshift's doc/blog on predicate columns. Here are the links:

1. https://aws.amazon.com/blogs/big-data/collect-data-statistics-up-to-5x-faster-by-analyzing-only-predicate-columns-with-amazon-redshift/
2. https://docs.aws.amazon.com/redshift/latest/dg/t_Analyzing_tables.html