Query optimization

Query Optimization Patterns

EXPLAIN ANALYZE

-- Full execution stats (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

-- Key things to look for:
-- "Seq Scan" on large table → needs index
-- "Nested Loop" with many rows → consider Hash Join
-- High "Buffers: shared hit/read" ratio → cache miss
-- "rows=1000" estimate vs "actual rows=50000" → stale statistics
-- → Run: ANALYZE table_name;

-- MySQL
EXPLAIN FORMAT=JSON SELECT ...;
-- Look for: type (ALL=bad, ref/eq_ref/const=good), key used, rows estimate

N+1 Detection and Fix

# BAD — N+1: 1 query for orders + N queries for each user
orders = Order.query.all()
for o in orders:
    print(o.user.name)  # triggers SELECT each time

# GOOD — eager load with JOIN
orders = Order.query.options(joinedload(Order.user)).all()

# GOOD — subquery load (avoids cartesian product on *-many)
posts = Post.query.options(subqueryload(Post.comments)).all()

# GOOD — explicit JOIN in SQL
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending';

Index Selection Strategy

-- Composite index: equality conditions first, then range/sort
-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY amount DESC
CREATE INDEX idx ON orders (status, created_at, amount DESC);

-- Index-only scan (covering index) — avoid heap fetch
CREATE INDEX idx_cover ON orders (user_id, status) INCLUDE (amount, created_at);

-- Check if index is being used
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes WHERE tablename = 'orders' ORDER BY idx_scan;

-- Unused indexes (drop them — they slow down writes)
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Pagination Patterns

-- BAD: OFFSET is slow on large tables (scans N rows to skip)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- GOOD: Keyset/cursor pagination (O(log n) regardless of depth)
-- First page
SELECT * FROM orders WHERE status = 'active' ORDER BY created_at DESC, id DESC LIMIT 20;
-- Next page (use last row's values)
SELECT * FROM orders
WHERE status = 'active'
  AND (created_at, id) < ('2024-01-15 10:00:00', 4521)
ORDER BY created_at DESC, id DESC LIMIT 20;

-- Count approximation (fast alternative to COUNT(*))
SELECT reltuples::BIGINT AS approx_count
FROM pg_class WHERE relname = 'orders';

Query Rewriting

-- Replace correlated subquery with JOIN
-- BAD
SELECT * FROM orders WHERE user_id IN (
  SELECT id FROM users WHERE country = 'US'
);

-- GOOD
SELECT o.* FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'US';

-- EXISTS vs IN (EXISTS stops on first match)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

-- CTE optimization fence (PostgreSQL — inline vs fence)
-- Force materialization to prevent repeated evaluation:
WITH expensive AS MATERIALIZED (SELECT ...)
SELECT * FROM expensive WHERE ...;

Statistics and Vacuuming

-- Update statistics manually after bulk load
ANALYZE orders;

-- Increase statistics target for columns with bad estimates
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

-- Extended statistics for correlated columns
CREATE STATISTICS orders_status_user ON (status, user_id) FROM orders;
ANALYZE orders;

-- Check statistics age
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables ORDER BY last_analyze NULLS FIRST;

Checklist

• EXPLAIN ANALYZE before and after each optimization
• Indexes on all foreign keys and WHERE/ORDER BY columns
• Eager-load associations to eliminate N+1
• Cursor pagination for result sets > 10k rows
• Statistics current (ANALYZE after bulk operations)
• Query cache via Redis for expensive, infrequently-changing queries