Ten Rows Versus Hundred

The same query shape, run twice against the same two tables, differing only in how many rows the outer predicate lets through.

Predicate t1.unique1 < 10:

Nested Loop  (cost=4.65..118.50 rows=10 width=488)
  ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
        Recheck Cond: (unique1 < 10)
  ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
        Index Cond: (unique2 = t1.unique2)

Predicate t1.unique1 < 100:

Hash Join  (cost=226.23..709.73 rows=100 width=488)
  Hash Cond: (t2.unique2 = t1.unique2)
  ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
  ->  Hash  (cost=224.98..224.98 rows=100 width=244)
        ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)

tenk2 has 10,000 rows and a usable index on unique2. Why does the planner abandon the index-driven nested loop?

A nested loop requires the inner join column to be unique, and unique2 stops being treated as unique once 100 outer rows are involved.

A nested loop pays one index lookup per outer row, so its cost scales with the outer row count. At 100 outer rows that total passes the fixed price of scanning tenk2 once and probing a hash table built from the small side.

Once more than a few dozen rows qualify, the planner stops considering the index on tenk2 because index scans are only valid for highly selective predicates.

Hash join is chosen whenever both join inputs exceed 100 rows, regardless of the available indexes.