Performance and EXPLAIN

Understand query plans, avoid common anti‑patterns, and tune with indexes and statistics.

EXPLAIN and ANALYZE

-- See the plan (estimated)
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 ORDER BY created_at DESC LIMIT 10;

-- Run and measure (actual)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1 ORDER BY created_at DESC LIMIT 10;

Read the plan top‑down: look for sequential scans vs index scans, join algorithms (nested loop / hash / merge), and row estimates vs actuals.

Statistics and ANALYZE

Keep table statistics fresh for good estimates.

-- Postgres
ANALYZE;          -- or autovacuum handles regularly

Skewed data may need higher sampling targets or extended stats (engine‑specific).

Index use

• Match predicates and ORDER BY to available indexes
• Prefer sargable predicates (search‑arg): avoid wrapping indexed columns in functions

-- bad: function prevents index use
WHERE LOWER(email) = 'a@example.com'
-- good: expression index (or store canonical form)
CREATE INDEX ON users ((lower(email)));

Anti‑patterns

• SELECT * (wider I/O; specify columns)
• Leading wildcard LIKE '%x' (prevents index use)
• Large OFFSET pagination (prefer keyset/seek)
• Functions on indexed columns without expression indexes
• Implicit casts causing type mismatches

Joins and data access

• Join on indexed keys; avoid cross join explosions
• Use appropriate join order; sometimes rewriting as EXISTS is faster
• Precompute aggregates with materialized views when queries are heavy and data changes slowly

Partitioning

Partition large tables by date/range/hash to prune partitions and keep indexes small.

Caching and connection settings

• Use query result caches at the app layer when appropriate
• Right‑size connection pools; too many concurrent queries can thrash caches

Summary

• Use EXPLAIN (ANALYZE) to inspect reality; align predicates with indexes
• Avoid anti‑patterns; consider partitioning and precomputation for scale