Practice & Assessment
Test your understanding of Indexes and Query Performance
Multiple Choice Questions
5A composite index exists on (department_id, salary). Which query will use this index efficiently?
EXPLAIN ANALYZE shows estimated rows=5000 but actual rows=3 for a query. What is the most likely cause?
Which index type is best suited for querying keys inside a JSONB column?
What is the difference between RANK() and DENSE_RANK() when two employees have the same salary?
You have a BRIN index on a created_at column. When is it most effective?
Coding Challenges
1Index Audit and Optimization
Given a table events (id BIGINT PK, user_id INT, event_type TEXT, payload JSONB, created_at TIMESTAMPTZ) with 1 million rows, no indexes beyond PK. Write CREATE INDEX statements for these three query patterns: (1) WHERE user_id = ? AND event_type = ? ORDER BY created_at DESC, (2) WHERE payload @> '{"action": "purchase"}', (3) WHERE created_at > now() - interval '7 days' on an append-only table. Use appropriate index types for each. Then write one EXPLAIN ANALYZE query for pattern 1 and annotate in comments what you expect to see. Time estimate: 25 minutes.
Mini Project
Query Performance Dashboard
You have a reporting database with three tables: users (1M rows), orders (5M rows), and order_items (20M rows). Tasks: (1) Write five analytical queries: top customers by lifetime value, monthly revenue by product category, users with no orders in 90 days, rolling 7-day order count, rank products by revenue within category. (2) Run EXPLAIN ANALYZE on each query and record the execution plan type (Seq Scan, Index Scan, etc.) and actual execution time in comments. (3) Add appropriate indexes to speed up at least three of the five queries. (4) Re-run EXPLAIN ANALYZE and document the improvement. (5) Use pg_stat_statements query to find any remaining slow queries and propose a fix. All work should be in a single SQL file with section comments.