query-optimization

Capture a baseline first: p50/p95 latency, rows examined, and rows returned. Then get the execution plan with EXPLAIN (ANALYZE, BUFFERS) on PostgreSQL, EXPLAIN ANALYZE FORMAT=JSON on MySQL 8, or SET STATISTICS IO, TIME ON in SQL Server, and look for common causes like sequential scans or bad row estimates.

Follow the ESR rule: place equality columns first, then range columns, then sort columns. For read-heavy queries, add INCLUDE columns to create a covering index that avoids heap lookups, and justify every index against its write cost.

Common causes include applying a function to the indexed column in the WHERE clause, stale table statistics causing row estimate errors above 10x, or a predicate that is not selective enough. Run ANALYZE to refresh statistics or use an expression index for computed predicates.

No. N+1 problems come from the ORM issuing one query per related record, so they must be fixed in the ORM with eager loading, not with an index. Indexes only help when a single query itself is slow.

Use a partial index for highly selective filters on skewed data, such as WHERE status = 'pending' when only a small fraction of rows match. It keeps the index small and cheap to maintain while still serving the hot query path.