**Predicate Pushdown Impact:**

Before:

- Join 10M orders with 1M customers = 10M rows

- Filter to 1M active orders

- Time: 45 seconds

After:

- Filter to 1M active orders

- Join 1M orders with 1M customers = 1M rows

- Time: 5 seconds (9x faster)

Rule: Filter reduces data by 10x = 10x speedup

**Join Elimination Impact:**

With unnecessary join:

- Scan orders: 10M rows

- Scan customers: 1M rows

- Join: 10M comparisons

- Time: 15 seconds

Without join:

- Scan orders: 10M rows

- Time: 2 seconds (7x faster)

**CTE Materialization:**

Materialized (compute once):

- Expensive calculation: 10 seconds

- Used 3 times: 10 seconds total

- Good for: Reused expensive calculations

Inlined (compute per use):

- Simple filter: 0.1 seconds

- Used 3 times: 0.3 seconds total

- Good for: Simple operations

**LATERAL vs Correlated Subquery:**

Correlated subquery:

- Executions: 100K (once per customer)

- Time per execution: 0.0005s

- Total: 50 seconds

LATERAL join:

- Executions: 1 (optimized by database)

- Time: 5 seconds (10x faster)

**Window Function vs Self-Join:**

Self-join:

- Algorithm: Nested loop

- Complexity: O(n²)