**Predicate Pushdown:**

-- Before: Filter after join

SELECT * FROM (

SELECT o.*, c.name

FROM orders o

JOIN customers c ON o.customer_id = c.id

) t

WHERE t.status = 'active' AND t.created_at > '2024-01-01';

-- After: Filter before join

SELECT o.*, c.name

FROM (

SELECT * FROM orders

WHERE status = 'active' AND created_at > '2024-01-01'

) o

JOIN customers c ON o.customer_id = c.id;

**Join Elimination:**

-- Before: Unnecessary join

SELECT o.order_id, o.total

FROM orders o

JOIN customers c ON o.customer_id = c.id

JOIN products p ON o.product_id = p.id

WHERE o.status = 'completed';

-- Uses no columns from customers or products

-- After: Remove unused joins

SELECT order_id, total

FROM orders

WHERE status = 'completed';

**Subquery Flattening:**

-- Before: Nested subqueries

SELECT * FROM orders

WHERE customer_id IN (

SELECT id FROM customers

WHERE country IN (

SELECT code FROM countries WHERE region = 'EU'

)

);

-- After: Flattened to joins

SELECT o.*

FROM orders o

JOIN customers c ON o.customer_id = c.id