Predicate pushdown: why the database filters earlier than you wrote it
The database quietly reorders your query so filters happen as early as possible, before an expensive join, not after. Knowing when it CAN'T do that is what matters.
A "predicate" is just a fancy word for a filter condition: anything in a WHERE clause. Predicate
pushdown is the database noticing it can apply that filter earlier than you wrote it, to avoid
doing expensive work on rows it's about to throw away anyway.
🎯 Explain Like I'm Hired Predicate pushdown is the database noticing "I can throw away 90% of these rows before I do the expensive part" and doing that first, even though you wrote the filter after the join in your SQL. Modern databases do this automatically in simple cases. Example: writing
WHERE status = 'delivered'after a join doesn't force the database to join everything first and filter after. The planner quietly moves that filter earlier, so the join only has to process the delivered orders in the first place.
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'delivered' AND c.country = 'IN';
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.