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📝 optimization Q15 of 15

PostgreSQL - Views and Materialized Views

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?

ADo not create any index; rely on sequential scan

BCreate separate indexes on customer_id and sale_date individually

CCreate an index only on sale_date because it is a date column

DCreate a single composite index on (customer_id, sale_date)

Step-by-Step Solution

Solution:

Step 1: Understand composite vs single indexes
A composite index on multiple columns speeds up queries filtering by both columns together.
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.
Final Answer:
Create a single composite index on (customer_id, sale_date) -> Option D
Quick Check:
Composite index = faster multi-column filters [OK]

Quick Trick: 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

Master "Views and Materialized Views" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

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?

Step 1: Understand composite vs single indexes

Step 2: Apply to query filtering by customer_id and sale_date

Final Answer:

Quick Check:

Want More Practice?