A 5,000 row signal table joins a 400 million row trade table on symbol, and each signal row matches about 80,000 trades. An index probe returning one row costs 0.1 ms, and a sequential scan reads 100 rows per page at 0.05 ms per page. Cost a nested loop against a hash join and say which the planner should pick.

A 5,000 row signal table joins a 400 million row trade table on symbol, and each signal row matches about 80,000 trades. An index probe returning one row costs 0.1 ms, and a sequential scan reads 100 rows per page at 0.05 ms per page. Cost a nested loop against a hash join and say which the planner should pick.

Approach: Count the rows each plan touches, price each touch with the stated per row figure, and compare the totals rather than reasoning from the shape of the plans.

Hash join, about 200 seconds against roughly 11 hours for the nested loop. The nested loop touches 5,000 * 80,000 = 400,000,000 matching rows and pays a random probe cost of 0.1 ms on each, which is 40,000 seconds, or about 11.1 hours. The hash join builds a hash table on the 5,000 row side, which fits in memory and costs nothing at this scale, then scans the large table once: 400m / 100 = 4,000,000 pages at 0.05 ms per page is 200 seconds, with an in memory lookup per row. The rule the arithmetic exposes is that a nested loop with an index wins when the outer side is small and each probe returns few rows, because it reads only what it needs, and it loses as soon as the total probed row count approaches the size of the inner table, because it has then read the whole table in scattered order rather than sequential order. Build and probe also degrades if the build side spills to disk, so confirm that the small side really is small before trusting the estimate that produced the plan.

Follow-up: At what average match count per signal row does the nested loop become the cheaper plan?

Key concepts: nested loop, hash join, random probe cost, build and probe.