A plan shows Nested Loop with rows=1 on the outer side and an inner Index Scan on trades, and the query takes 40 minutes with actual rows=2,400,000 on that outer side. Say what the planner got wrong, price the plan under a 0.1 ms index probe, and give two fixes.

A plan shows Nested Loop with rows=1 on the outer side and an inner Index Scan on trades, and the query takes 40 minutes with actual rows=2,400,000 on that outer side. Say what the planner got wrong, price the plan under a 0.1 ms index probe, and give two fixes.

Approach: Compare the estimated against the actual row count on the outer side first, then multiply the per probe cost by the actual count and see which join algorithm the arithmetic favours.

The planner estimated one outer row and chose a nested loop, so the inner index scan ran 2,400,000 times instead of once. At 0.1 ms per probe that is 2.4e6 * 0.1 ms = 240 seconds of pure lookup, and far more once the working set no longer fits in cache and every probe becomes random io, which is where 40 minutes comes from. The cardinality estimate is the entire failure: a nested loop is the cheapest join when the outer side is small and the worst when it is large, and the cost model scaled a correct per probe cost by a row count wrong by six orders of magnitude. Fix one is to make the estimate right, by refreshing statistics on the columns feeding the outer filter, adding an extended statistics object when two predicates are correlated, or rewriting a filter the planner cannot estimate through such as a function call or an opaque parameter. Fix two is to make the plan insensitive to the estimate by forcing a hash join, which builds once and probes once at O(n + m) whatever the estimate says. Read actual against estimated rows before any timing, because the ratio names the fault immediately.

Follow-up: Which of the two fixes do you prefer when the same query runs with a different parameter every minute?

Key concepts: cardinality estimate, nested loop, hash join, random io.