SQL

Query optimization basics

• Start with the access pattern: filters, joins, grouping, sorting, and result size.
• Index columns used for selective WHERE, join keys, and stable ORDER BY clauses.
• Prefer narrow projections over SELECT *; return only the columns callers need.
• Avoid accidental row multiplication in joins; check cardinality before adding DISTINCT.
• Watch for N+1 query loops at application boundaries.

EXPLAIN and EXPLAIN ANALYZE

• Use EXPLAIN to inspect the planned access path before changing indexes or query shape.
• Use EXPLAIN ANALYZE when you need actual timing and row counts; run it against safe data and statements.
• Compare estimated vs actual rows. Large gaps often mean stale statistics, skewed data, or missing predicates.
• Optimize the highest-cost operation first, but confirm the full query got faster.