Indexes on materialized views: the MV is a table, index it as such

Lesson overview

Creating an MV with a unique index to support REFRESH CONCURRENTLY (lessons 03-04) is only the first index — the one the refresh needs. But the frontend's queries against the MV are another story. If your dashboard does SELECT * FROM mv_top_posts_weekly WHERE category_id = 5 ORDER BY view_count DESC LIMIT 10, without an appropriate index the query scans the whole MV. For a 10-row MV that's invisible. For a 500K-row one, that's 200ms lost where it could be 2ms.

The common misunderstanding is thinking "the MV is already precomputed, the queries are fast by construction." False. The MV is a table. Queries against it follow the same rules as any table: filters without an index → seq scan; ORDER BY without an index → sort in memory; joins without indexes → costly loops. This lesson teaches you to design indexes on MVs according to the frontend's queries, just as you would with any table in the schema.

By the end you'll know: when to add an additional B-tree, when composite, when partial, how to verify with EXPLAIN ANALYZE that your MV uses the expected indexes, and how to avoid over-indexing (each extra index slows down the refresh).

Mental model: the MV is the table, the indexes are the indexes

Remember that a materialized MV is a table with data stored on disk. PostgreSQL treats it exactly like any table:

• It has a heap (the actual rows).
• It has metadata in pg_class, pg_attribute.
• It accepts CREATE INDEX with all the options (B-tree, GIN, GiST, partial, expression).
• It shows up in pg_stat_user_tables and pg_stat_user_indexes.

The only difference: it doesn't accept direct INSERT/UPDATE/DELETE (it only changes with REFRESH).

┌──────────────────────────────────────────────────────────────────┐
│  mv_top_posts_weekly (materialized table)                        │
│                                                                  │
│  Heap (rows):                                                    │
│  ┌────────┬─────────┬──────────┬────────────┬──────────────┐     │
│  │ post_id│ title   │ slug     │ view_count │ category_id  │     │
│  ├────────┼─────────┼──────────┼────────────┼──────────────┤     │
│  │  142   │ Post X  │ post-x   │   8421     │  3           │     │
│  │  87    │ Post Y  │ post-y   │   6133     │  1           │     │
│  │  ...   │ ...     │ ...      │   ...      │  ...         │     │
│  └────────┴─────────┴──────────┴────────────┴──────────────┘     │
│                                                                  │
│  Indexes:                                                        │
│  ┌──────────────────────────────────────────────────────────┐    │
│  │ idx_mv_top_posts_pk ON (post_id) UNIQUE                  │    │
│  │   ← mandatory for REFRESH CONCURRENTLY                   │    │
│  │                                                          │    │
│  │ idx_mv_top_posts_category ON (category_id)               │    │
│  │   ← speeds up WHERE category_id = ? queries              │    │
│  │                                                          │    │
│  │ idx_mv_top_posts_views ON (view_count DESC)              │    │
│  │   ← speeds up ORDER BY view_count DESC LIMIT N           │    │
│  └──────────────────────────────────────────────────────────┘    │
└──────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. The unique index for CONCURRENTLY is just one. It's the "refresh" index. For the queries to be fast, you need additional indexes according to the queries that read it. A unique index on post_id doesn't speed up WHERE category_id = 5 — they're different things.

2. Each index added increases the refresh cost. PostgreSQL maintains each index at the end of the refresh (rebuilds or updates it). An MV with 5 indexes refreshes slower than one with 1. Over-indexing is real — add only the indexes that justify real queries.

3. Queries against the MV are analyzed the same as against normal tables. EXPLAIN ANALYZE SELECT * FROM mv_x WHERE y = 5 shows Index Scan or Seq Scan just like any table. The criteria for indexing (selectivity, frequently filtered columns, recurring ORDER BYs) are the same as in guide #12.

Design indexes according to the queries: the pattern

The process is always the same:

1. List the queries the frontend will run against the MV.
2. Identify filtered columns (WHERE) and ordered columns (ORDER BY).
3. Design indexes to cover those columns.
4. Verify with EXPLAIN ANALYZE that the indexes are used.
5. Iterate if any isn't used or if new queries appear.

Applied example: blog dashboard

Your MV is:

CREATE MATERIALIZED VIEW mv_post_summary AS
SELECT
    p.id AS post_id,
    p.title,
    p.slug,
    p.category_id,
    u.username AS author,
    count(v.id) AS view_count,
    count(DISTINCT c.id) AS comment_count,
    p.published_at,
    NOW() AS computed_at
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN views v ON v.post_id = p.id
    AND v.created_at > NOW() - INTERVAL '30 days'
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title, p.slug, p.category_id, u.username, p.published_at;

-- Index for CONCURRENTLY (mandatory)
CREATE UNIQUE INDEX idx_mv_post_summary_pk ON mv_post_summary (post_id);

This MV has ~250K rows (one row per published post). With only the unique index, every SELECT that filters by something other than post_id will do a seq scan.

Queries the frontend runs:

-- Query 1: paginated list of posts by category
SELECT * FROM mv_post_summary WHERE category_id = $1
ORDER BY published_at DESC LIMIT 20 OFFSET $2;

-- Query 2: top posts by views (homepage)
SELECT * FROM mv_post_summary
ORDER BY view_count DESC LIMIT 10;

-- Query 3: posts by an author
SELECT * FROM mv_post_summary WHERE author = $1
ORDER BY published_at DESC LIMIT 20;

-- Query 4: recent posts with many comments
SELECT * FROM mv_post_summary
WHERE comment_count > 10
ORDER BY published_at DESC LIMIT 20;

Analysis of each query:

Indexes to create:

-- For query 1
CREATE INDEX idx_mv_ps_category_published
    ON mv_post_summary (category_id, published_at DESC);

-- For query 2
CREATE INDEX idx_mv_ps_views
    ON mv_post_summary (view_count DESC);

-- For query 3
CREATE INDEX idx_mv_ps_author_published
    ON mv_post_summary (author, published_at DESC);

-- For query 4 (partial)
CREATE INDEX idx_mv_ps_high_comments
    ON mv_post_summary (published_at DESC)
    WHERE comment_count > 10;

5 indexes total (1 unique + 4 for queries). Each one covers a frontend query. There's no over-indexing — each index justifies a real query.

Verify with EXPLAIN ANALYZE

After creating the indexes, verify that the queries use them.

Before the indexes

EXPLAIN ANALYZE
SELECT * FROM mv_post_summary
WHERE category_id = 3
ORDER BY published_at DESC LIMIT 20;

Output (without an index on category_id):

Limit  (cost=12834.45..12834.50 rows=20)
       (actual time=185.123..185.131 rows=20 loops=1)
  ->  Sort
        ->  Seq Scan on mv_post_summary
              Filter: (category_id = 3)
              Rows Removed by Filter: 247831

Planning Time: 0.412 ms
Execution Time: 185.234 ms

185ms to scan 250K rows and filter by category_id = 3. Terrible.

After the indexes

EXPLAIN ANALYZE
SELECT * FROM mv_post_summary
WHERE category_id = 3
ORDER BY published_at DESC LIMIT 20;

Output (with idx_mv_ps_category_published):

Limit  (cost=0.42..3.21 rows=20)
       (actual time=0.041..0.087 rows=20 loops=1)
  ->  Index Scan using idx_mv_ps_category_published on mv_post_summary
        Index Cond: (category_id = 3)

Planning Time: 0.234 ms
Execution Time: 0.124 ms

0.12ms — ~1500× faster. The index does a direct lookup to the category and returns rows pre-sorted by published_at DESC (because the index is also sorted that way).

Lesson: without verifying with EXPLAIN, you don't know whether your index helps. Every new query against the MV requires analysis.

Types of indexes on MVs

B-tree (default, used in >90% of cases)

For columns with high cardinality and equality/range/order queries:

CREATE INDEX idx_mv_x_col ON mv_x (col);
CREATE INDEX idx_mv_x_col_desc ON mv_x (col DESC);
CREATE INDEX idx_mv_x_composite ON mv_x (col1, col2 DESC, col3);

Partial index (when a subset is queried a lot)

If most queries filter by a specific condition (e.g. WHERE active = true), a partial index reduces size and cost:

CREATE INDEX idx_mv_x_active
    ON mv_x (created_at DESC)
    WHERE active = true;

PostgreSQL only indexes rows where active = true. If that condition is always part of the queries' WHERE, the index is smaller (cache-friendly) and the refresh updates fewer entries.

Typical case in MVs: filtering "published posts," "non-deleted comments," "active users."

GIN (when there's JSONB or full-text in the MV)

If your MV has JSONB or tsvector columns (result of FTS), use GIN:

CREATE INDEX idx_mv_x_metadata
    ON mv_x USING gin (metadata jsonb_path_ops);

CREATE INDEX idx_mv_x_search_vector
    ON mv_x USING gin (search_vector);

Same syntax as on normal tables (covered in modules 1-3).

Expression index (when filtering by a transformation)

If the frontend does WHERE LOWER(author) = 'pepe':

CREATE INDEX idx_mv_x_author_lower
    ON mv_x (LOWER(author));

Useful but rare in MVs — it's usually better to materialize the transformation as a direct column in the MV's SELECT (e.g. LOWER(author) AS author_lower) and index the column.

The refresh cost increases with each index

Each additional index increases the REFRESH CONCURRENTLY time. PostgreSQL has to maintain all the indexes at the end of the refresh.

Empirical measurement

On mv_post_summary (250K rows):

\timing on

-- Only the unique index (1 index)
DROP INDEX IF EXISTS idx_mv_ps_category_published;
DROP INDEX IF EXISTS idx_mv_ps_views;
DROP INDEX IF EXISTS idx_mv_ps_author_published;
DROP INDEX IF EXISTS idx_mv_ps_high_comments;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_summary;
-- Time: 8421 ms (8.4s)

-- With 5 indexes
CREATE INDEX idx_mv_ps_category_published ON mv_post_summary (category_id, published_at DESC);
CREATE INDEX idx_mv_ps_views ON mv_post_summary (view_count DESC);
CREATE INDEX idx_mv_ps_author_published ON mv_post_summary (author, published_at DESC);
CREATE INDEX idx_mv_ps_high_comments ON mv_post_summary (published_at DESC) WHERE comment_count > 10;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_summary;
-- Time: 13421 ms (13.4s)

5 indexes vs 1 → refresh ~60% slower. For massive MVs (millions of rows), the effect is accentuated.

Rule of thumb

Each index must justify at least 1 frequent frontend query. If you added an index 6 months ago for a feature that was removed, drop it:

-- Identify unused indexes
SELECT
    schemaname,
    relname AS table_or_mv,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname LIKE 'mv_%'
ORDER BY idx_scan ASC;

Any index with idx_scan = 0 for weeks is a candidate for DROP INDEX. Except the mandatory unique index for CONCURRENTLY — that one isn't dropped even though it may seem "unused by queries" (the refresh uses it internally).

Indexes and column order in composite

For composite indexes, the column order matters a lot. General rule:

1. Columns with equality (=) first.
2. Columns with range (>, <, BETWEEN) after.
3. Columns used in ORDER BY at the end, in the same order.

Example

Query:

SELECT * FROM mv_post_summary
WHERE category_id = 3
  AND published_at > NOW() - INTERVAL '30 days'
ORDER BY published_at DESC LIMIT 20;

Optimal index:

CREATE INDEX idx_mv_ps_cat_pub
    ON mv_post_summary (category_id, published_at DESC);

• category_id first (equality).
• published_at DESC after (range + ORDER BY).

PostgreSQL does a direct lookup to category_id = 3, walks the index already sorted by published_at DESC, applies the range filter, and returns the first 20.

Sub-optimal index:

CREATE INDEX idx_mv_ps_pub_cat
    ON mv_post_summary (published_at DESC, category_id);

Here PostgreSQL can't jump straight to "category 3" — it has to scan the whole index (sorted by published_at) filtering by category_id = 3 row by row. Much slower.

Verify with EXPLAIN ANALYZE that the index uses the equality column as the prefix.

Covering indexes (PostgreSQL 11+)

For queries that only select certain columns, you can add additional columns to the index with INCLUDE:

CREATE INDEX idx_mv_ps_category_covering
    ON mv_post_summary (category_id, published_at DESC)
    INCLUDE (post_id, title, slug);

If the query is:

SELECT post_id, title, slug FROM mv_post_summary
WHERE category_id = 3
ORDER BY published_at DESC LIMIT 20;

PostgreSQL does an index-only scan — it returns results without touching the heap. Faster and less disk read.

Useful for: queries that return few columns and run very frequently. Trade-off: the index is larger, the refresh slower.

Why does this matter on the job?

1. The difference between "MV created" and "useful MV" is the query indexes. Creating the MV with a unique index passes the "it exists and refreshes" test. But if the dashboard is still slow because the queries do a seq scan over 500K rows, you solved nothing. The MV without appropriate indexes is just a "precomputed table that's queried slowly."

2. Over-indexing is an invisible problem until the refresh becomes slow. The team adds indexes "just in case" for 6 months. One day they notice the refresh takes 2 minutes instead of 30 seconds. The cause: 12 accumulated indexes, half without queries that use them. Auditing with pg_stat_user_indexes and dropping the unused ones restores the refresh time.

3. Code review of PRs with MVs requires reviewing indexes. When someone submits a PR creating mv_x, the right questions are: "what frontend queries read it? are there indexes to cover them? is the unique index for CONCURRENTLY there?" Without this lesson, code review on MVs is generic.

4. Badly ordered composite indexes are one of the most subtle bugs. The index exists, it seems fine, but EXPLAIN ANALYZE shows it's not used. The typical cause is a column order that doesn't match the WHERE/ORDER BY pattern. Knowing the rule (equality first, range/order after) avoids that debug.

5. Covering indexes (INCLUDE) is a feature many don't know. For hot-path queries, adding INCLUDE can give improvements of 2-5× without adding significant data duplication. It's technical vocabulary that distinguishes the senior from the mid.

Pitfalls and common mistakes

Mistake 1 (conceptual): assuming the MV "needs no more indexes than the unique one"

Symptom: you create the MV with a unique index for CONCURRENTLY, you assume the queries are fast because "it's already precomputed." The queries are still slow (200ms+).

Why it happens: confusing "precomputed data" (yes, the MV already has the results of the joins/aggregations) with "automatically fast queries" (no — queries against the MV follow the same indexing rules as any table).

How to tell: EXPLAIN ANALYZE on the frontend query shows Seq Scan on mv_x with Rows Removed by Filter: many.

How to fix: identify the frontend queries, add appropriate indexes according to the pattern (equality first in composite, ORDER BY at the end).

Mistake 2 (practical): over-indexing "just in case"

Symptom: the MV has 8 indexes. The refresh takes 3× the expected. Some indexes never appear in EXPLAIN.

Why it happens: the team added speculative indexes, or the original feature changed and the indexes were left orphaned.

How to tell:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'mv_x'
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 for weeks are candidates for a drop.

How to fix: DROP INDEX the unused ones. Reset the stats with pg_stat_reset() if you need to measure from scratch.

Mistake 3 (conceptual): wrong column order in composite

Symptom: you create CREATE INDEX idx ON mv_x (col_a, col_b), but the query is WHERE col_b = 5 ORDER BY col_a. EXPLAIN shows Seq Scan or a sub-optimal Bitmap Index Scan.

Why it happens: PostgreSQL can use prefixes of a composite index. If your query filters by col_b but the index starts with col_a, PostgreSQL can't jump straight — it has to scan everything or use a bitmap.

How to tell: EXPLAIN ANALYZE doesn't show Index Scan using idx ... Index Cond: (col_b = 5).

How to fix: reorder the index so the equality columns come first:

DROP INDEX idx;
CREATE INDEX idx ON mv_x (col_b, col_a);

Mistake 4 (practical): index on a column that changes on every refresh (computed_at)

Symptom: you create CREATE INDEX ON mv_x (computed_at) (the column filled with NOW() on every refresh). The refresh is very slow.

Why it happens: computed_at changes for all rows on every refresh. PostgreSQL has to update the entire index, which costs as much as rebuilding it. With CONCURRENTLY, this multiplies the cost.

How to tell: \timing on; REFRESH ... shows high times. Removing the index and refreshing again is 2-3× faster.

How to fix: don't index columns that change on every refresh, unless it's strictly necessary for queries. computed_at is rarely used in WHERE — it's for showing "last updated," not for filtering.

Mistake 5 (conceptual): assuming partial indexes need no maintenance

Symptom: you create CREATE INDEX ... WHERE active = true and then active changes for many rows in the refresh. Bloat appears or the index gets out of date.

Why it happens: PostgreSQL maintains partial indexes correctly, but if the WHERE condition is very volatile (many rows enter/leave the subset on every refresh), the maintenance cost accumulates.

How to tell: pg_stat_user_indexes shows high idx_tup_read vs idx_tup_fetch on the partial index, suggesting bloat.

How to fix: consider whether the partial is worth it or whether a full index is better. For stable conditions (WHERE deleted_at IS NULL on published posts), partial is a good idea. For volatile conditions (WHERE last_login > NOW() - INTERVAL '7 days' that changes on every refresh), full is better.

Exercises

Exercise 1: design indexes for 4 queries

Your MV is:

CREATE MATERIALIZED VIEW mv_user_activity AS
SELECT
    u.id AS user_id,
    u.username,
    u.country_code,
    count(p.id) AS post_count,
    count(c.id) AS comment_count,
    max(p.published_at) AS last_post_at,
    NOW() AS computed_at
FROM users u
LEFT JOIN posts p ON p.author_id = u.id AND p.published_at IS NOT NULL
LEFT JOIN comments c ON c.author_id = u.id
GROUP BY u.id, u.username, u.country_code;

CREATE UNIQUE INDEX idx_mv_ua_pk ON mv_user_activity (user_id);

The frontend's queries:

-- Query A
SELECT * FROM mv_user_activity WHERE country_code = 'AR' ORDER BY post_count DESC LIMIT 50;

-- Query B
SELECT * FROM mv_user_activity WHERE username = $1;

-- Query C
SELECT * FROM mv_user_activity ORDER BY last_post_at DESC NULLS LAST LIMIT 100;

-- Query D
SELECT user_id, username FROM mv_user_activity WHERE post_count > 10 AND comment_count > 50;

Design the appropriate indexes.

CREATE INDEX idx_mv_ua_country_posts
    ON mv_user_activity (country_code, post_count DESC);

CREATE UNIQUE INDEX idx_mv_ua_username
    ON mv_user_activity (username);

CREATE INDEX idx_mv_ua_last_post
    ON mv_user_activity (last_post_at DESC NULLS LAST);

-- Option 1: covering index to avoid touching the heap
CREATE INDEX idx_mv_ua_active
    ON mv_user_activity (post_count, comment_count)
    INCLUDE (user_id, username)
    WHERE post_count > 10 AND comment_count > 50;

Exercise 2: detect the badly ordered index

Your MV:

CREATE MATERIALIZED VIEW mv_orders_summary AS
SELECT
    customer_id,
    status,
    total_amount,
    created_at
FROM orders;

CREATE INDEX idx_orders_status_created
    ON mv_orders_summary (created_at DESC, status);

The frontend query:

SELECT * FROM mv_orders_summary
WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 20;

EXPLAIN ANALYZE shows Seq Scan instead of Index Scan. Why? How do you fix it?

DROP INDEX idx_orders_status_created;

CREATE INDEX idx_orders_status_created_v2
    ON mv_orders_summary (status, created_at DESC);

EXPLAIN ANALYZE
SELECT * FROM mv_orders_summary
WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 20;

Limit
  ->  Index Scan using idx_orders_status_created_v2 on mv_orders_summary
        Index Cond: (status = 'pending')

Exercise 3: audit unused indexes

On the MV mv_post_summary with 5 indexes, simulate traffic, wait 24 hours, and then identify which indexes aren't used.

-- 1. Reset statistics (to start from scratch)
SELECT pg_stat_reset();

-- 2. Simulate traffic (your app already did this in production)
-- The real frontend queries run here.

-- 3. After 24h or enough traffic, audit:
SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'mv_post_summary'
ORDER BY idx_scan ASC;

       index_name              | times_used | idx_tup_read | size
-------------------------------+------------+--------------+----------
 idx_mv_ps_high_comments       |     0      |       0      | 4 MB
 idx_mv_ps_author_published    |    12      |     240      | 18 MB
 idx_mv_ps_views               |   8421     |   84210      | 12 MB
 idx_mv_ps_category_published  |  15234     |  304680      | 22 MB
 idx_mv_post_summary_pk        |  18421     |  368420      | 8 MB  -- DO NOT TOUCH (CONCURRENTLY)

DROP INDEX idx_mv_ps_high_comments;
-- Consider dropping idx_mv_ps_author_published after confirming with the team.

Exercise 4: implement a covering index for a hot-path query

The frontend runs this query 5000 times per hour:

SELECT post_id, title, slug FROM mv_post_summary
WHERE category_id = $1
ORDER BY published_at DESC LIMIT 5;

You have the index idx_mv_ps_category_published ON mv_post_summary (category_id, published_at DESC). It works but you want to speed it up more with covering. Design the covering index and measure the improvement.

CREATE INDEX idx_mv_ps_category_published
    ON mv_post_summary (category_id, published_at DESC);

Limit
  ->  Index Scan using idx_mv_ps_category_published on mv_post_summary
        Index Cond: (category_id = 3)
        Heap Fetches: 5
       (actual time=0.234..0.421 rows=5)

DROP INDEX idx_mv_ps_category_published;

CREATE INDEX idx_mv_ps_category_published_covering
    ON mv_post_summary (category_id, published_at DESC)
    INCLUDE (post_id, title, slug);

Limit
  ->  Index Only Scan using idx_mv_ps_category_published_covering on mv_post_summary
        Index Cond: (category_id = 3)
        Heap Fetches: 0
       (actual time=0.087..0.124 rows=5)

Exercise 5: measure the cost of over-indexing on the refresh

On mv_post_summary (250K rows), create 10 indexes (some useful, others redundant). Measure the refresh time with 1, 3, 6, 10 indexes. Report the pattern.

\timing on

-- Only the unique index (1)
DROP INDEX IF EXISTS idx_a;
DROP INDEX IF EXISTS idx_b;
-- ... (drop all)

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_summary;
-- Time: 8421 ms (1 index)

-- Add 2 more (total 3)
CREATE INDEX idx_a ON mv_post_summary (category_id, published_at DESC);
CREATE INDEX idx_b ON mv_post_summary (view_count DESC);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_summary;
-- Time: 10231 ms (3 indexes)

-- Add 3 more (total 6)
CREATE INDEX idx_c ON mv_post_summary (author);
CREATE INDEX idx_d ON mv_post_summary (published_at DESC);
CREATE INDEX idx_e ON mv_post_summary (comment_count DESC);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_summary;
-- Time: 12842 ms (6 indexes)

-- Add 4 more (total 10)
CREATE INDEX idx_f ON mv_post_summary (slug);
CREATE INDEX idx_g ON mv_post_summary (category_id);
CREATE INDEX idx_h ON mv_post_summary (post_id, view_count);
CREATE INDEX idx_i ON mv_post_summary (LOWER(author));

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_summary;
-- Time: 18234 ms (10 indexes)

Summary and next step

In this lesson you learned to index materialized views like any table:

• The MV is a table. It accepts CREATE INDEX with all the options (B-tree, partial, covering, GIN). Queries against it follow the same planning rules as any table.

• The unique index for CONCURRENTLY is only the first one. For the frontend's queries to be fast, you need additional indexes according to the WHERE and ORDER BY patterns.

• Composite indexes: equality first, range/order after. The column order determines whether PostgreSQL can use the index for filtering and ordering simultaneously.

• Each index added slows down the refresh. The growth is more-than-linear. Indexes are justified one by one with real frontend queries.

• INCLUDE for covering indexes speeds up hot-path queries by avoiding heap access. Trade-off: larger index, slower refresh. Reserve for ultra-frequent queries.

• Auditing with pg_stat_user_indexes identifies orphaned indexes. Indexes with idx_scan = 0 for weeks are candidates for DROP INDEX (except the unique one for CONCURRENTLY).

Before moving on, you should be able to:

• Design indexes for a new MV based on the frontend's queries.
• Verify with EXPLAIN ANALYZE that the indexes are used.
• Detect over-indexing and drop what doesn't help.
• Order columns correctly in composite indexes.

Next lesson — analytics use cases: dashboards and reports. So far you've seen how to create, refresh, and index MVs in the abstract. Lesson 06 lands it all in the central case of the guide: a blog dashboard with 4 panels, each backed by an MV. You're going to see the EXPLAIN ANALYZE before/after of each query, compute the total latency improvement, and implement the FastAPI endpoint that serves the dashboard. It's where everything you learned becomes a functional system.

Resources

1. PostgreSQL 16 — CREATE INDEX — complete reference, includes INCLUDE and partial.
2. PostgreSQL 16 — Indexes and ORDER BY — when the planner can use an index to avoid a Sort.
3. PostgreSQL 16 — Multicolumn Indexes — rules of composite indexes and column order.
4. Crunchy Data — Index Maintenance for Materialized Views — indexing patterns in production.
5. pganalyze — Composite Indexes Explained — deep analysis of column order.
6. Use The Index, Luke! — classic reference on effective indexing (not PostgreSQL-specific but universal).

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: Analytics use cases — blog dashboard with MVs and before/after benchmarks.