PostgreSQL Table Statistics: When, Where, and How They're Calculated

In the previous blog, we explored TableAmRoutine - what it is, and how it gives us the ability to hook a custom storage engine into PostgreSQL without changing any of its core functionality.

In today's blog, we'll explore PostgreSQL's table statistics - when, where, and how they get calculated - and finally, take a brief look at how these stats actually help our queries.

Part 1: What are Table Statistics & Why they are important?

Table statistics are estimates about the data that lives inside a table - things like the most common values in a column and their frequencies, the number of distinct values that exist in a column, correlation between columns, and so on.

When you run a query, it enters the PostgreSQL planner, which has to find an efficient and optimised way - in terms of memory usage and data access, to produce the required result. But on what basis does the planner decide whether a generated plan is actually optimised and efficient? That's exactly what statistics tell it.

If the stats are stale, they can easily mislead the planner into generating a bad plan - and a bad plan means bad query performance.

Part 2: Where Do These Statistics Live?

Usually, statistics are stored in two distinct layers: in-memory during ANALYZE, then persisted across three system catalogs.

Layer-1: In-Memory - VacAttrStats

Reference: vacuum.h

During the ANALYZE operation, each column's computed stats live in a VacAttrStats struct:

typedef struct VacAttrStats {
    // Set by ANALYZE before calling typanalyze:
    int         attstattarget;      // sample size target
    Oid         attrtypid;          // column's type OID
    Form_pg_type attrtype;          // pg_type row
    Oid         attrcollid;

    // Set by typanalyze (e.g. std_typanalyze):
    AnalyzeAttrComputeStatsFunc compute_stats;  // fn pointer
    int         minrows;
    void       *extra_data;   // e.g. StdAnalyzeData (eq/lt operators)

    // Filled by compute_stats:
    bool        stats_valid;
    float4      stanullfrac;
    int32       stawidth;
    float4      stadistinct;
    int16       stakind[5];         // slot type codes
    Oid         staop[5];           // operator OIDs per slot
    Oid         stacoll[5];
    float4     *stanumbers[5];      // numeric arrays (frequencies, correlation)
    Datum      *stavalues[5];       // value arrays (MCV values, histogram bounds)
} VacAttrStats;

This is a transient structure - it exists only during ANALYZE and is freed once the stats are flushed to disk.

Layer-2: On-Disk - Three System Catalogs

Once the stats are computed, those stats are flushed into the PostgreSQL catalog tables namely:

• pg_class

• pg_statistic

• pg_statistic_ext + pg_statistic_ext_data - Extended stats

pg_class

pg_class catalog table holds the table-level stats. Like:

pg_statistic

pg_statistic catalog table holds per-column stats. One row per (table, column, stainherit) triple.

Reference: update_attstats()

The kind codes defined in pg_statistic.h determine what each slot means:

pg_statistic_ext + pg_statistic_ext_data - Extended stats

Created by CREATE STATISTICS, populated by ANALYZE. split across two tables by design:

pg_statistic_ext (OID 3381) - the definition (created once, survives until DROP STATISTICS):

pg_statistic_ext_data(OID 3429) — the computed data (overwritten each ANALYZE):

Part 3: When Is ANALYZE Called

There are two distinct ways ANALYZE gets triggered: manually, by you running the command yourself, and automatically, via autovacuum's analyze sub-process.

Trigger 1: Manual ANALYZE

We can invoke it directly:

ANALYZE table_name;            -- single table
ANALYZE table_name(col1, col2); -- specific columns only
ANALYZE;                        -- entire database

This runs synchronously, in our session, immediately recomputing stats for the target relation(s).

Trigger 2: Autoanalyze - The Threshold Formula

PostgreSQL's autovacuum launcher doesn't just watch for dead tuples (which trigger VACUUM) - it separately tracks how many rows have been inserted, updated, or deleted since the last ANALYZE, and compares that against a computed threshold:

analyze threshold = autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor * reltuples)

Where:

• autovacuum_analyze_threshold : a flat minimum, default 50 rows

• autovacuum_analyze_scale_factor : a fraction of table size, default 0.1 (10%)

• reltuples : the table's estimated row count, read from pg_class

So for a table with 100,000 rows, using defaults: the analyze threshold equals the analyze base threshold plus the analyze scale factor multiplied by the number of tuples, giving 50 + (0.1 × 100,000) = 10,050. Once 10,050+ rows have changed since the last ANALYZE, the table becomes a candidate for autoanalyze.

Part 4: How Are They Actually Computed? - The ANALYZE Call Tree

Here's the full execution path, from the moment we run ANALYZE to the moment stats land in pg_statistic:

ANALYZE command => {
    analyze_rel() => {
        do_analyze_rel() => {
            // examine_attribute() is called once per column to analyze
            // Builds VacAttrStats per column
            examine_attribute() => {
                std_typanalyze(); // sets the compute_stats fn pointer
            }

            // acquire_sample_rows() is called once per column to sample rows
            // samples ~300 × target rows using reservoir sampling
            acquire_sample_rows() => {
                BlockSampler_Init(); // picks random blocks to read
                reservoir sampling; // keeps a uniform random subset
            }

            // compute_stats() is called once per column to compute statistics
            // computes scalar, distinct, and trivial statistics
            compute_stats() => {
                compute_scalar_stats(); // computes scalar statistics 
                compute_distinct_stats(); // computes distinct statistics
                compute_trivial_stats(); // computes trivial statistics
            }
            
            update_attstats() => {
                INSERT/UPDATE into pg_statistic
            }
        }
    }
}

Part 5: Why Any of This Matters - Stats and the Query Planner

Everything we've covered so far - pg_statistic, the ANALYZE call tree, the sampling - exists for one reason: so that when the planner sees a query, it can guess how many rows will come out of each step, before actually running it. Those row estimates are what decide whether you get a fast index scan or a slow sequential scan.

Let's see this directly, with a table simple enough to set up in seconds:

CREATE TABLE sample (id INT PRIMARY KEY, col1 TEXT);
INSERT INTO sample SELECT i, 'col' || i FROM generate_series(1, 1000000) i;
ANALYZE sample;

A million rows, one indexed integer column, one text column. Now:

EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 50042;

Output:

Index Scan using sample_pkey on sample  (cost=0.42..8.44 rows=1 width=13) (actual time=0.012..0.012 rows=1.00 loops=1)
  Index Cond: (id = 50042)
  Index Searches: 1
  Buffers: shared hit=4
Planning:
  Buffers: shared hit=17
Planning Time: 0.290 ms
Execution Time: 0.028 ms

Look at rows=1 in the planner's estimate, and then rows=1.00 in the actual execution result right next to it. The planner predicted exactly one row and it was right.

Why? Because id is the primary key, which means it's backed by a unique index. When ANALYZE ran, it stored n_distinct = 1,000,000 for this column (every value distinct), with no MCV list at all - no value is any more common than another. So when the planner sees id = 50042, it doesn't need a histogram or anything fuzzy: it knows there's exactly one matching value, period. That's why it confidently chose an Index Scan instead of scanning the whole table - cost=0.42..8.44 is tiny, because the estimate told it there's almost nothing to fetch.

This is the entire point of everything in Parts 1 through 5. The planner never touched all 1,000,000 rows to make this decision. It looked at the statistics - built from a sample, stored in pg_statistic, computed back when ANALYZE ran - and used them to predict the outcome before running anything.

Part 6: Putting It All Together

We started this post asking a simple question: when, where, and how does PostgreSQL calculate the statistics that its planner depends on?

We've now traced the entire lifecycle:

• What stats are and why a planner can't make good decisions without them

• Where they live: transiently in VacAttrStats during ANALYZE, and permanently across pg_class, pg_statistic, and the extended-stats catalogs

• When they get triggered : manually, or automatically once autovacuum's analyze threshold formula is crossed

• How they're actually computed : the full call tree from analyze_rel() down through sampling and per-column stat computation, to being written back via update_attstats()

• And finally, why it all matters - watching the planner use exactly these numbers, live, in a real EXPLAIN ANALYZE, to confidently pick an index scan over a sequential scan