Indexing materialized views in PostgreSQL - Step-by-Step Execution

Concept Flow - Indexing materialized views

Create Materialized View

↓

Materialized View Created

↓

Create Index on Materialized View

↓

Index Created

↓

Query Materialized View

↓

Index Used to Speed Up Query

↓

Refresh Materialized View as Needed

First, create a materialized view, then create an index on it to speed up queries. Refresh the view when data changes.

Execution Sample

PostgreSQL

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

CREATE INDEX idx_sales_summary_product ON sales_summary(product_id);

This code creates a materialized view summarizing sales by product and then creates an index on product_id to speed up queries.

Execution Table

Step	Action	Object Affected	Result	Notes
1	Execute CREATE MATERIALIZED VIEW	sales_summary	Materialized view created with aggregated data	Data snapshot stored
2	Execute CREATE INDEX	idx_sales_summary_product	Index created on sales_summary(product_id)	Speeds up queries filtering by product_id
3	Query materialized view filtering by product_id	sales_summary	Query uses index for faster lookup	Improved query performance
4	Refresh materialized view	sales_summary	Data updated to reflect latest sales	Index remains valid
5	Query again after refresh	sales_summary	Index used again for fast query	Consistent performance

💡 Execution stops after queries use the index and materialized view is refreshed as needed

Variable Tracker

Object	Initial State	After Step 1	After Step 2	After Step 4	Final State
sales_summary	Does not exist	Created with data snapshot	Unchanged	Refreshed with new data	Up-to-date data snapshot
idx_sales_summary_product	Does not exist	Does not exist	Created on product_id	Unchanged	Index available for queries

Key Moments - 3 Insights

Why do we create an index on a materialized view?

Creating an index on the materialized view (see step 2 in execution_table) helps speed up queries that filter or join on the indexed column, improving performance.

Does the index need to be recreated after refreshing the materialized view?

No, the index remains valid after refreshing the materialized view (see step 4 and 5). Refresh updates data but keeps the index intact.

What happens if we query the materialized view without an index?

Queries will scan the entire materialized view, which can be slower (compare step 3 with and without index). Indexes help avoid full scans.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the index created on the materialized view?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Check the 'Action' column in execution_table for 'CREATE INDEX'

According to variable_tracker, what is the state of sales_summary after step 4?

A Does not exist

B Created with data snapshot

C Refreshed with new data

D Index created

💡 Hint

Look at the 'sales_summary' row under 'After Step 4' in variable_tracker

If we do not create an index on the materialized view, what is the likely effect on queries?

A Queries run faster

B Queries run slower due to full scans

C Materialized view cannot be queried

D Materialized view refresh fails

💡 Hint

Refer to key_moments about query performance without index

Concept Snapshot

CREATE MATERIALIZED VIEW stores query results physically.
CREATE INDEX on materialized view speeds up queries.
Refresh materialized view to update data.
Index stays valid after refresh.
Use indexes to avoid full scans and improve performance.

Full Transcript

This visual execution shows how to create a materialized view in PostgreSQL, then create an index on it to speed up queries. First, the materialized view is created with aggregated data. Next, an index is created on a column to help queries filter faster. When querying the materialized view, the index is used to improve performance. Refreshing the materialized view updates the data but keeps the index intact. This process helps maintain fast query speeds on large, precomputed datasets.