Materialized View Strategy: Precompute and Index Expensive Results #

A materialized view is the one indexing tool that lets you pay an expensive aggregate or join cost once, store the result on disk, and then serve it back with a cheap indexed scan. Unlike a plain view — which is just a stored query that re-executes every time — a materialized view has its own physical storage that you refresh on a schedule. The catch that trips up most teams: PostgreSQL will never silently swap a matview in for a base-table query. You get its benefit only by querying the matview by name and indexing it like any other table.

What a Materialized View Buys You #

The core win is turning a query that costs thousands of cost units on every execution into one that costs tens. Consider a dashboard that reads daily revenue per region from a 200-million-row line_items table joined to orders. Computed live, that aggregate scans and hashes millions of rows for every page load. Precomputed into a matview keyed by (sale_date, region), the same answer is a handful of index lookups.

This makes the matview a genuine index tuning instrument rather than merely a caching convenience. You are not just storing rows — you are creating a new, much smaller relation whose grain matches your query, and then building indexes on that relation so the planner can reach individual answers without scanning the whole precomputed set.

Where a matview beats a covering index is expressiveness: an index can only reorder and cover columns of an existing table, but a matview can store the result of a GROUP BY, a multi-table join, a window function, or a distinct-count. Where it costs you is freshness — the stored result is a snapshot, correct only as of the last refresh.

The matview lifecycle #

The No-Auto-Rewrite Rule #

This is the single most important fact about matviews in PostgreSQL, and it separates them from features in some other databases: there is no automatic query rewrite to a materialized view. Some engines maintain “materialized view rewrite” where the optimizer detects that a submitted query is a subset of a stored matview and silently redirects it. PostgreSQL does not do this at all.

A matview is used if and only if the query names it. This query never touches mv_daily_revenue, even if that matview holds exactly this aggregate:

-- References base tables only → planner scans base tables, ignores any 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 benefit, the query must read the matview directly:

-- References the matview by name → planner scans matview storage
SELECT sale_date, region, revenue
FROM   mv_daily_revenue
WHERE  region = 'EU';

The practical consequence: adopting a matview is an application change, not a transparent database tweak. Every place that should benefit has to be edited to select from the matview — or wrapped behind an indirection layer, which is exactly what the query-rewrite rules for matviews page covers.

Building and Indexing a Matview #

Creation is a single statement that runs and stores the query result immediately:

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT o.sale_date,
       o.region,
       sum(li.amount)      AS revenue,
       count(*)            AS line_count
FROM   orders o
JOIN   line_items li ON li.order_id = o.order_id
GROUP  BY o.sale_date, o.region
WITH DATA;   -- populate now; WITH NO DATA leaves it unscannable until first REFRESH

The matview is now a physical relation. To make it useful for point lookups — and to unlock CONCURRENTLY refreshes — add a unique index on its natural grain:

-- UNIQUE index is mandatory for REFRESH ... CONCURRENTLY; it also speeds point lookups
CREATE UNIQUE INDEX ux_mv_daily_revenue
  ON mv_daily_revenue (sale_date, region);

-- Optional secondary index for a common filter path
CREATE INDEX ix_mv_daily_revenue_region
  ON mv_daily_revenue (region);

The unique index does double duty. PostgreSQL needs it to identify matching rows when it diffs the old and new result sets during a concurrent refresh, and the planner uses it to reach individual (sale_date, region) rows without scanning the whole matview. Choosing the right column order here follows the same logic as B-tree index optimization on a base table — leading columns should match your equality predicates.

Refresh Cost: Plain vs CONCURRENTLY #

A matview is a snapshot; keeping it useful means refreshing it, and the two refresh modes have very different plan and locking profiles.

Plain refresh rebuilds the entire result set into new storage and swaps it in:

-- Fast write, but takes an AccessExclusiveLock: readers of the matview block until it finishes
REFRESH MATERIALIZED VIEW mv_daily_revenue;

Plain refresh re-runs the defining query, writes a fresh copy, and atomically replaces the old heap. It is the faster of the two because it writes sequentially with no diffing, but it holds an AccessExclusiveLock for the whole duration — every SELECT against the matview waits. That is fine inside a maintenance window and unacceptable for a 24/7 read workload.

Concurrent refresh computes the new result into a temporary relation, diffs it against the current contents by the unique key, and applies only the changed rows:

-- Requires the UNIQUE index; allows concurrent SELECTs; slower and generates dead tuples
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

CONCURRENTLY takes only a light lock that permits reads throughout, so dashboards keep serving. The price is real: it builds a full temporary copy, runs a FULL OUTER JOIN-style diff, and issues INSERT/UPDATE/DELETE against the live matview — more I/O, longer wall-clock time, and dead tuples that autovacuum must later reclaim. The detailed model of that trade-off lives in REFRESH CONCURRENTLY cost analysis.

Reading a Matview Scan in EXPLAIN #

Because a matview has its own heap and indexes, it appears in EXPLAIN as an ordinary scan node — there is no dedicated matview operator. Here is a lookup that uses the index:

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..24.60 rows=42 width=20)
    (actual time=0.021..0.088 rows=40 loops=1)
  Index Cond: ((sale_date >= '2026-06-01'::date) AND (region = 'EU'::text))
  Buffers: shared hit=6
  -- ① cost= is a unitless planner metric; actual time= is wall-clock ms
  -- ② node names the matview relation directly — it is scanned like a table
  -- ③ rows estimate (42) tracks actual (40) → matview statistics are fresh
  -- ④ 6 buffers, all cache hits: the precomputed answer is tiny vs the base scan

Contrast that with the live base-table aggregate the matview replaces, which hashes millions of rows before it can answer. The whole point of the strategy is to move from a large HashAggregate over base tables to this small Index Scan over stored results. Whether you get the index scan or fall back to a Seq Scan depends on the matview’s own statistics — the subject of when the planner chooses a matview scan.

Step-by-Step Adoption Workflow #

Follow these steps to convert a repeated expensive aggregate into an indexed, refreshable matview.

Step 1 — Identify an expensive repeated aggregate

Find the query using pg_stat_statements: high calls, high total_exec_time, and a stable shape (the same GROUP BY grain every time).

SELECT query, calls, round(total_exec_time) AS total_ms, round(mean_exec_time) AS mean_ms
FROM   pg_stat_statements
WHERE  query ILIKE '%group by%sale_date%'
ORDER  BY total_exec_time DESC
LIMIT  10;

Step 2 — Create the matview and a unique index

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT o.sale_date, o.region, sum(li.amount) AS revenue, count(*) AS line_count
FROM   orders o JOIN line_items li ON li.order_id = o.order_id
GROUP  BY o.sale_date, o.region
WITH DATA;

CREATE UNIQUE INDEX ux_mv_daily_revenue ON mv_daily_revenue (sale_date, region);

Step 3 — Schedule a concurrent refresh

Drive REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue; from cron, pg_cron, or your job scheduler at whatever interval your staleness tolerance allows.

Step 4 — Point queries at the matview

Edit each report and application query that computed this aggregate live to select from mv_daily_revenue instead. Remember: nothing happens automatically — the query must name the matview.

Step 5 — ANALYZE the matview after refresh

-- Autovacuum does analyze matviews, but its timing lags; do it explicitly after refresh
ANALYZE mv_daily_revenue;

Step 6 — Verify an Index Scan

EXPLAIN (ANALYZE, BUFFERS)
SELECT sale_date, revenue FROM mv_daily_revenue
WHERE region = 'EU' AND sale_date >= '2026-06-01';
-- Confirm: Index Scan using ux_mv_daily_revenue, not Seq Scan; rows estimate ≈ actual.

Common Pitfalls #

Expecting automatic rewrite. The most frequent mistake is creating a matview and assuming existing base-table queries will speed up. They will not — PostgreSQL never substitutes a matview for a base-table query. Every consumer must be redirected explicitly.

CONCURRENTLY without a unique index. REFRESH MATERIALIZED VIEW CONCURRENTLY fails outright with cannot refresh materialized view "..." concurrently unless a unique index covering every row exists. Create the unique index first; if the grain has duplicates, the matview needs a different key.

Stale-data window. Between refreshes the matview is a snapshot. Consumers must tolerate data that is as old as your refresh interval. For anything that must be exactly current, keep reading the base tables.

Missing ANALYZE after refresh. A refresh replaces the matview’s contents but leaves its planner statistics describing the old data until autovacuum catches up. Run ANALYZE on the matview as the last step of every refresh job, or the planner may pick a Seq Scan over your carefully built index.

Bloat from CONCURRENTLY. Because concurrent refresh applies row-level INSERT/UPDATE/DELETE, it leaves dead tuples behind on every run. On a frequently refreshed matview this bloat accumulates; ensure autovacuum keeps up or the storage and scan cost creep upward over time.

Refresh storms. Scheduling several heavy matviews to refresh at the same instant, or refreshing far more often than the data changes, can saturate I/O and CPU. Stagger refresh jobs and match the interval to the real rate of change in the base tables.

Frequently Asked Questions #

Will PostgreSQL automatically use a materialized view for a matching base-table query? #

No. PostgreSQL has no automatic query rewrite to materialized views. A query only reads a matview if it references that matview by name. If you SELECT from the base tables, the planner scans the base tables and never substitutes the precomputed result, no matter how closely the shapes match. See when the planner chooses a matview scan for the details.

Do I need a unique index to refresh a materialized view? #

Only for REFRESH MATERIALIZED VIEW CONCURRENTLY. Plain REFRESH works without any index but takes an AccessExclusiveLock that blocks readers. CONCURRENTLY requires at least one UNIQUE index covering all rows so PostgreSQL can diff old and new rows, and in exchange it allows concurrent reads during the refresh.

Why does my query still ignore the matview index and do a Seq Scan? #

A matview is scanned from its own statistics, and those statistics go stale the moment you refresh it. If you have not run ANALYZE on the matview since the last refresh, the planner may misjudge selectivity and choose a Seq Scan over the index. Run ANALYZE on the matview after every refresh. The cost estimation models page explains how those statistics feed the scan decision.

Does a matview show up in EXPLAIN differently from a table? #

No. Because a matview has its own physical storage, EXPLAIN shows an ordinary Seq Scan or Index Scan node naming the matview relation, exactly as it would for a table. There is no special MaterializedView node.

Related #

• REFRESH CONCURRENTLY Cost Analysis — the lock, I/O, and bloat trade-off between plain and concurrent refresh
• When the Planner Chooses a Materialized View Scan — why ANALYZE after refresh decides Index Scan vs Seq Scan
• Query-Rewrite Rules for Materialized Views — approximate auto-rewrite with wrapping views and disciplined query routing
• Covering Index Design — when an index-only scan is enough and you do not need a whole matview
• B-Tree Index Optimization — order the matview’s unique index columns to match your lookup predicates

Up: Index Tuning & Strategy