Predicate Pushdown for Complex Report Query

advanced

Monthly sales report with nested subqueries and joins takes 5 minutes. Query filters data after expensive joins. Apply predicate pushdown to filter early, reducing to 30 seconds.

-- BEFORE: Filter after all joins (5 minutes)

SELECT

r.region_name,

SUM(t.total) as revenue,

COUNT(t.order_id) as order_count

FROM (

SELECT

o.order_id,

o.total,

o.customer_id,

o.created_at

FROM orders o

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.id

JOIN customers c ON o.customer_id = c.id

JOIN regions r ON c.region_id = r.id

) t

JOIN regions r ON t.region_id = r.id

WHERE t.created_at >= '2024-01-01'

AND t.created_at < '2024-02-01'

AND t.status = 'completed'

GROUP BY r.region_name;

-- Problems:

-- 1. Joins ALL orders (10M) with order_items (50M)

-- 2. Then filters to January completed (500K)

-- 3. Processes 50M rows to get 500K

-- Time: 5 minutes

-- AFTER: Push filters down (30 seconds)

SELECT

r.region_name,

SUM(o.total) as revenue,

COUNT(o.order_id) as order_count

FROM (

-- Filter orders FIRST

SELECT order_id, total, customer_id

FROM orders

WHERE created_at >= '2024-01-01'