Course contentsShow
← Software Engineering
Lesson 881 of 1,93327. Database Design in PracticeFree lesson

Filtering and Predicate Pushdown

Learn how WHERE clause positioning and predicate pushdown reduce data scanned and improve query performance.

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:

  1. Filter orders to only 'shipped' rows first (fewer rows to join)
  2. Filter customers to only 'USA' rows first
  3. 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.