1
# Common plan nodes
2
3
## Scans
4
5
**Seq Scan / Table Scan** - reads every row. Correct for a small table or a query that
6
genuinely wants most rows. A problem when the table is large and the predicate is
7
selective.
8
9
**Index Scan** - walks the index, then fetches each matching row from the table. Good
10
when few rows match. Each fetch is a random read, so it loses to a sequential scan past
11
roughly 5 to 10% of the table.
12
13
**Index Only Scan** - answers entirely from the index, no table fetch. The fastest
14
shape. Requires every referenced column to be in the index.
15
16
**Bitmap Heap Scan** - collects matching locations from the index, sorts them, then
17
reads the table in physical order. The planner's compromise between the two above.
18
Normal for medium selectivity.
19
20
## Joins
21
22
**Nested Loop** - for each outer row, scan the inner side. Excellent when the outer
23
side is genuinely tiny. Catastrophic when the estimate was wrong. Always check
24
`loops`.
25
26
**Hash Join** - builds a hash table from one side, probes with the other. The usual
27
choice for large unsorted joins. Watch for the build side spilling to disk.
28
29
**Merge Join** - both sides sorted, then merged. Good when the inputs are already
30
sorted, for example by an index. Watch for a sort node feeding it.
31
32
## Blocking nodes
33
34
**Sort** - look for the method. In-memory quicksort is fine. "external merge Disk"
35
means it spilled, and is worth fixing.
36
37
**Aggregate / HashAggregate / GroupAggregate** - Hash is usually faster but needs
38
memory. Group needs sorted input.
39
40
**Materialize** - caches a subplan's output for repeated reads. Often paired with a
41
nested loop.
42
43
## Reading tips
44
45
- `rows=N` in the estimate is per loop, not total.
46
- `actual time=x..y` is start time and end time, per loop.
47
- The `Filter` line under a scan shows a predicate applied after reading. `Rows Removed
48
  by Filter` on that line is wasted work you can often move into an index.
49
- Buffer counts distinguish a cold cache from a genuinely expensive plan. A slow first
50
  run and a fast second run is a caching story, not a planning one.
51