Query Optimisation

Writing correct SQL is only the first step. Writing fast SQL requires understanding how PostgreSQL executes your queries. This lesson covers execution plans, scan types, join algorithms, statistics, vacuuming, and common anti-patterns.

---

Reading Execution Plans

Every query goes through the planner/optimiser before execution. Use EXPLAIN ANALYZE to see the actual execution plan:

EXPLAIN ANALYZE
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 500;

Anatomy of a Plan Node

Hash Join  (cost=50.00..350.00 rows=200 width=36) (actual time=1.2..5.8 rows=185 loops=1)
  Hash Cond: (o.customer_id = c.id)
  -> Seq Scan on orders o  (cost=0.00..250.00 rows=200 width=20) (actual time=0.01..3.2 rows=200 loops=1)
       Filter: (total > 500)
       Rows Removed by Filter: 9800
  -> Hash  (cost=30.00..30.00 rows=1000 width=20) (actual time=0.5..0.5 rows=1000 loops=1)
       -> Seq Scan on customers c  (cost=0.00..30.00 rows=1000 width=20)