Indexing Materialized Views for Dashboards #

A materialized view reduced 400 million orders to 90,000 summary rows, and the dashboard still takes 700 ms per panel. The matview has one unique index, created because REFRESH CONCURRENTLY requires one, and every panel filters by region and date range — so every panel sequentially scans 90,000 rows, eight times per page load.

Precomputing is only half the job, as noted in materialized view strategy. A materialized view is a table: it needs indexes and statistics like any other.

The Condition #

A materialized view stores its result in heap storage with no indexes beyond those you create. Three consequences:

The unique index required by REFRESH … CONCURRENTLY is chosen for the refresh’s diff, not for queries. It is usually on the full grouping key, in an order nobody filters by, and it is frequently the only index present.

Because a matview is small relative to its base tables, the cost of extra indexes is small too: the write cost is paid once per refresh rather than on every base-table write.

A matview is a table with three needs A stack. The stored result is the precomputed rows. The unique index exists for concurrent refresh. Query indexes serve the dashboard's filters and ordering. An ANALYZE after each refresh keeps the planner's estimates realistic. Without the last two, queries scan the whole matview. stored result the precomputed rows unique index required by REFRESH CONCURRENTLY query indexes the dashboard's filters and order ANALYZE after refresh autovacuum never does it the last two are the ones teams forget

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT day, revenue FROM mv_revenue_daily
WHERE region = 'emea' AND day >= current_date - 90
ORDER BY day;

Before:

Sort  (actual time=684.2..684.4 rows=90 loops=1)
  Sort Key: day
  ->  Seq Scan on mv_revenue_daily  (cost=0.00..2104.00 rows=450 width=20)
                                    (actual time=0.9..683.1 rows=90 loops=1)
        Filter: ((region = 'emea'::text) AND (day >= (CURRENT_DATE - 90)))
        Rows Removed by Filter: 89910
        Buffers: shared hit=1204
Execution Time: 684.9 ms
-- rows=450 estimate is a default: the matview has never been analyzed
-- 90,000 rows scanned per panel, eight panels per page

After indexing and analyzing:

CREATE INDEX mv_revenue_daily_region_day_idx ON mv_revenue_daily (region, day) INCLUDE (revenue);
ANALYZE mv_revenue_daily;
Index Only Scan using mv_revenue_daily_region_day_idx on mv_revenue_daily
    (cost=0.29..8.92 rows=88 width=20) (actual time=0.02..0.03 rows=90 loops=1)
  Index Cond: ((region = 'emea'::text) AND (day >= (CURRENT_DATE - 90)))
  Heap Fetches: 0
  Buffers: shared hit=4
Execution Time: 0.05 ms
-- no sort: the index provides (region, day) order
Two missing pieces, one plan Three lines are annotated. A sequential scan with rows removed by filter shows no usable index. An estimate that is a round fraction of the row count shows missing statistics. A sort node shows the ordering is not provided by any index. Seq Scan … Rows Removed by Filter: 89910 no index for the filter rows=450 (default estimate) matview never analyzed Sort Sort Key: day ordering not provided by an index a matview is planned exactly like a table, with the same needs

Step-by-Step Resolution #

  1. Collect the dashboard’s real queries. Panels usually share a few filter and ordering shapes; pg_stat_statements filtered by the matview’s name shows them.

  2. Create one index per access shape, leading with equality columns and ending with the ordering column, adding INCLUDE columns for the returned values:

    CREATE INDEX mv_revenue_daily_region_day_idx
  ON mv_revenue_daily (region, day) INCLUDE (revenue, orders);

  3. Keep the unique index that REFRESH … CONCURRENTLY needs, even if no query uses it.

  4. Analyze after every refresh. Add it to the refresh job:

    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_revenue_daily;
ANALYZE mv_revenue_daily;

  5. Vacuum after concurrent refreshes. They leave dead rows, which also keeps Heap Fetches above zero for index-only scans:

    VACUUM (ANALYZE) mv_revenue_daily;

  6. Check plans from the application’s role, since dashboards often run through a different role with different settings.

  7. Re-check after the matview grows — an index that was unnecessary at 5,000 rows matters at 500,000.

One dashboard panel, four configurations Querying the base tables directly takes 362 seconds. Querying the unindexed matview takes 685 milliseconds. Adding the region and day index takes 0.4 milliseconds. Adding INCLUDE columns and analyzing takes 0.05 milliseconds. base tables directly 362 s matview, no index 685 ms + (region, day) index 0.4 ms + INCLUDE + ANALYZE 0.05 ms precomputing gained 500×; indexing it gained another 13,000×

Before and After #

-- BEFORE: only the refresh's unique index, never analyzed
Sort → Seq Scan on mv_revenue_daily  Rows Removed by Filter: 89910   Execution Time: 684.9 ms

-- AFTER: (region, day) INCLUDE (revenue, orders) + ANALYZE
Index Only Scan  Heap Fetches: 0  Buffers: shared hit=4               Execution Time: 0.05 ms

Statistics on matviews deserve special attention #

Because autovacuum ignores materialized views entirely, they are the most common place to find a table planned on defaults. The symptoms are the familiar round numbers — estimates of 0.5% of the rows, or exactly 200 groups — described in default selectivity for unestimable predicates. On a small matview queried alone, the wrong estimate rarely changes the plan. It matters when the matview is joined to other tables: a summary joined to a dimension table with a defaulted estimate can produce a nested loop over hundreds of thousands of rows.

A plain REFRESH also resets the matview’s statistics to nothing, because it rewrites the relation. A refresh job that does not analyze therefore alternates between planning on good statistics and planning on none, which shows up as plans that change shape every refresh cycle without any data change. Adding ANALYZE to the job costs seconds on a small matview and removes the whole class of problem.

How many indexes a matview can afford #

The usual objection to adding indexes — write amplification — barely applies here. A base table pays index maintenance on every insert and update, continuously; a materialized view pays it once per refresh. If the matview refreshes every ten minutes and serves eight dashboard panels, four well-chosen indexes cost four index builds per refresh (or incremental maintenance under CONCURRENTLY) and save eight full scans on every page load, by hundreds of users.

The practical limit is refresh duration. Measure it with and without each candidate index: on a small matview the difference is usually a few hundred milliseconds, which is irrelevant next to the query savings. On a large matview refreshed frequently, index rebuild time can dominate the refresh, and that is the point at which an incremental summary table — where only changed rows are written, so only those index entries are maintained — becomes the better structure.

Common Pitfalls #

Relying on the refresh’s unique index for queries. Its columns and order suit the diff, not the dashboard. Diagnostic signal: sequential scans despite an index existing. Fix: index for the query shapes.

Forgetting ANALYZE. Autovacuum never analyzes matviews. Diagnostic signal: default-looking estimates. Fix: analyze in the refresh job.

Indexing every column “just in case”. Each index is rebuilt or maintained at every refresh. Diagnostic signal: refresh time growing with index count. Fix: index the shapes that are actually queried.

Dead rows after concurrent refreshes. They inflate the matview and block index-only scans. Diagnostic signal: Heap Fetches above zero and growing size. Fix: vacuum after refresh.

Frequently Asked Questions #

Do materialized views need their own indexes? #

Yes. A materialized view is stored like a table and is planned like one, so filters, joins and ordering against it need indexes or the query scans all of its rows.

Does autovacuum analyze materialized views? #

No. They are never analyzed automatically, so the planner may use default estimates for them. Run ANALYZE explicitly, ideally as part of the refresh job.

Do indexes survive REFRESH MATERIALIZED VIEW? #

Yes. A plain refresh rebuilds them as part of replacing the contents, and a concurrent refresh maintains them incrementally. You do not need to recreate indexes after a refresh, but you do need to analyze.

Up: Materialized View Strategy