The cost of a join is driven by how many rows enter it, so a condition that removes 99% of a table is worth applying before the join rather than to the joined result. Planners push simple conditions down automatically, but they cannot push through some outer joins, some grouped subqueries and materialised CTEs — and putting a condition in the ON clause of an outer join means something different from putting it in WHERE. Checking the plan's row counts at each level is how you tell whether the filter actually moved.
Questions this Concept answers
Why does a planner sometimes fail to push a filter down below a join?
J
jeremy
Text
Pushing Predicates Into Subqueries and CTEs in PostgreSQL
A join costs roughly what the number of rows entering it costs, so a condition that throws away most of a table is worth applying before the join rather than after. This shows how the planner normall…