Comparison
Broadcast joinvsCardinality estimate
Broadcast join
the small dimension table is copied to every worker so the big table never has to move.
Joining by sending a copy of the smaller side to every node, avoiding a shuffle of the large side entirely. It is dramatically faster when the small side genuinely is small, and it fails hard when the planner's size estimate is wrong — broadcasting something large runs every executor out of memory. The threshold is configurable and the estimate comes from statistics, which is why stale statistics show up as a query that used to be fast.
Full entry →Cardinality estimate
the plan says it expects nine hundred rows from that filter, it actually returns forty million, and every choice after that is wrong.
The planner's guess at how many rows each step will produce, from which it picks join algorithms, join order and memory allocations. A bad estimate early in the plan compounds through everything after it, which is why one wrong filter estimate can make an otherwise sensible query catastrophic. Comparing estimated against actual row counts in the plan is the fastest way to find where a query went wrong.
Full entry →