sql-query-optimization

SQL Query Optimization

Essential patterns for diagnosing and fixing slow SQL queries across relational databases.

Core Principles

1. Measure before optimizing - Always read the EXPLAIN plan before rewriting a query
2. Indexes solve most problems - 90% of slow queries are missing an appropriate index
3. Keep predicates sargable - Never wrap indexed columns in functions
4. Reduce the working set early - Filter and limit rows before joins and aggregations
5. Statistics drive the optimizer - Stale statistics produce bad plans regardless of indexes

Reading EXPLAIN Plans

-- PostgreSQL: EXPLAIN ANALYZE shows actual execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01'
  AND o.status = 'shipped';

-- Key output to examine:
-- Seq Scan on orders  (cost=0.00..18450.00 rows=5200 width=48) (actual time=0.03..112.5 rows=5180 loops=1)
--   Filter: ((order_date >= '2025-01-01') AND (status = 'shipped'))
--   Rows Removed by Filter: 994820
--   Buffers: shared hit=8450
-- Planning Time: 0.15 ms
-- Execution Time: 115.8 ms

See explain-plan-reading.md for:

• EXPLAIN vs EXPLAIN ANALYZE differences and when to use each
• Understanding cost estimates, row counts, and width
• Identifying sequential scans, index scans, and bitmap scans
• Reading join algorithm nodes (Nested Loop, Hash Join, Merge Join)

Index Selection and Design

-- Composite index: column order matters for range queries
-- Put equality columns first, range columns last
CREATE INDEX idx_orders_status_date
ON orders (status, order_date);

-- Covering index: includes all columns the query needs
-- Avoids heap table lookups entirely
CREATE INDEX idx_orders_covering
ON orders (status, order_date)
INCLUDE (customer_id, total_amount);

-- Partial index: smaller index, faster lookups
CREATE INDEX idx_orders_pending
ON orders (order_date)
WHERE status = 'pending';