Reading EXPLAIN ANALYZE

Plans are trees, read bottom-up

EXPLAIN shows the planner's chosen plan; EXPLAIN ANALYZE runs the query and adds actual rows and loops (plus timings unless disabled). Read the tree from the innermost nodes upwards: scans feed joins, which feed sorts and aggregates. Look for big differences between estimated and actual rows, sequential scans on large tables with selective filters, and "Rows Removed by Filter". Use BUFFERS to see I/O.

An aggregated join, analysed, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Run with COSTS OFF, TIMING OFF and SUMMARY OFF so the output is stable. Orders are filtered to 5 paid rows (2 removed), hashed, joined to 4 customers, sorted and grouped into 3 rows. Memory figures depend on the build and data.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.name, count(*)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.name;

Output:

                              QUERY PLAN                              
----------------------------------------------------------------------
 GroupAggregate (actual rows=3 loops=1)
   Group Key: c.name
   ->  Sort (actual rows=5 loops=1)
         Sort Key: c.name
         Sort Method: quicksort  Memory: 25kB
         ->  Hash Join (actual rows=5 loops=1)
               Hash Cond: (c.id = o.customer_id)
               ->  Seq Scan on customers c (actual rows=4 loops=1)
               ->  Hash (actual rows=5 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on orders o (actual rows=5 loops=1)
                           Filter: (status = 'paid'::text)
                           Rows Removed by Filter: 2
(13 rows)

Never EXPLAIN ANALYZE a destructive query casually

It really executes the statement; wrap UPDATE or DELETE in BEGIN ... ROLLBACK.

Quick check: What does EXPLAIN ANALYZE add compared with EXPLAIN?

• It creates missing indexes
• It rewrites the query
• It executes the query and reports actual row counts
• It only formats the SQL