Recap — The Query Performance Checklist

# The Query Performance Checklist When a query is slow, run through this checklist before asking engineering. ## 1) Missing index on the filter or join column ```sql SELECT * FROM orders WHERE customer_id = 12345; ``` If there's no index on `customer_id`, Postgres reads the entire table. For 10M rows, that's seconds. With an index, microseconds. Check: `\d orders` (psql) or `SHOW INDEX FROM orders;` (MySQL). If `customer_id` isn't indexed and it's a common filter, you've found the bug. Fix: `CREATE INDEX orders_customer_id_idx ON orders(customer_id);` ## 2) Function applied to indexed column in WHERE ```sql SELECT * FROM orders WHERE DATE(created_at) = '2026-05-25'; ``` `DATE(created_at)` cannot use the index on `created_at` — the function blocks it. The query falls back to Seq Scan. Rewrite as a range: ```sql SELECT * FROM orders WHERE created_at >= '2026-05-25' AND created_at NOW()` → rearrange to `WHERE order_date > NOW() - INTERVAL '1 day'`. ## 3) SELECT * when you only need 3 columns ```sql SELECT * FROM orders WHERE customer_id = 12345; ``` If the table has 40 columns and you need 3, you're moving 40 columns' worth of data over the wire. Worse, you can't use a *covering index* (an index that contains all needed columns and avoids a heap lookup). ```sql SELECT order_id, total_amount, status FROM orders WHERE customer_id = 12345; ``` If you have `CREATE INDEX orders_customer_idx ON orders(customer_id) INCLUDE (order_id, total_amount, status)`, this never touches the table — pure index read. ## 4) Joining before filtering ```sql SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.created_at > '2026-01-01'; ``` In many query plans, Postgres correctly applies the filter early. But for complex queries with many joins, it sometimes joins everything first, then filters. EXPLAIN tells you which. If the planner is choosing badly, force early filtering with a CTE or subquery: ```sql WITH recent_orders AS ( SELECT * FROM orders WHERE created_at > '2026-01-01' ) SELECT c.name, o.total FROM customers c JOIN recent_orders o ON o.customer_id = c.id; ``` This is mostly a problem in older databases (MySQL pre-8) or with complicated views. PostgreSQL's planner usually does the right thing. ## 5) Stale statistics After a big insert/update, the table's row counts and value distributions change. The planner's estimates become stale, and it picks the wrong join algorithm. Fix: `ANALYZE table_name;` (PostgreSQL) or `ANALYZE TABLE table_name;` (MySQL). Run after big data changes; usually autovacuum handles it. ## Bonus: ORDER BY + LIMIT without an index ```sql SELECT * FROM orders ORDER BY created_at DESC LIMIT 10; ``` Without an index on `created_at`, Postgres reads the whole table, sorts it, takes the top 10. With an index, it reads exactly 10 rows in order. ```sql CREATE INDEX orders_created_at_idx ON orders(created_at DESC); ``` This is the highest-leverage "1 line" fix in BI workloads. Top-N queries are everywhere. ## The 60-second triage 1. Is there an index on the WHERE column? (no → likely fix) 2. Is a function applied to the WHERE column? (yes → rewrite without) 3. Is SELECT * being used unnecessarily? (yes → name the columns) 4. Does EXPLAIN show wrong row estimates? (yes → ANALYZE) 5. Is there an ORDER BY + LIMIT without a matching index? (yes → add index) If none of these apply, the query might genuinely be complex. Then it's time to think harder — or ask engineering. ## Takeaway 90% of slow analyst queries fall into one of these five buckets. Develop the reflex: see a slow query, run through the list, fix the most likely cause. You'll outpace engineers who haven't built this muscle.