Nested Loop is a simple join algorithm. The first child node is run once, then for each row it produces matching rows are looked up in the second child node.

For example, the child nodes might be a Seq Scan on a tiny table and an Index Only Scan on a larger table. In this case, the Seq Scan would run once, and each row it produced would cause a loop of the Index Only Scan.

The Nested Loop algorithm is cheap to start, but gets more expensive as the number of loops increases. As such, it is often chosen when Postgres estimates that one of the relations is small. If it is chosen in spite of this, then it might be the only option available (due to the join condition).

Nested Loop fields

• Parent Relationship

• Parallel Aware

• Join Type

• Startup Cost

• Total Cost

• Plan Rows

• Plan Width

• Actual Startup Time

• Actual Total Time

• Actual Rows

• Actual Loops

• Output

• Inner Unique

• Join Filter

• Rows Removed by Join Filter

• Filter

• Rows Removed by Filter

• Shared Hit Blocks

• Shared Read Blocks

• Shared Dirtied Blocks

• Shared Written Blocks

• Local Hit Blocks

• Local Read Blocks

• Local Dirtied Blocks

• Local Written Blocks

• Temp Read Blocks

• Temp Written Blocks

• Shared I/O Read Time

• Shared I/O Write Time

• Local I/O Read Time

• Local I/O Write Time

• Temp I/O Read Time

• Temp I/O Write Time

Related operations

• Hash Join

• Merge Join

• Nested Loop Anti Join

• Nested Loop Semi Join

• Memoize

Related official docs

• Planner

• Query Planning

• Using EXPLAIN

Further reading

• Rules of thumb – Stack Overflow

• Join strategies – Cybertec

• Row count estimates – pgMustard

• Exploding runtime – Cybertec