SQL Optimization Checklist

Step 1: Diagnose before touching anything

• Run EXPLAIN ANALYZE on the actual slow query — never optimize based on a guess about what's wrong.
• Identify the scan type at each leaf node — a Seq Scan on a large, selective query is the most common red flag. See Index Scan vs Table Scan.
• Compare estimated vs actual row counts — a big gap points at stale statistics, not a schema problem. Full method in EXPLAIN Query Plan – Beginner to Advanced.
• Confirm it isn't an N+1 pattern in application code before optimizing the SQL itself — see Why SQL Queries Are Slow.

Step 2: Fix the query itself

• Make every predicate sargable — no functions wrapped around indexed columns (YEAR(order_date) → a date range).
• Replace SELECT * with the exact columns needed — reduces bytes moved and gives covering indexes a chance to work.
• Avoid implicit type casts in WHERE clauses — comparing a text column to a number, for example, can silently block index use.
• Push filters as early as possible — filter before joining wherever the logic allows, rather than joining everything and filtering last.

Step 3: Review joins and subqueries

• Check for accidental row fan-out — a join that unintentionally multiplies rows before a filter or aggregate is applied.
• Prefer a JOIN over a correlated subquery when the same row-by-row logic can be expressed as a single set operation.
• Verify every join has an equality condition backed by an index on at least one side — an unindexed join key forces a full scan of one input.

Step 4: Review indexing

• Add indexes based on the rewritten query, not the original — a sargable rewrite often needs a different index than the broken version did. See Indexes Explained With Real Examples.
• Order composite index columns by equality filters first, then range filters, then sort columns.
• Check for redundant or unused indexes before adding a new one — you may already have one that covers this. See When NOT to Use Indexes.
• Consider a covering index if the query is hot enough to justify the extra storage and write cost.

Step 5: Check statistics and maintenance

• Refresh table statistics (ANALYZE / UPDATE STATISTICS) if estimated and actual row counts diverged in step 1.
• Check for table or index bloat after heavy delete/update churn, which can silently degrade both scan and index performance.
• Confirm autovacuum or the equivalent background maintenance is actually keeping up with write volume.

Step 6: Verify and monitor

• Re-run EXPLAIN ANALYZE after every change to confirm the plan actually improved, not just the query text.
• Test against production-sized data — a query that's fast on a 500-row dev database can behave completely differently at 5 million rows.
• Track execution time over time in production, since data volume and distribution shift, and a good plan today may not stay good.

Before/after: the checklist applied end to end

Same query as Why SQL Queries Are Slow — this time showing the net result of walking every step above in order: sargable rewrite, column trim, and a composite index sized to the rewritten query.

SELECT * FROM orders
WHERE YEAR(order_date) = 2026
  AND status = 'pending'
ORDER BY order_date DESC;

Sort
  -> Seq Scan on orders (rows=2401880)

SELECT id, total, order_date FROM orders
WHERE status = 'pending'
  AND order_date >= '2026-01-01'
ORDER BY order_date DESC;

Index Scan using idx_orders_status_date
  (rows=1842)

~353x faster no single trick — sargable rewrite, trimmed columns, and one right-sized index, applied in order