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
EXPLAIN = Query Debugging¶
cost vs actual reveals problems.
postgresqlexplainquery optimization