SQL Optimization

Overview

Analyzing and tuning SQL for performance: reading execution plans, index design, query rewriting, and PostgreSQL-specific behavior. Most notes assume PostgreSQL. The mechanism-level material — reading EXPLAIN, composite-index column order, partial-index limits, statistics, concurrency — is in Expert Practices below; this section is the workflow and the anti-pattern catalogue.

Workflow

1. Find the slow query (logs, pg_stat_statements by total time, not just per-call).
2. Explain it: EXPLAIN (ANALYZE, BUFFERS, SETTINGS). Read plans by estimated-vs-actual row divergence (bad stats → wrong join/scan choice), scan type, join algorithm, and Rows Removed by Filter. See Expert Practices → Reading EXPLAIN.
3. Fix in priority order: refresh/extend statistics → add/reshape an index → rewrite the query → denormalize/derive → tune config. Change one thing at a time.
4. Validate: re-EXPLAIN on production-like data, confirm the target node changed (Sort gone / Index Scan chosen / Heap Fetches low), verify correctness, monitor post-deploy.

Best Practices