[Solved] You often run this query:SELECT * FROM products WHERE category = 'books' AND price < 20;Which indexing strategy will most improve query speed without slowing inserts too much? | PostgreSQL

PostgreSQL - Indexing Strategies

You have a table products with columns id, category, and price. You often run this query:

SELECT * FROM products WHERE category = 'books' AND price < 20;

Which indexing strategy will most improve query speed without slowing inserts too much?

ACreate no indexes to keep inserts fast.

BCreate separate indexes on category and price.

CCreate a composite index on (category, price).

DCreate an index only on price.

Step-by-Step Solution

Solution:

Step 1: Analyze query filter conditions
The query filters on both category and price together, so a composite index on both columns helps the database find matching rows efficiently.
Step 2: Compare indexing options
Separate indexes may be less efficient because PostgreSQL might not combine them well; no index slows queries; indexing only price misses category filtering.
Final Answer:
Create a composite index on (category, price). -> Option C
Quick Check:
Composite index matches multi-column filters [OK]

Quick Trick: Use composite index for multi-column WHERE filters [OK]

Common Mistakes:

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

You have a table products with columns id, category, and price. You often run this query: