Indexing materialized views in PostgreSQL

Introduction

Indexing materialized views helps speed up data retrieval by creating a shortcut to find data quickly.

When you have a materialized view that is queried often and needs faster response times.

When the materialized view contains a large amount of data and searching it is slow.

When you want to improve performance of reports or dashboards that use materialized views.

When you want to optimize queries that filter or join on specific columns in the materialized view.

Syntax

PostgreSQL

CREATE INDEX index_name ON materialized_view_name (column_name);

You create indexes on materialized views just like on regular tables.

Make sure the materialized view is refreshed before querying to have up-to-date data.

Examples

This creates an index on the customer_id column of the materialized view sales_summary_mv.

PostgreSQL

CREATE INDEX idx_mv_customer_id ON sales_summary_mv (customer_id);

This creates a unique index on the order_id column of the materialized view orders_mv.

PostgreSQL

CREATE UNIQUE INDEX idx_mv_order_id ON orders_mv (order_id);

Sample Program

This example creates a materialized view that sums sales quantity by product. Then it creates an index on product_id to speed up queries filtering by product. Finally, it queries the materialized view for product 101.

PostgreSQL

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

CREATE INDEX idx_product_sales_mv_product_id ON product_sales_mv (product_id);

-- Query using the index
SELECT * FROM product_sales_mv WHERE product_id = 101;

Important Notes

Indexes on materialized views improve query speed but do not update automatically when the base tables change.

You must refresh the materialized view manually or on a schedule to keep data current.

Creating too many indexes can slow down the refresh process, so add only needed indexes.

Summary

Materialized views store query results physically for faster access.

Indexes on materialized views work like indexes on tables to speed up searches.

Remember to refresh materialized views to keep data and indexes up to date.