Database

EXPLAIN Plan Analyser

Read a PostgreSQL plan with the per-loop row counts multiplied out, each node's own time separated from its children's, and the usual culprits flagged.

Plan

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=12844.32..12844.44 rows=50 width=52) (actual time=284.116..284.129 rows=50 loops=1)
   ->  Sort  (cost=12844.32..12856.19 rows=4748 width=52) (actual time=284.114..284.122 rows=50 loops=1)
         Sort Key: (sum(o.total_cents)) DESC
         Sort Method: top-N heapsort  Memory: 32kB
         ->  HashAggregate  (cost=12604.21..12651.69 rows=4748 width=52) (actual time=281.443..283.201 rows=4612 loops=1)
               Group Key: c.id, c.email, c.tier
               Batches: 1  Memory Usage: 1553kB
               ->  Hash Join  (cost=1284.55..12461.77 rows=18992 width=44) (actual time=18.204..262.914 rows=19104 loops=1)
                     Hash Cond: (o.customer_id = c.id)
                     ->  Seq Scan on orders o  (cost=0.00..11021.00 rows=18992 width=16) (actual time=0.031..221.456 rows=19104 loops=1)
                           Filter: ((created_at >= (now() - '30 days'::interval)) AND (status = 'completed'::text))
                           Rows Removed by Filter: 480896
                     ->  Hash  (cost=1225.00..1225.00 rows=4764 width=36) (actual time=18.098..18.099 rows=4764 loops=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 379kB
                           ->  Seq Scan on customers c  (cost=0.00..1225.00 rows=4764 width=36) (actual time=0.012..16.442 rows=4764 loops=1)
                                 Filter: (tier = ANY ('{gold,platinum}'::text[]))
                                 Rows Removed by Filter: 45236
 Planning Time: 0.412 ms
 Execution Time: 284.203 ms

How to produce a plan worth reading

EXPLAIN (ANALYZE, BUFFERS) <your query>;

ANALYZE runs the query, which is the only way to see an estimate that was wrong or a sort that spilled. It also means a write statement will actually write — wrap one in a transaction and roll back.

Summary

Nodes: 7
Mode: EXPLAIN ANALYZE
Planning: 0.412 ms
Execution: 284.2 ms
Costliest node: Seq Scan78%

What to look at

InfoSeq Scan on orders o accounts for 78% of the execution time on its own. A child's time is included in its parent's, so this is the figure to look at rather than the largest number in the plan.
HighSeq Scan on orders o read 500.0k rows and discarded 480.9k of them. An index on the filtered column would let PostgreSQL skip that work: Filter: ((created_at >= (now() - '30 days'::interval)) AND (status = 'completed'::text)).
HighSeq Scan on customers c read 50.0k rows and discarded 45.2k of them. An index on the filtered column would let PostgreSQL skip that work: Filter: (tier = ANY ('{gold,platinum}'::text[])).

Nodes

rows and time multiplied out by loops

Node	Est. rows	Actual	Loops	Own time	Share
Limit	50	50	1	0.007 ms	0%
Sort	4.7k	50↓95×	1	0.921 ms	0%
HashAggregate	4.7k	4.6k	1	20.3 ms	7%
Hash Join	19.0k	19.1k	1	23.4 ms	8%
Seq Scan on orders o	19.0k	19.1k	1	221.5 ms	78%
Hash	4.8k	4.8k	1	1.7 ms	1%
Seq Scan on customers c	4.8k	4.8k	1	16.4 ms	6%

Node detail

Sort

Sort Key: (sum(o.total_cents)) DESC
Sort Method: top-N heapsort Memory: 32kB

HashAggregate

Group Key: c.id, c.email, c.tier
Batches: 1 Memory Usage: 1553kB

Hash Join

Hash Cond: (o.customer_id = c.id)

Seq Scan on orders o

Filter: ((created_at >= (now() - '30 days'::interval)) AND (status = 'completed'::text))
Rows Removed by Filter: 480896

Hash

Buckets: 8192 Batches: 1 Memory Usage: 379kB

Seq Scan on customers c

Filter: (tier = ANY ('{gold,platinum}'::text[]))
Rows Removed by Filter: 45236

“Own time” is this node's time with its children's subtracted, which is what identifies the expensive step — a parent's reported time always includes everything beneath it.