Expression Statistics for Computed Predicates #

WHERE date_trunc('day', created_at) = '2026-09-16' is estimated at 50,000 rows on a 10-million-row table and returns 312,000. WHERE lower(email) = '[email protected]' is estimated at the same 50,000 and returns one. Both estimates are 0.5% of the table — a constant, not a measurement — because the planner has statistics for created_at and email, but not for anything computed from them.

How estimates are built from column statistics is described in statistics and selectivity estimation. This page covers giving the planner statistics for expressions.

The Planner Condition #

ANALYZE collects statistics for columns. When a predicate compares an expression — a function call, a cast, arithmetic, COALESCE — the planner looks for statistics describing that exact expression. There are two places it can find them:

Without either, the planner falls back to fixed default selectivities — 0.5% for equality, one third for inequality — described in default selectivity for unestimable predicates. Those defaults are wrong for almost every real expression: lower(email) is nearly unique, date_trunc('day', …) has a few hundred distinct values, and a boolean expression may be true for 99% of rows.

The expression must match exactly as the planner normalizes it: same function, same arguments, same constants, same collation. date_trunc('day', created_at) statistics do not help created_at::date.

Does this expression have statistics? For a predicate on an expression, the planner checks for statistics. If an expression index exists on exactly that expression and the table has been analyzed, it uses the index's expression statistics. If an extended statistics object on the expression exists, it uses those. Otherwise it applies fixed default selectivities of 0.5 percent for equality and one third for inequality. predicate on an expression — statistics available? expression index + ANALYZE index expression stats real MCV and histogram CREATE STATISTICS ON (expr) extended expression stats PostgreSQL 14+ neither default selectivity 0.005 equality, 0.333 range the expression in the query must match the indexed or declared expression exactly

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE)
SELECT count(*) FROM signups WHERE date_trunc('day', created_at) = '2026-09-16';
Aggregate  (actual time=1804.2..1804.2 rows=1 loops=1)
  ->  Seq Scan on signups  (cost=0.00..244120.00 rows=50000 width=0)
                           (actual time=812.4..1780.6 rows=312044 loops=1)
        Filter: (date_trunc('day'::text, created_at) = '2026-09-16 00:00:00+00'::timestamp with time zone)
        Rows Removed by Filter: 9687956
-- rows=50000 = 10,000,000 × 0.005: the default equality selectivity

After adding expression statistics:

CREATE STATISTICS signups_created_day ON (date_trunc('day', created_at)) FROM signups;
ANALYZE signups;
  ->  Seq Scan on signups  (cost=0.00..244120.00 rows=309870 width=0)
                           (actual time=810.2..1776.1 rows=312044 loops=1)
-- rows=309870: estimate now comes from the expression's MCV list

Inspect what was collected:

SELECT expr, n_distinct, most_common_vals[1:3], most_common_freqs[1:3]
FROM pg_stats_ext_exprs WHERE statistics_name = 'signups_created_day';
                expr                 | n_distinct |          most_common_vals           | most_common_freqs
-------------------------------------+------------+-------------------------------------+-------------------
 date_trunc('day'::text, created_at) |        412 | {"2026-09-16 00:00:00+00", …}        | {0.0312, …}
Recognising a default estimate Three items are annotated. A row estimate exactly 0.5 percent of the table suggests the default equality selectivity. The Filter shows a function applied to the column, which has no column statistics. The pg_stats_ext_exprs view shows real distinct counts and frequencies once expression statistics exist. rows=50000 on a 10,000,000-row table exactly 0.5%: the default Filter: (date_trunc('day', created_at) = …) expression, not a column pg_stats_ext_exprs n_distinct=412 real statistics after ANALYZE suspicious round fractions of the table are the first clue

Step-by-Step Resolution #

  1. Spot default estimates. Divide rows= by the table’s row count. Exactly 0.005, 0.333 or 0.5 strongly suggests a default.

  2. Decide between an index and a statistics object. If queries need to find rows by the expression, an expression index gives both an access path and statistics. If the expression only needs accurate estimates — for join sizing, or because the filter is not selective — CREATE STATISTICS avoids index maintenance cost.

    -- access path + statistics
CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));
-- statistics only (PostgreSQL 14+)
CREATE STATISTICS signups_created_day ON (date_trunc('day', created_at)) FROM signups;

  3. Analyze the table. Neither source has statistics until ANALYZE runs.

  4. Verify the statistics exist. Expression index statistics appear in pg_stats with tablename set to the index name; extended expression statistics in pg_stats_ext_exprs:

    SELECT tablename, attname, n_distinct FROM pg_stats WHERE tablename = 'users_lower_email_idx';

  5. Match the query text to the expression. Normalise application code so every query uses the same form — for example always date_trunc('day', created_at) rather than a mix with created_at::date.

  6. Combine with correlated columns when needed: CREATE STATISTICS … (mcv) ON (date_trunc('day', created_at)), country FROM signups records how the expression co-varies with another column, following extended statistics for correlated columns.

Default estimates versus measured ones For date_trunc day equals a busy day, the default estimate is 50 thousand rows, the expression statistics estimate is 310 thousand, and the actual count is 312 thousand. For lower of email equals one address, the default is 50 thousand, the statistics estimate is 1, and the actual count is 1. date_trunc — default 50,000 date_trunc — with stats 309,870 date_trunc — actual 312,044 lower(email) — default 50,000 (actual: 1) the same 0.5% default is wrong in opposite directions

Before and After #

-- BEFORE: no expression statistics
Seq Scan on signups (rows=50000) (actual rows=312044)       -- joins above planned for 50k rows

-- AFTER: CREATE STATISTICS ON (date_trunc('day', created_at)); ANALYZE
Seq Scan on signups (rows=309870) (actual rows=312044)      -- joins above planned correctly

Why an accurate estimate matters when the plan does not change #

In the example the scan itself stayed a sequential scan: at 3% selectivity there was no better access path. The payoff comes above it. A report that joins these signups to users and sessions was planned for 50,000 rows and chose nested loops with index probes; with 312,000 rows it now chooses hash joins and runs several times faster. Estimate errors at the leaves propagate upward, as described in spotting row estimate explosions, so fixing a leaf estimate is worthwhile even when the leaf’s own node is unchanged.

The opposite case is the lower(email) lookup. There the default estimate is too high by a factor of 50,000, which can make the planner reject an index scan in favour of a sequential or bitmap scan and choose hash joins where a single nested-loop probe would do. Because that query also needs an access path, an expression index is the right fix, and its statistics come for free.

Keeping expression statistics cheap #

Each expression added to a statistics object is sampled on every ANALYZE of the table, so objects should contain only expressions that actually appear in predicates or grouping clauses. pg_stat_statements is a good source for that list: search normalized query texts for function calls applied to columns in WHERE and GROUP BY. Expressions that appear only in the SELECT list do not need statistics at all, because the planner never estimates selectivity for them.

Common Pitfalls #

Creating the object and not analyzing. The planner keeps using defaults. Diagnostic signal: no row in pg_stats_ext_exprs. Fix: ANALYZE table.

Expression mismatch. created_at::date does not match date_trunc('day', created_at). Diagnostic signal: default estimate persists despite statistics. Fix: use one canonical expression everywhere.

Time zone dependent expressions. date_trunc('day', timestamptz) depends on the session TimeZone, so statistics gathered under one zone describe different day boundaries under another. Diagnostic signal: estimates that shift between application and batch sessions. Fix: use the three-argument date_trunc('day', created_at, 'UTC') (PostgreSQL 12+) in both the query and the statistics definition.

Adding an index only for statistics. Index maintenance costs every write. Diagnostic signal: an expression index with idx_scan = 0. Fix: replace it with CREATE STATISTICS on the expression.

Frequently Asked Questions #

Why is my estimate exactly 0.5% of the table? #

That is PostgreSQL’s default selectivity for an equality comparison it has no statistics for. It usually means the predicate compares an expression — a function or cast applied to a column — rather than the bare column.

Do expression indexes provide statistics even when not used? #

Yes. ANALYZE samples the indexed expression and stores statistics for it, and the planner uses those statistics to estimate any predicate matching the expression, regardless of whether it chooses the index for the scan.

What is the difference between CREATE STATISTICS on an expression and an expression index? #

Both give the planner statistics for the expression. An expression index also provides an access path and costs maintenance on every write; a statistics object provides estimates only, with no write overhead beyond ANALYZE.

Up: Statistics and Selectivity Estimation