When the Planner Chooses a Materialized View Scan #

The question in this page’s title contains a trap. PostgreSQL’s planner never chooses a materialized view the way it chooses between a hash join and a nested loop — it does not shop your query against available matviews at all. There is no automatic query rewrite to matviews. The planner only “chooses” a matview scan in the trivial sense that you named the matview and it now has to scan it. What is genuinely a planner decision is what happens next: given a query against the matview, does it use a Seq Scan or an Index Scan? That choice comes entirely from the matview’s own statistics, which is why ANALYZE after every refresh is non-negotiable.

The Planner Does Not Substitute Matviews #

Say it plainly: a query that reads the base tables scans the base tables. Even if mv_daily_revenue holds precisely the aggregate below, this statement never touches it.

-- Base-table aggregate: planner scans orders + line_items, ignores every matview
SELECT sale_date, region, sum(amount) AS revenue
FROM   orders o JOIN line_items li ON li.order_id = o.order_id
GROUP  BY sale_date, region;

To get the precomputed answer you must name the matview. Only then does it enter the plan — and at that point PostgreSQL forgets it is a “view” entirely and treats it as the physical relation it is:

-- Names the matview: now it is in the plan, scanned like a table
SELECT sale_date, region, revenue
FROM   mv_daily_revenue
WHERE  region = 'EU';

This is the core of the materialized view strategy: adoption is an explicit routing decision, never a transparent optimization. If you want something closer to automatic redirection, you build it yourself with query-rewrite rules for matviews — but the database will not do it for you.

Seq Scan vs Index Scan on the Matview’s Own Stats #

Once the matview is named, the scan decision is ordinary planner arithmetic driven by cost estimation models, applied to the matview’s statistics. The same rules that govern sequential vs index scans on a base table apply here unchanged: the planner estimates how many rows the predicate selects, and if that fraction is small and an index exists, it uses the index; if the fraction is large, a sequential scan is cheaper.

With an index present and fresh statistics, a selective predicate gets an Index Scan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT sale_date, revenue
FROM   mv_daily_revenue
WHERE  region = 'EU' AND sale_date >= '2026-06-01';
Index Scan using ux_mv_daily_revenue on mv_daily_revenue
    (cost=0.29..22.40 rows=38 width=20)
    (actual time=0.018..0.071 rows=37 loops=1)
  Index Cond: ((sale_date >= '2026-06-01'::date) AND (region = 'EU'::text))
  Buffers: shared hit=5
  -- ① cost= is a unitless planner metric; actual time= is wall-clock ms
  -- ② node names the matview relation — scanned exactly like a physical table
  -- ③ rows estimate (38) ≈ actual (37): matview stats are fresh after ANALYZE

Remove the index, or ask for a large fraction of the matview, and the planner correctly prefers a full scan:

Seq Scan on mv_daily_revenue
    (cost=0.00..1840.00 rows=91000 width=20)
    (actual time=0.010..24.6 rows=90800 loops=1)
  Filter: (region = ANY ('{EU,US,APAC}'::text[]))
  Rows Removed by Filter: 4200
  -- ④ predicate selects most rows → Seq Scan is genuinely cheaper than an index

Neither is a bug. The planner is doing exactly what it does for tables — the only matview-specific wrinkle is that its statistics are only as current as your last ANALYZE.

Why ANALYZE After Refresh Is Mandatory #

A refresh — plain or CONCURRENTLY — replaces the matview’s rows but does not update its planner statistics as part of the operation. The pg_stats histogram, n_distinct, and row-count estimates continue to describe the previous contents until autovacuum eventually reanalyzes it. In the window between a refresh and that autovacuum pass, the planner is reasoning from stale numbers.

Matview Scan Decision After Refresh Flow: after a refresh, if ANALYZE has run the statistics are fresh and the planner picks the right scan; if ANALYZE has not run the statistics are stale and the planner may wrongly choose a Seq Scan over the index. REFRESH completes contents replaced, stats not updated ANALYZE run on matview? YES Fresh statistics planner picks correct scan NO Stale statistics may skip index → wrong Seq Scan Index Scan, fast Full scan, slow

The fix is one statement, run as the last step of the refresh job:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;
ANALYZE mv_daily_revenue;   -- restore accurate statistics immediately, do not wait for autovacuum

Before/After: Adding an Index Plus ANALYZE #

Starting from a freshly refreshed matview with no secondary index and stale statistics:

-- BEFORE: no index on region, stats describe pre-refresh data
Seq Scan on mv_daily_revenue
    (cost=0.00..1840.00 rows=250 width=20)     -- rows=250 estimate is wrong
    (actual time=0.012..23.9 rows=90800 loops=1)
  Filter: (region = 'EU'::text)
  Rows Removed by Filter: ...

Add the index and analyze:

CREATE INDEX ix_mv_daily_revenue_region ON mv_daily_revenue (region);
ANALYZE mv_daily_revenue;
-- AFTER: index present, statistics fresh
Index Scan using ix_mv_daily_revenue_region on mv_daily_revenue
    (cost=0.29..410.20 rows=90800 width=20)
    (actual time=0.020..8.4 rows=90800 loops=1)
  Index Cond: (region = 'EU'::text)

Both changes were required. Without the index there is nothing to choose; without ANALYZE the planner’s rows=250 estimate could talk it out of using the index even after you built it.

Common Pitfalls #

Forgetting ANALYZE after refresh. This is the number-one cause of a matview reverting to a Seq Scan in production. The refresh silently invalidates the statistics; if your job does not ANALYZE, you are relying on autovacuum’s lagging schedule to fix estimates the planner needs right now.

Expecting auto-rewrite. Building a matview and leaving base-table queries untouched changes nothing — those queries never consult the matview. Every consumer must reference the matview by name.

Planner ignoring the index at low selectivity. If a query returns most of the matview, a Seq Scan is the correct, cheaper choice and no amount of indexing will change it. That is not a failure; reserve indexes for the selective access paths and accept full scans for broad ones.

Building the index but skipping ANALYZE. A new index does not carry statistics for the planner’s row estimate. Until you ANALYZE, the planner may still price the index scan off stale numbers and decline to use it.

Frequently Asked Questions #

Does the planner ever pick a matview on its own? No. PostgreSQL has no automatic query rewrite to materialized views. The planner only considers a matview when your query names it directly. Once named, the matview is treated like any physical table and the planner chooses Seq Scan or Index Scan on it from its own statistics.

Why does the matview use a Seq Scan right after a refresh? A refresh replaces the matview’s contents but leaves its planner statistics describing the previous data until autovacuum catches up. With stale statistics the planner may misjudge selectivity and skip the index. Running ANALYZE on the matview immediately after each refresh restores accurate estimates.

Do I have to ANALYZE a matview manually? Autovacuum does analyze materialized views, but its timing lags behind your refresh, so estimates can be wrong in the window right after a refresh. Running ANALYZE explicitly as the last step of the refresh job removes that gap and keeps scan choices correct.

Related