Principles and Practice of Predicate Pushdown in Database Query Optimization

Topic Description
Predicate Pushdown is a crucial technique in database query optimization. Its core idea is to apply filtering conditions as early as possible in the query process, thereby reducing the amount of data processed in subsequent steps and improving query performance. This topic will detail the principles, applicable scenarios, limitations, and actual optimization effects of predicate pushdown.

Solution Process

1. Basic Concept of Predicate Pushdown

   • Definition: During query execution, push the filtering conditions (predicates) from the WHERE clause "down" to execution steps closer to the data source (e.g., before table scans or join operations).
   • Goal: Filter out data that does not meet the conditions as early as possible, reduce the size of intermediate result sets, and lower CPU and I/O overhead.
   • Example Scenario:

     SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id
     WHERE customers.country = 'China';
     

     Without optimization, a join operation on both tables must be performed first, followed by data filtering. With predicate pushdown, records satisfying customers.country = 'China' can be filtered first before participating in the join.

2. How Predicate Pushdown Works

   • Role of the Optimizer: The query optimizer rewrites the query logic during execution plan generation to move predicates to appropriate positions.
   • Pushdown Directions:
     • Push to Data Source: Push predicates directly to the table scan phase (e.g., using indexes to filter data).
     • Push to Join Operations: Filter single tables before the join or push predicates to the other side of the join (e.g., in specific cases of outer joins).
   • Underlying Implementation:
     • Restructure the syntax tree to move the filter node down in the query plan tree.
     • Example: Rewrite σ_{country='China'}(orders ⋈ customers) as (σ_{country='China'}(customers)) ⋈ orders.

3. Applicable Scenarios for Predicate Pushdown

   • Inner Join Queries: Predicates can be freely pushed down to either side of the join.
   • Partitioned Table Queries: Combined with partition pruning to directly skip partitions that do not meet the conditions.
   • Subquery Optimization: Push predicates from the outer query down into the subquery to reduce the subquery's result set.
   • Columnar Storage Databases: Filter data early during column scans to reduce disk reads.

4. Limitations of Predicate Pushdown

   • Asymmetry in Outer Joins:
     • In a LEFT OUTER JOIN, predicates on the right table cannot be pushed down (to avoid losing data from the left table), but predicates on the left table can.
     • Example:

       SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.id
       WHERE customers.country = 'China';
       

       Pushing down the right table predicate would incorrectly filter out unmatched records from the left table, so filtering must remain after the join.
   • Aggregate and Window Functions: Filtering conditions applied after aggregation (HAVING clause) cannot be pushed down before the aggregation.
   • Expression Dependencies: Predicates that depend on the results of other operations (e.g., scalar subqueries) cannot be pushed down.

5. Analysis of Actual Optimization Effects

   • Performance Improvement Case:
     • Assume the orders table has 1 million rows, the customers table has only 100 rows, and only 10 rows meet country='China'.
     • Without pushdown: Needs to filter 1 million joined results.
     • With pushdown: Filter the customers table first, only 10 rows participate in the join, reducing computation by over 99%.
   • Execution Plan Verification:
     • Use the EXPLAIN command to observe if filtering conditions appear at the table scan stage (e.g., in Index Scan or Filter nodes).

6. Practical Suggestions

   • When writing queries, try to place filtering conditions as close to the data source as possible (e.g., inside subqueries).
   • Combine with index design to ensure pushed-down predicates can leverage indexes for acceleration.
   • Use database performance tools (e.g., execution plan visualization) to verify if pushdown is effective.

Through the above steps, the optimization logic, applicable boundaries, and practical value of predicate pushdown are clearly demonstrated, helping to systematically improve performance in complex queries.