EXPLAIN ANALYZE is the most important optimization tool.
Usage¶
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=123;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
Scans¶
- Seq Scan — entire table
- Index Scan — index + fetch
- Index Only Scan — index only
- Bitmap — for multiple rows
Joins¶
- Nested Loop — small tables
- Hash Join — large without index
- Merge Join — sorted data
Optimization¶
- Add missing index
- ANALYZE for statistics
- Subquery → JOIN
- Increase work_mem for sorts
Reading EXPLAIN Output¶
When analyzing EXPLAIN ANALYZE output, focus on the difference between estimated cost and actual time. A large discrepancy signals outdated statistics — run ANALYZE on the relevant table. A Seq Scan on a large table with a WHERE condition usually means a missing index.
Monitor Buffers: shared hit vs shared read — a high read ratio means data is not cached and is being read from disk. Increasing shared_buffers may help. For sort operations, check whether they are performed in memory or on disk (Sort Method: external merge). In that case, increase work_mem. The auto_explain extension with the log_min_duration parameter automatically logs slow queries with their plans, which is essential for production optimization.
EXPLAIN = Query Debugging¶
cost vs actual reveals problems.