[YSQL] Auto analyze triggers only on mutations, missing other events that invalidate statistics

Jira Link: DB-23655

Description

Auto analyze decides whether to run ANALYZE from one signal, a table's accumulated mutation count. Several operations change what the statistics describe without producing mutations, so nothing asks for an analyze afterwards and the stale or missing statistics persist for as long as the table is otherwise idle.

Two instances are confirmed below. The general point is that the trigger set should cover the events that invalidate statistics, not only row level writes.

How the trigger works today

DetermineTablesForAnalyze compares the accumulated mutation count against a threshold derived from the table size:

double analyze_threshold = threshold + scale_factor * it->second;
...
if (table_info.mutations >= analyze_threshold && since_last_analyze >= cooldown) {

and the mutations come from row level writes, counted one per non read only write operation on a non index relation, in pg_client_session.cc:

if (!op.op->read_only() && !op.op->table()->IsIndex() && pg_node_level_mutation_counter) {
  ...
  pg_node_level_mutation_counter->Increase(table_id, 1);

Instance 1, creating an index on an expression

An expression index is the only index kind with statistics of its own. The index build calls index_update_stats in catalog/index.c, which writes relhasindex, reltuples, relpages and relallvisible to pg_class and nothing else. The statistics describing the values the expression produces are pg_statistic rows keyed by the index's own relid, created only by ANALYZE, in compute_index_stats guarded by:

if (indexInfo->ii_Expressions != NIL && va_cols == NIL)

Until that runs, examine_variable finds nothing for the expression and the planner falls back to DEFAULT_EQ_SEL, half a per cent per element. On a table that is not being written to, the mutation count never crosses the threshold again, so this never resolves. A user who adds an expression index to improve a plan can be left with the plan the index was meant to fix.

From the case in #34021: (greatest(r2, r3, r4) - least(r2, r3, r4)) IN (1, 2, 3, 4) selects about 42 per cent of the table, and without statistics on the expression the planner estimates about 2 per cent. That is the difference between choosing the expression index and not.

A plain index needs nothing here, since the planner uses the table's own column statistics, and a partial index gets its row count from the build. Expression indexes are the specific case.

Instance 2, TRUNCATE

TRUNCATE does not go through the write operation path quoted above. It has its own statement path in pggate, NewTruncateTable and ExecTruncateTable, so it produces no mutations. A truncated table therefore keeps statistics describing its former contents, with nothing to ask for a refresh.

Open question, rewriting forms of ALTER TABLE

Forms of ALTER TABLE that rewrite a table may deserve the same treatment. I have not traced how those are counted, so this is a question rather than a claim.

Upstream comparison

Upstream PostgreSQL has both gaps. Its autoanalyze trigger is also mutation only, anltuples = tabentry->changes_since_analyze against anl_base_thresh + anl_scale_factor * reltuples in autovacuum.c. Nothing in index.c or indexcmds.c touches analyze statistics, so index creation triggers nothing there either. For TRUNCATE, pgstat_relation.c resets n_live_tuples, n_dead_tuples and inserts_since_vacuum but leaves changes_since_analyze alone.

So this is not a gap relative to upstream, and would be an improvement on it rather than a catch up. That is worth weighing when deciding how much it is worth.

Relationship to #34050

That issue covers a manual ANALYZE failing to suppress a redundant automatic one. Ordering matters between the two. The redundant run it removes is currently the only thing that gives a newly created expression index its statistics on an otherwise idle table, so it should land with or after this one.

Notes on the shape of a fix

• PostgreSQL has no syntax to analyze a single index, so the remedy for instance 1 is a table level ANALYZE. On a large table that is a full sampling pass, though the user has just paid for a full scan to build the index.
• Statistics gathered by ANALYZE are only reproducible while a table fits in its sample, which is #34028. Triggering more analyzes makes that limit more visible.

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.