Default Selectivity for Unestimable Predicates #

Three unrelated slow queries share a pattern. One estimates exactly 0.5% of its table, one exactly a third, one exactly 200 groups. Round numbers like these rarely come from data; they are the constants the planner uses when it has nothing else to go on. Learning to recognise them turns vague “bad estimate” investigations into specific fixes.

The normal path from statistics to estimates is covered in statistics and selectivity estimation. This page catalogues what happens when that path is unavailable.

The Planner Condition #

When a selectivity function cannot find usable statistics — no ANALYZE yet, an expression without expression statistics, an operator with no estimator, a parameter whose value is unknown — it returns a fixed default. The most important ones:

Situation Default Typical cause
equality, no stats 0.005 (0.5%) f(col) = const, unanalyzed table
inequality (<, >), no stats 0.3333 f(col) > const, generic plan range
range pair (>= a AND < b), no stats 0.005 expression ranges
pattern match (LIKE), no stats 0.005 LIKE on unanalyzed or expression data
boolean function in WHERE 0.3333 WHERE is_active(x)
distinct values, no stats 200 GROUP BY on expressions or unanalyzed tables
IS NULL, no stats 0.005 unanalyzed columns

These defaults were chosen to be moderately selective so that, without information, the planner still considers index paths. They are rarely right. A real equality predicate might match one row or half the table; a real GROUP BY might produce 3 groups or 30 million.

Defaults compound. Two defaulted equality filters multiply to 0.000025; a join estimated from default distinct counts on both sides can be off by orders of magnitude. And because they look like ordinary numbers in EXPLAIN, they are easy to accept as measurements.

Functions can escape the defaults. Since PostgreSQL 12, a function may have a planner support function that supplies selectivity or row estimates; many built-ins use this, and extensions can too.

Round numbers that are really defaults A table mapping each default to what it looks like in EXPLAIN. Equality without statistics shows rows equal to half a percent of the table. Inequality shows one third of the table. A boolean function shows one third. Missing distinct counts show 200 groups. Two defaulted equalities together show 0.0025 percent of the table. default looks like in EXPLAIN equality / LIKE 0.005 rows = 0.5% of table inequality 0.3333 rows = 1/3 of table boolean function 0.3333 rows = 1/3 of table distinct values 200 Group rows = 200 two equalities 0.000025 rows = 0.0025% of table divide rows by the table's reltuples to check

Annotated EXPLAIN Evidence #

EXPLAIN
SELECT o.region_code, count(*)
FROM orders o
WHERE o.flags->>'channel' = 'partner'
  AND is_priority(o.id)
GROUP BY o.region_code || '-' || o.tier;
HashAggregate  (cost=… rows=200 width=40)                      -- 200: default distinct count
  Group Key: (((region_code || '-'::text) || tier))
  ->  Seq Scan on orders o  (cost=0.00..412008.00 rows=16667 width=40)
        Filter: (((flags ->> 'channel'::text) = 'partner'::text) AND is_priority(id))
-- reltuples = 10,000,000
-- 10,000,000 × 0.005 (equality on expression) × 0.3333 (boolean function) = 16,667

Checking each piece:

SELECT reltuples FROM pg_class WHERE relname = 'orders';            -- 1e+07
EXPLAIN SELECT 1 FROM orders WHERE flags->>'channel' = 'partner';   -- rows=50000   (0.5%)
EXPLAIN SELECT 1 FROM orders WHERE is_priority(id);                 -- rows=3333333 (1/3)
SELECT count(*) FROM orders WHERE flags->>'channel' = 'partner';     -- 4,120,044   (41%)
Decomposing a defaulted estimate Three plan values are annotated. HashAggregate rows 200 is the default distinct count for an expression. The scan estimate of 16,667 equals ten million times 0.005 times one third. The isolated test of the JSON predicate shows 50 thousand, exactly half a percent, against 4.1 million actual rows. HashAggregate (rows=200) default n_distinct for an expression Seq Scan (rows=16667) 1e7 × 0.005 × 0.3333 flags->>'channel' = … rows=50000 0.5% default; actual 41% test each predicate alone to see which ones are defaults

Step-by-Step Resolution #

  1. Divide each suspicious estimate by reltuples. Match the ratio against the default table above.

  2. Isolate predicates with individual EXPLAIN statements to find which ones are defaulted.

  3. Analyze first. An unanalyzed table defaults everything:

    SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'orders';

  4. Add expression statistics for computed predicates and grouping expressions, as in expression statistics for computed predicates:

    CREATE STATISTICS orders_channel ON ((flags->>'channel')) FROM orders;
CREATE STATISTICS orders_region_tier ON ((region_code || '-' || tier)) FROM orders;
ANALYZE orders;

  5. Promote frequently filtered JSON keys to columns — generated columns keep them in sync — so they get ordinary statistics and indexes:

    ALTER TABLE orders ADD COLUMN channel text GENERATED ALWAYS AS (flags->>'channel') STORED;

  6. Replace opaque boolean functions with inline SQL expressions the planner can estimate, or give them a support function if they live in an extension you maintain.

Stacked defaults versus measured estimates With stacked defaults the scan is estimated at 16,667 rows. After expression statistics on the JSON key it is estimated at 1.37 million. After also replacing the boolean function with an inline expression it is estimated at 1.02 million. The actual count is 1.04 million. stacked defaults 16,667 + expression stats 1.37M + inline expression 1.02M actual 1.04M each removed default moved the estimate closer to reality

Before and After #

-- BEFORE: expression predicate, boolean function, expression grouping — all defaulted
HashAggregate (rows=200) → Seq Scan (rows=16667)   actual: 1,040,210 rows, 612 groups

-- AFTER: expression statistics + inline predicate
HashAggregate (rows=598) → Seq Scan (rows=1021004)  actual: 1,040,210 rows, 612 groups

Defaults in generic plans #

Prepared statements introduce defaults of their own. In a generic plan the planner knows a parameter’s type but not its value, so col > $1 cannot be located in the histogram and uses the one-third inequality default, while col = $1 uses the column’s average frequency rather than the 0.5% constant. A range on a timestamp parameter — created_at >= $1 — is therefore always estimated at a third of the table in a generic plan, whether the application asks for the last minute or the last decade. That is often the real reason a generic plan scans a table the custom plan would have indexed, and it is why EXPLAIN GENERIC_PLAN output so frequently shows estimates of exactly one third.

Building a habit of checking estimates #

Defaults are cheap to detect systematically. A useful routine for any new query that will run in production: capture EXPLAIN (ANALYZE) on realistic data, and for every scan and aggregate node compute the ratio of rows to actual rows. Nodes whose estimate is an exact default fraction of the table, or exactly 200 groups, go on a short list, along with the predicate or grouping expression responsible. Most items on that list have one of three causes — an unanalyzed table, an expression without statistics, or an opaque function — and each has a direct fix described above.

It also pays to look at queries that are fast today. A default estimate that happens to produce a good plan on current data is a latent regression: as the table grows or the distribution shifts, the real selectivity moves while the estimate stays frozen at its constant, and the plan that was right by coincidence becomes wrong without any change to the query or the schema. Replacing defaults with measured statistics does not just fix slow queries; it makes the planner’s decisions track the data instead of a constant chosen decades ago for an unknown workload.

Finally, remember that estimates are only as current as the last ANALYZE. Expression statistics and extended statistics objects are refreshed by the same autovacuum analyze runs as ordinary column statistics, so once they exist, keeping them fresh needs no extra work on ordinary tables — only on partitioned parents and temporary tables, which autovacuum never analyzes.

Common Pitfalls #

Accepting round estimates as data. 0.5%, a third and 200 are suspicious. Diagnostic signal: estimates that are exact fractions of reltuples. Fix: test predicates individually.

Fixing the scan, forgetting the grouping. Grouping expressions default their distinct count separately. Diagnostic signal: rows=200 on an aggregate above a well-estimated scan. Fix: statistics on the grouping expression.

JSON keys as filters. ->> expressions have no statistics by default. Diagnostic signal: 0.5% estimates for every JSON filter. Fix: expression statistics or generated columns.

Blaming the planner for a generic plan’s one-third. The value is unknown by design. Diagnostic signal: range estimates of exactly a third only in prepared statements. Fix: custom plans for that statement, as in prepared statement plan pinning.

Frequently Asked Questions #

What is PostgreSQL’s default selectivity for equality? #

0.005, meaning 0.5% of rows, when no statistics are available for the compared expression. Inequality comparisons default to one third of rows.

Why does the planner estimate 200 groups? #

200 is the default number of distinct values used when there are no statistics for the grouping column or expression — common for expressions, JSON keys, unanalyzed tables and partitioned parents.

How can a function avoid default selectivity estimates? #

By being replaced with an expression the planner can analyse, by having expression statistics gathered for it, or — for functions you control in C — by providing a planner support function that returns a selectivity or row estimate.

Up: Statistics and Selectivity Estimation