Optimizing Query Performance
1. Using EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;2. Using EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- actually runs queryWarning: ANALYZE executes the statement. Use BEGIN; ... ROLLBACK; for destructive queries.
3. Using EXPLAIN (BUFFERS)
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) SELECT ... ;| Output | Meaning |
|---|---|
| shared hit | Page in buffer cache |
| shared read | Page read from disk |
| temp written | Spill to disk (sort/hash) |
4. Using EXPLAIN (VERBOSE)
EXPLAIN (VERBOSE) SELECT ... ; -- shows output columns, schema-qualified names5. Understanding Scan Types
| Scan | When Used |
|---|---|
| Seq Scan | Small / large-fraction read |
| Index Scan | Selective predicate + ordering |
| Index Only Scan | All columns in index (visibility map OK) |
| Bitmap Heap Scan | Many index entries → unique heap visits |
6. Understanding Join Methods
| Method | Best For |
|---|---|
| Nested Loop | Small outer + indexed inner |
| Hash Join | Equality, large inputs fit in work_mem |
| Merge Join | Pre-sorted inputs |
7. Understanding Node Types
-- Common: Aggregate, Sort, Limit, HashAggregate, Materialize,
-- Append, Gather, Gather Merge, Parallel Seq Scan, WindowAgg, Memoize (PG 14+)8. Analyzing Costs
| Number | Meaning |
|---|---|
| cost=startup..total | Planner units |
| rows | Estimated rows |
| actual time | Real ms (with ANALYZE) |
| loops | Times node ran |
9. Identifying Bottlenecks
Note: Look for: large discrepancy between estimated and actual rows, Seq Scan on big tables with selective filter, Sort spilling to disk, Rows Removed by Filter very high.
10. Using Index-Only Scans
CREATE INDEX orders_cover ON orders(customer_id) INCLUDE (status);
VACUUM orders; -- keep visibility map fresh
EXPLAIN SELECT customer_id, status FROM orders WHERE customer_id = 42;11. Understanding Bitmap Heap Scans
-- Bitmap Index Scan builds bitmap of TIDs
-- Bitmap Heap Scan then fetches heap pages in physical order (less seek)12. Using Parallel Query Execution
SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT count(*) FROM big_fact; -- Gather → Parallel Seq Scan13. Analyzing Join Order
SET join_collapse_limit = 8; -- default; raise for many tables
SET from_collapse_limit = 8;14. Identifying Missing Indexes
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND seq_tup_read / seq_scan > 10000
ORDER BY seq_tup_read DESC;15. Using EXPLAIN (FORMAT JSON, FORMAT YAML)
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ; -- machine readable
EXPLAIN (FORMAT YAML) SELECT ... ;