You want to speed up queries on a products table filtering only active products with price < 100. Which partial index is best to create?

hard📝 Application Q15 of 15

PostgreSQL - Indexing Strategies

You want to speed up queries on a products table filtering only active products with price < 100. Which partial index is best to create?

ACREATE INDEX idx_active_price ON products (price);

BCREATE INDEX idx_active_price ON products (price) WHERE active = true AND price < 100;

CCREATE INDEX idx_active_price ON products (price) WHERE active = true OR price < 100;

DCREATE INDEX idx_active_price ON products WHERE active = true AND price < 100;

Step-by-Step Solution

Solution:

Step 1: Define index condition matching query filters
The query filters active = true AND price < 100, so index WHERE must match both.
Step 2: Check syntax and columns
Index must specify columns (price) and use AND in WHERE clause for correct filtering.
Final Answer:
CREATE INDEX idx_active_price ON products (price) WHERE active = true AND price < 100; -> Option B
Quick Check:
Partial index WHERE matches query filters exactly [OK]

Quick Trick: Use AND in WHERE to match all query conditions [OK]

Common Mistakes:

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free