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.

Practice

1. What is the main purpose of creating an index on a materialized view in PostgreSQL?

easy

A. To speed up query performance on the materialized view

B. To automatically refresh the materialized view

C. To store the materialized view data physically

D. To prevent the materialized view from being updated

Solution

1. Step 1: Understand what an index does

   An index helps the database find data faster by creating a quick lookup structure.

2. Step 2: Apply this to materialized views

   Since materialized views store query results physically, indexing them speeds up queries on that stored data.

3. Final Answer:

   To speed up query performance on the materialized view -> Option A

4. Quick Check:

   Indexing = Faster queries [OK]

Hint: Indexes speed up data retrieval on stored results [OK]

Common Mistakes:

• Thinking indexes refresh the view automatically
• Confusing storage with indexing
• Believing indexes prevent updates

2. Which of the following is the correct syntax to create an index on a materialized view named sales_summary on column region?

easy

A. CREATE INDEX idx_region ON TABLE sales_summary(region);

B. CREATE INDEX idx_region ON MATERIALIZED VIEW sales_summary(region);

C. CREATE INDEX idx_region ON sales_summary(region);

D. CREATE INDEX idx_region ON VIEW sales_summary(region);

Solution

1. Step 1: Recall index creation syntax

   Indexes are created with CREATE INDEX index_name ON table_name(column); syntax.

2. Step 2: Apply to materialized views

   Materialized views behave like tables for indexing, so no extra keywords like MATERIALIZED VIEW or VIEW are used.

3. Final Answer:

   CREATE INDEX idx_region ON sales_summary(region); -> Option C

4. Quick Check:

   Index syntax = CREATE INDEX name ON table(column) [OK]

Hint: Use table syntax for materialized views when creating indexes [OK]

Common Mistakes:

• Adding MATERIALIZED VIEW keyword in index creation
• Using VIEW keyword instead of table name
• Incorrect syntax with TABLE keyword

3. Given a materialized view mv_orders with an index on order_date, what will be the output of this query?

SELECT order_date FROM mv_orders WHERE order_date = '2023-01-01';

Assuming the materialized view is refreshed and contains data for that date.

medium

A. Returns all rows with order_date '2023-01-01' quickly using the index

B. Returns an error because indexes cannot be used on materialized views

C. Returns no rows because materialized views do not store data

D. Returns all rows but ignores the index, causing slow performance

Solution

1. Step 1: Understand index usage on materialized views

   Indexes on materialized views speed up queries by quickly locating matching rows.

2. Step 2: Analyze the query behavior

   The query filters by order_date, which is indexed, so it returns matching rows efficiently.

3. Final Answer:

   Returns all rows with order_date '2023-01-01' quickly using the index -> Option A

4. Quick Check:

   Indexed query = fast matching rows [OK]

Hint: Indexed columns speed up matching queries on materialized views [OK]

Common Mistakes:

• Thinking indexes don't work on materialized views
• Assuming materialized views are empty without refresh
• Believing query ignores indexes always

4. You created an index on a materialized view but notice queries are still slow. What is the most likely cause?

medium

A. Indexes do not work on materialized views in PostgreSQL

B. The materialized view has not been refreshed after data changes

C. The index was created on a non-existent column

D. Materialized views cannot have indexes on date columns

Solution

1. Step 1: Understand materialized view refresh

   Materialized views store data physically but need manual refresh to update data and indexes.

2. Step 2: Connect refresh to query speed

   If not refreshed, the view and its index are outdated, causing slow or incorrect query results.

3. Final Answer:

   The materialized view has not been refreshed after data changes -> Option B

4. Quick Check:

   Refresh needed for updated data and index [OK]

Hint: Always refresh materialized views after data changes [OK]

Common Mistakes:

• Believing indexes don't work on materialized views
• Creating index on wrong column silently
• Thinking date columns can't be indexed

5. You have a materialized view mv_sales refreshed daily. You want to speed up queries filtering by customer_id and sale_date. Which is the best indexing strategy?

hard

A. Do not create any index; rely on sequential scan

B. Create separate indexes on customer_id and sale_date individually

C. Create an index only on sale_date because it is a date column

D. Create a single composite index on (customer_id, sale_date)

Solution

1. Step 1: Understand composite vs single indexes

   A composite index on multiple columns speeds up queries filtering by both columns together.

2. Step 2: Apply to query filtering by customer_id and sale_date

   Since queries filter on both columns, a composite index is more efficient than separate indexes.

3. Final Answer:

   Create a single composite index on (customer_id, sale_date) -> Option D

4. Quick Check:

   Composite index = faster multi-column filters [OK]

Hint: Use composite index for multi-column filtering queries [OK]

Common Mistakes:

• Creating separate indexes causing less efficient queries
• Indexing only one column when filtering by two
• Skipping indexes and relying on slow scans