You want to speed up this query: SELECT product_id, price, stock FROM products WHERE product_id = 123; by creating a covering index. Which index is best?
hard📝 Application Q15 of 15
PostgreSQL - Indexing Strategies
You want to speed up this query: SELECT product_id, price, stock FROM products WHERE product_id = 123; by creating a covering index. Which index is best?
ACREATE INDEX idx_product ON products(product_id) INCLUDE (price, stock);
BCREATE INDEX idx_product ON products(price, stock) INCLUDE (product_id);
CCREATE INDEX idx_product ON products(product_id, price, stock);
DCREATE INDEX idx_product ON products(product_id);
Step-by-Step Solution
Solution:
Step 1: Identify the filtering and selected columns in the query
The query filters on product_id and selects price and stock.
Step 2: Choose an index that filters on product_id and includes price and stock
CREATE INDEX idx_product ON products(product_id) INCLUDE (price, stock); indexes product_id and includes price and stock, covering the query efficiently.
Step 3: Compare other options
CREATE INDEX idx_product ON products(price, stock) INCLUDE (product_id); indexes price and stock, not filtering column; CREATE INDEX idx_product ON products(product_id, price, stock); indexes all columns but includes unnecessary columns in index key; CREATE INDEX idx_product ON products(product_id); lacks included columns, so table access needed.
Final Answer:
CREATE INDEX idx_product ON products(product_id) INCLUDE (price, stock); -> Option A
Quick Check:
Filter column indexed, others included [OK]
Quick Trick:Index filter column, INCLUDE others for covering [OK]
Common Mistakes:
Including filter columns instead of indexing them
Indexing all columns as keys unnecessarily
Not including selected columns causing table access
Master "Indexing Strategies" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
