DB Query Optimization -- EXPLAIN Plan, Index Tuning, N+1 Detection

Reference skill for database query optimization used by the backend-optimizer agent.

EXPLAIN ANALYZE Interpretation (PostgreSQL)

Key cost indicators:

• Seq Scan: full table scan; check if index should be added
• Index Scan: good for selective queries (< 5% of rows)
• Index Only Scan: best; reads only from index
• Hash Join: for large join sets; use when tables lack sorted indexes
• Nested Loop: good for small result sets with indexed foreign keys
• Sort: check if sort can be eliminated with index (ORDER BY matches index)

Red flags in EXPLAIN output:

• rows=X (estimated) vs actual rows=Y where Y >> X: stale statistics; run ANALYZE
• cost=0..999999: extremely high cost; full table scan on large table
• Filter: (condition after Seq Scan): condition not using index
• Buffers: hit=0 read=10000: all reads from disk, no cache (buffer pool cold)