Raising the Statistics Target for Skewed Columns #

A single-column predicate is misestimated on its own: WHERE tenant_id = 8812 is planned for 90 rows and returns 212,000. The value is one of your largest tenants, but the statistics describe it as an ordinary one. The column is skewed, and the default sample is too small to notice.

This is the narrowest of the estimate repairs described in statistics and selectivity estimation: give one column a larger statistics target so ANALYZE keeps a longer most-common-values list and a finer histogram.

Why the Default Sample Misses Skew #

default_statistics_target is 100. It controls three things for each column:

On a 400-million-row table with 60,000 tenants, a tenant owning 212,000 rows is 0.05% of the table. In a 30,000-row sample it appears around 16 times. Whether it makes the MCV list depends on how that compares with the other tenants’ sample counts, and with a hundred slots contested by the largest tenants, it can easily fall off. Once it is off the list, its estimate is the leftover frequency divided evenly among the remaining distinct values — the same small number as a tenant with 40 rows.

Ranges suffer the same way. With 100 histogram buckets, each bucket covers 1% of the non-MCV rows. A created_at range covering the last hour on a table spanning three years falls entirely inside one bucket, and the estimate is interpolated linearly within it — accurate only if rows are spread evenly inside that bucket, which recent-heavy timestamp data rarely is.

Where the MCV list stops A descending bar chart of value frequencies. A cutoff at target 100 includes only the tallest bars; the hot value sits just beyond it and is estimated as an average value. A cutoff at target 1000 extends far enough right to include it with its real frequency. target 100 cutoff target 1000 cutoff tenant 8812 — just past the default list values sorted by frequency; everything right of the cutoff is estimated as average

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE tenant_id = 8812 AND issued_on >= '2026-09-01';
Index Scan using invoices_tenant_issued_idx on invoices
    (cost=0.57..352.10 rows=91 width=212) (actual time=0.06..1840.2 rows=212044 loops=1)
  Index Cond: ((tenant_id = 8812) AND (issued_on >= '2026-09-01'::date))
  Buffers: shared hit=4120 read=161902        -- random reads for 212k rows
-- rows=91 → the index scan looked like 91 heap fetches
-- actual 212044 → a bitmap heap scan or a parallel seq scan would have read far less randomly

Confirm the value is missing from the list:

SELECT array_position(most_common_vals::text::int[], 8812) AS pos,
       array_length(most_common_vals::text::int[], 1)      AS mcv_len,
       n_distinct
FROM pg_stats WHERE tablename = 'invoices' AND attname = 'tenant_id';
 pos | mcv_len | n_distinct
-----+---------+------------
     |     100 |      41220
-- pos NULL → 8812 is not in the list; list is full at 100 entries
-- n_distinct 41220 (real: 60,000) → the non-MCV estimate is also low
Plan and pg_stats evidence side by side Three lines are annotated. The index scan estimate of 91 rows against 212 thousand actual. The read buffer count shows random heap fetches. The pg_stats check returns no MCV position for the value and a full list of 100 entries. rows=91 … actual rows=212044 hot value estimated as average Buffers: read=161902 random heap reads for each row pos NULL · mcv_len 100 list full, value not in it a full list without the value means the target is too small

Step-by-Step Resolution #

  1. Isolate the predicate. Estimate the column alone so a correlated second predicate is not confusing the picture:

    EXPLAIN SELECT 1 FROM invoices WHERE tenant_id = 8812;
SELECT count(*) FROM invoices WHERE tenant_id = 8812;

  2. Raise the target on that column only.

    ALTER TABLE invoices ALTER COLUMN tenant_id SET STATISTICS 1000;

    The setting is stored in pg_attribute.attstattarget and survives restarts and dumps. It overrides default_statistics_target for this column only.

  3. Analyze just that column so you do not pay for a full-table analyze while iterating:

    ANALYZE invoices (tenant_id);

  4. Verify the list now contains the value with a sensible frequency.

    SELECT v, f
FROM pg_stats,
     unnest(most_common_vals::text::int[], most_common_freqs) AS u(v, f)
WHERE tablename = 'invoices' AND attname = 'tenant_id' AND v = 8812;
-- expect f ≈ 212044 / reltuples

  5. Measure planning cost. Run the query several times and compare Planning Time with the previous target. A few tenths of a millisecond is normal; several milliseconds on a statement that executes thousands of times a second is not.

    EXPLAIN (ANALYZE, SUMMARY) SELECT * FROM invoices WHERE tenant_id = 8812 LIMIT 1;

  6. Re-check the full query. The plan should change from an index scan planned for dozens of rows to a shape suited to hundreds of thousands.

What each step up in target buys and costs Three columns for statistics targets 100, 1000 and 10000. Sample rows grow from 30 thousand to 300 thousand to 3 million. MCV slots grow from 100 to 1000 to 10000. Planning cost grows from negligible to small to noticeable. The middle setting is marked as the usual sweet spot for a skewed column. sample rows MCV slots planning cost 30,000 100 negligible 300,000 1,000 small 3,000,000 10,000 noticeable target 100 target 1000 — usual fix target 10000

Before and After #

-- BEFORE: attstattarget default (100)
Index Scan using invoices_tenant_issued_idx  (rows=91) (actual rows=212044)   Execution Time: 1911.6 ms
  Buffers: shared hit=4120 read=161902

-- AFTER: SET STATISTICS 1000; ANALYZE invoices (tenant_id)
Bitmap Heap Scan on invoices  (rows=208770) (actual rows=212044)              Execution Time: 402.3 ms
  Buffers: shared hit=2210 read=24518

The estimate moved from 91 to 208,770 and the access path moved with it; read buffers dropped by about 85% because the bitmap visits heap pages in physical order.

Common Pitfalls #

Changing the global default instead. default_statistics_target = 1000 in postgresql.conf fixes this column and makes every analyze on the server ten times larger. Diagnostic signal: autovacuum analyze durations climb across unrelated tables. Fix: revert the global value and set per-column targets on the columns you measured.

Forgetting that the new target needs an analyze. SET STATISTICS changes nothing until statistics are rebuilt. Diagnostic signal: attstattarget shows 1000 but most_common_vals still has 100 entries. Fix: ANALYZE table (column) immediately.

Expecting a higher target to fix a correlated pair. If the column is estimated well alone and badly only in combination, more samples of the same column change nothing. Diagnostic signal: the single-predicate EXPLAIN is already accurate. Fix: use extended statistics for correlated columns.

Losing the setting in a table rewrite tool. Some online schema-change tools rebuild the table from a CREATE TABLE … LIKE that does not carry per-column targets. Diagnostic signal: the regression returns after a migration that touched the table. Fix: keep the SET STATISTICS statement in your migrations and re-apply it after rewrites.

Frequently Asked Questions #

What value should I set the statistics target to? Start at 500 or 1,000 for the column, analyze, and check whether the values you care about now appear in most_common_vals with plausible frequencies. Increase only if they still do not; the maximum is 10,000 but rarely pays for its cost.

Does a higher statistics target slow down queries? It can slow planning, not execution. Larger MCV lists and histograms are read and searched while estimating selectivity, so statements planned on every execution pay each time. Compare Planning Time before and after.

Why is my hot value still not in the MCV list? A value is kept only when it appears in the sample clearly more often than an average value. On a column with a very long tail, a moderately hot value may still not qualify, and its estimate then depends on n_distinct — see overriding n_distinct on large tables.

Up: Statistics and Selectivity Estimation