Indexing materialized views in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Index on Materialized View Query Performance

Consider a materialized view mv_sales_summary created from a large sales table. You create a B-tree index on the customer_id column of the materialized view.

What is the expected effect when you run a query filtering by customer_id on mv_sales_summary?

PostgreSQL

CREATE MATERIALIZED VIEW mv_sales_summary AS SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id;
CREATE INDEX idx_mv_customer ON mv_sales_summary(customer_id);

-- Query:
SELECT * FROM mv_sales_summary WHERE customer_id = 123;

A The index will cause the query to return incorrect results.

B The index will not be used because materialized views do not support indexing.

C The query will perform a full scan of the materialized view ignoring the index.

D The query will use the index to quickly find rows for customer_id 123, improving performance.

💡 Hint

Think about how indexes help find data quickly in tables and materialized views.

Materialized views in PostgreSQL can have indexes just like tables. Creating an index on customer_id allows queries filtering on that column to use the index, speeding up data retrieval.

🧠 Conceptual

intermediate

When to Refresh Materialized Views with Indexes

You have a materialized view with several indexes. After updating the base tables, what must you do to ensure the materialized view and its indexes reflect the latest data?

A Drop and recreate the materialized view to update data and indexes.

B Only update the indexes manually; the data updates automatically.

C Run <code>REFRESH MATERIALIZED VIEW</code> to update the data and indexes.

D No action is needed; materialized views update automatically.

💡 Hint

Materialized views store data physically and need manual refresh.

Materialized views do not update automatically when base tables change. You must run REFRESH MATERIALIZED VIEW to update the data and rebuild the indexes accordingly.

📝 Syntax

advanced

Correct Syntax to Create an Index on a Materialized View

Which of the following SQL statements correctly creates a B-tree index on the order_date column of a materialized view named mv_orders?

A CREATE INDEX idx_order_date ON mv_orders(order_date);

B CREATE MATERIALIZED VIEW INDEX idx_order_date ON mv_orders(order_date);

C CREATE INDEX ON MATERIALIZED VIEW mv_orders(order_date);

D CREATE INDEX idx_order_date ON MATERIALIZED VIEW mv_orders(order_date);

💡 Hint

Creating an index on a materialized view uses the same syntax as for tables.

Materialized views are indexed like tables. The correct syntax is CREATE INDEX index_name ON materialized_view(column);

❓ optimization

advanced

Choosing Index Types for Materialized Views

You have a materialized view with a column location storing geographic points. You want to optimize queries filtering by location within a radius.

Which index type is best to create on location for this purpose?

A Create a BRIN index on the <code>location</code> column.

B Create a GiST index on the <code>location</code> column.

C Create a Hash index on the <code>location</code> column.

D Create a B-tree index on the <code>location</code> column.

💡 Hint

Think about spatial data and which index type supports geometric queries.

GiST indexes support spatial data types and queries like radius searches. B-tree and Hash indexes do not support spatial operators efficiently.

🔧 Debug

expert

Diagnosing Slow Queries on Indexed Materialized Views

You created a materialized view mv_customer_orders with an index on customer_id. However, queries filtering by customer_id are still slow and do not use the index.

What is the most likely cause?

A The query uses a function on <code>customer_id</code> preventing index use.

B The materialized view was not refreshed after base table updates, so the index is outdated.

C The index was created with the wrong column name.

D PostgreSQL does not support indexes on materialized views.

💡 Hint

Consider how query conditions affect index usage.

If the query applies a function or expression on the indexed column (e.g., LOWER(customer_id)), the index on the raw column cannot be used unless a functional index exists.