# Common plan nodes

## Scans

**Seq Scan / Table Scan** - reads every row. Correct for a small table or a query that
genuinely wants most rows. A problem when the table is large and the predicate is
selective.

**Index Scan** - walks the index, then fetches each matching row from the table. Good
when few rows match. Each fetch is a random read, so it loses to a sequential scan past
roughly 5 to 10% of the table.

**Index Only Scan** - answers entirely from the index, no table fetch. The fastest
shape. Requires every referenced column to be in the index.

**Bitmap Heap Scan** - collects matching locations from the index, sorts them, then
reads the table in physical order. The planner's compromise between the two above.
Normal for medium selectivity.

## Joins

**Nested Loop** - for each outer row, scan the inner side. Excellent when the outer
side is genuinely tiny. Catastrophic when the estimate was wrong. Always check
`loops`.

**Hash Join** - builds a hash table from one side, probes with the other. The usual
choice for large unsorted joins. Watch for the build side spilling to disk.

**Merge Join** - both sides sorted, then merged. Good when the inputs are already
sorted, for example by an index. Watch for a sort node feeding it.

## Blocking nodes

**Sort** - look for the method. In-memory quicksort is fine. "external merge Disk"
means it spilled, and is worth fixing.

**Aggregate / HashAggregate / GroupAggregate** - Hash is usually faster but needs
memory. Group needs sorted input.

**Materialize** - caches a subplan's output for repeated reads. Often paired with a
nested loop.

## Reading tips

- `rows=N` in the estimate is per loop, not total.
- `actual time=x..y` is start time and end time, per loop.
- The `Filter` line under a scan shows a predicate applied after reading. `Rows Removed
  by Filter` on that line is wasted work you can often move into an index.
- Buffer counts distinguish a cold cache from a genuinely expensive plan. A slow first
  run and a fast second run is a caching story, not a planning one.