Filtering and Predicate Pushdown
What you'll learn: How strategic placement of WHERE clauses and predicate pushdown optimize queries by reducing the amount of data scanned early in execution.
What Is Predicate Pushdown?
A predicate is simply a filtering condition (like WHERE status = 'active'). Predicate pushdown is the database optimizer's technique of applying these filters as early as possible in the query execution process—ideally before joining tables or scanning large datasets.
Think of it like grocery shopping: instead of loading your entire cart with every item in the store and then filtering out what you don't need at checkout, you only pick up items matching your list from the start. This saves time and effort.
How WHERE Clause Positioning Matters
When you write queries involving multiple tables, the database doesn't necessarily execute them in the order you wrote them. The optimizer tries to "push down" WHERE conditions to filter rows before expensive operations like joins or aggregations.
For example:
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.status = 'shipped'
AND customers.country = 'USA';
A smart optimizer will:
- Filter
ordersto only'shipped'rows first (fewer rows to join) - Filter
customersto only'USA'rows first - Then perform the join on the reduced datasets
This dramatically reduces I/O and memory usage compared to joining everything first, then filtering.
Checking Your Execution Plan
Your query execution plan (from lesson 879) will show whether predicates are pushed down effectively. Look for filters appearing early in the plan, ideally alongside index scans rather than after full table scans.
Key Takeaway: Predicate pushdown reduces query cost by applying WHERE filters as early as possible, minimizing the data scanned, joined, and processed—always verify this optimization in your execution plans.