Slow queries are one of the most common production performance issues. Learn index strategies, query plan analysis, and rewriting techniques that deliver real speedups on large datasets.

# SQL Query Optimization: Techniques That Actually Work A query that runs fine on 10,000 rows can grind to a halt on 10 million. Here's a systematic approach to making slow SQL fast. ## Step 1: Read the Query Plan ```sql EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; ``` Look for **Seq Scan** (full table scan)—often a signal that an index is missing or not being used. ## Step 2: Add the Right Indexes ```sql -- Composite index for common filter + sort CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC); ``` Rules of thumb: - Index columns that appear in `WHERE`, `JOIN ON`, and `ORDER BY`. - Put high-cardinality columns first in composite indexes. - Don't over-index—writes pay for every index. ## Step 3: Avoid SELECT * Only select what you need. This reduces I/O and allows index-only scans. ## Step 4: Use CTEs and Subquery Sparingly In PostgreSQL, CTEs are optimization fences (pre-15). Inline them when the optimizer needs freedom. ## Step 5: Paginate with Keyset Pagination ```sql -- Avoid OFFSET on large tables SELECT * FROM orders WHERE created_at < :last_seen_cursor ORDER BY created_at DESC LIMIT 20; ``` ## Step 6: Batch Large Updates ```sql -- Instead of one giant UPDATE UPDATE orders SET status = 'archived' WHERE id IN (SELECT id FROM orders WHERE created_at < '2024-01-01' LIMIT 1000); -- repeat until done ``` ## Step 7: Check for N+1 Queries Application-level loops that fire one query per record kill performance. Use JOINs or `WHERE IN` to batch. These techniques consistently deliver 10–100x speedups. Always measure before and after with `EXPLAIN ANALYZE`.