How to Create Index on Materialized View in PostgreSQL

In PostgreSQL, you create an index on a materialized view using the CREATE INDEX statement just like on a regular table. First, create the materialized view, then run CREATE INDEX index_name ON materialized_view_name (column_name); to speed up queries on that view.

Syntax

The syntax to create an index on a materialized view is similar to creating an index on a table. You specify the index name, the materialized view name, and the column(s) to index.

• CREATE INDEX index_name: Name your index.
• ON materialized_view_name: Specify the materialized view.
• (column_name): Choose the column(s) to index.

sql

CREATE INDEX index_name ON materialized_view_name (column_name);

Example

This example shows how to create a materialized view and then add an index on one of its columns to improve query speed.

sql

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

CREATE INDEX idx_sales_summary_product_id ON sales_summary (product_id);

-- Query using the materialized view
SELECT * FROM sales_summary WHERE product_id = 101;

Output

 product_id | total_quantity 
------------+----------------
        101 |            500
(1 row)

Common Pitfalls

One common mistake is trying to create an index before the materialized view exists, which causes an error. Another is forgetting to refresh the materialized view after data changes, so the index works on outdated data.

Also, remember that indexes on materialized views do not update automatically when the underlying tables change; you must run REFRESH MATERIALIZED VIEW to update the data and keep the index useful.

sql

/* Wrong: Creating index before materialized view */
-- CREATE INDEX idx_wrong ON sales_summary (product_id); -- ERROR: relation "sales_summary" does not exist

/* Right: Create materialized view first, then index */
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

CREATE INDEX idx_sales_summary_product_id ON sales_summary (product_id);

Quick Reference

Command	Purpose
CREATE MATERIALIZED VIEW view_name AS SELECT ...	Create a materialized view
CREATE INDEX index_name ON view_name (column)	Create index on materialized view column
REFRESH MATERIALIZED VIEW view_name	Update materialized view data
DROP MATERIALIZED VIEW view_name	Remove materialized view

Key Takeaways

Create the materialized view before creating an index on it.

Use CREATE INDEX just like on tables to speed up queries on materialized views.

Remember to REFRESH MATERIALIZED VIEW to keep data and indexes up to date.

Indexes on materialized views improve read performance but do not auto-update with base tables.

Check for errors if you try to create an index on a non-existing materialized view.