---
title: "PostgreSQL Table Statistics: When, Where, and How They're Calculated"
description: "Understand and Learn about the PostgreSQL Table's Statistics and in-depth code walkthrough"
author: "Samba Siva"
published: 2026-06-22T16:41:25.069Z
updated: 2026-11-03T12:09:05.978Z
tags:
  - "PostgreSQL"
  - "Technical"
  - "PGSourceCode"
url: "https://sambasivareddy.in/blog/postgresql-table-statistics-when-where-and-how-theyre-calculated"
---

# 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:

```c
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:

| Column | What it stores |
| --- | --- |
| reltuples | Estimated number of live rows |
| relpages | Number of disk pages (blocks) |
| relallvisible | Number of all-visible pages (used by index-only scans) |

**pg\_statistic**

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

> Reference: `update_attstats()`

| Column | What it stores |
| --- | --- |
| starelid | Relid of the table |
| staattnum | Column position in the table |
| stainherit | Is this column inherited by child tables? |
| stanullfrac | Fraction of null values in this column |
| stawidth | Width/Size of the data it stores |
| stadistinct | No. of distinct data values it has |
| stakind`1..5` | Integer kind code (what type of stat is in this slot) |
| staop`1..5` | Operator OID used (e.g. `=` for MCV, `<` for histogram) |
| stacoll`1..5` | Collation OID |
| stanumbers`1..5` | `float4[]` - numeric data (frequencies, correlation coefficient) |
| stavalues`1..5` | `anyarray` — actual data values (MCV values, histogram bounds) |

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

| stakind | Name | stanumbers hold | stavalues hold |
| --- | --- | --- | --- |
| 1 | `STATISTIC_KIND_MCV` | frequenices (most->least) | the K most common values |
| 2 | `STATISTIC_KIND_HISTOGRAM` | NULL | M equi-depth boundary values (first=MIN, last=MAX) |
| 3 | `STATISTIC_KIND_CORRELATION` | Pearson r coefficient | NULL |
| 4 | `STATISTIC_KIND_MCELEM` | element frequencies + min/max | most common array / tsvector elements |
| 5 | `STATISTIC_KIND_DECHIST` | distinct-element-count histogram + avg | NULL |
| 6 | `STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM` | fraction of empty ranges | histogram of range lengths |
| 7 | `STATISTIC_KIND_BOUNDS_HISTOGRAM` | NULL | interleaved lower/upper bound histogram |

`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`):

| Column | Meaning |
| --- | --- |
| stxrelid | Table OID |
| stxname/stxnamespace | Name and Schema |
| stxkeys | `int2vector` of columns attnums covered |
| stxkind | What stat kinds to compute: `d` = distinct, `f` = dependencies, `m` = MCV, `e` = expressions |
| stxexprs | Expression tress for expression statistics |

`pg_statistic_ext_data`(`OID 3429`) — the _computed data_ (overwritten each ANALYZE):

| Column | Meaning |
| --- | --- |
| stxoid | FK → `pg_statistic_ext` |
| stxdinherit | Includes child tables? |
| stxdndistinct | Serialized multi-column n-distinct coefficients |
| stxddependencies | Serialized functional dependency degrees |
| stxdmcv | Serialized multi-column MCV list |
| stxdexpr | Per-expression stats (same `pg_statistic` row format) |

* * *

## 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:

```sql
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:

```markdown
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`:

```cpp
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