Nested Loop

A Nested Loop takes each row from its outer input and runs the inner input once for it. The inner side is usually an Index Scan that looks up the matching rows. In the sample, the outer is the Seq Scan on orders, 206,880 rows, so the inner Index Scan using order_items_order_idx runs 206,880 times. The loop above takes the 620,640 items and looks up customers 620,640 times.

Why it matters

It is the best join when the outer side is small and the inner lookup is indexed: ten orders, ten lookups. It is the worst when the outer side is big, because the cost is outer rows times one lookup. The planner chose it here because it expected 612 web orders. 206,880 was 338× more, and 620,640 lookups on customers at a correlation of 0.01 is 620,640 random pages. Check the outer row count against its estimate. When that is right, the join choice usually is too.

What to do

1. Fix the estimate first. ANALYZE commerce.orders, and the planner switches to Hash Joins.
2. If the estimate is right and the loop still slow, make each lookup cheaper: an index covering the join column and the filter, so a loop reads one page.
3. SET enable_nestloop = off for one run shows whether the alternative is faster. Use it to learn, not as the fix.

In the Plan tab

The band into the inner node carries ↻ ×207K, marked as wrong when the loop count is 10× or more off the planner's estimate. The inspector's Loops ruler reads 338× more. The time strip shows both index scans as their own segments, so you can see that the lookups, not the join, took the time.