Pipelined vs blocking node
Also called: streaming vs materializing node, blocking operator
A pipelined node can hand up a result row as soon as it has one, a sequential scan, an index scan, a nested loop. A blocking node must read all of its input before it can return even the first row, a sort, a hash build, an aggregate over unsorted data.
What this means
Some plan steps stream: a scan or a nested loop hands a row upward the moment it has one. Others block: a sort or a hash build has to consume all of their input before returning even the first row. Knowing which is which tells you where a query pauses before results start flowing.
Why it matters operationally
This split is the run-time reason behind startup cost. A blocking node makes a query "freeze" until its input is fully consumed, and a LIMIT above it saves nothing. Replacing a Sort with an index that supplies order, or keeping the blocking node inside work_mem, is how you make a stalling query feel instant.