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, same intent as the eager-load call above
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
Mattakushi432