The standard situation you'll see a Nested Loop in is one where the inner table is only returning back a limited number of rows. If an index exists on one of the two tables involved, the optimizer is going to use it to limit the number of rows substantially, and the result may then make the inner * outer rows runtime of the Nested Loop acceptable. Consider the case where you're looking for a single order using a field with no index (so every order must be scanned), but then joining with its matching orderlines:
EXPLAIN ANALYZE SELECT * FROM orders,orderlines WHERE orders.totalamount=329.78 AND orders.orderid=orderlines.orderid; QUERY PLAN ---------- Nested Loop (cost=0.00..265.41 rows=5 width=54) (actual ...
