In this lesson, you will learn how to systematically rewrite relational algebra expressions to reduce the computational cost of your queries.
When we talk about refactoring for performance, we aren't just cleaning up syntax; we are applying the laws of relational algebra to transform an inefficient logical plan into one that executes faster. The goal is to move the "heavy lifting" (like filtering or joins) as early as possible in the processing pipeline.
The Rule of Early Reduction
Relational algebra is commutative and associative, meaning the order in which we perform operations often produces the same result set, but at vastly different costs.
Consider a scenario where you are joining a large Orders table with a small Customers table, then filtering by Country.
The Inefficient Approach:
If you perform the join first, you create a massive temporary intermediate relation containing every order from every country, only to discard most of them in the final step.
σ(Country='USA')(Orders ⨝ Customers)
The Refactored Approach:
By applying Predicate Pushdown (which we studied in lesson 12), you move the filter inside the join.
(σ(Country='USA')(Orders)) ⨝ Customers
By filtering the Orders table before the join, you reduce the number of tuples passed to the join operator. This minimizes memory usage, reduces the likelihood of spilling to disk, and potentially allows the engine to utilize a covering index on the Country column.
Systematic Refactoring Steps
To refactor your queries effectively, follow this sequence:
- Push down selections: Filter rows as early as possible.
- Project early: Only select the specific columns required for the final output or intermediate joins, reducing the memory footprint of each row.
- Reorder joins: Place the most restrictive filters on the tables that are joined first to minimize the size of intermediate results.
Think of your query like a factory assembly line. If you can discard defective parts (filtering) or remove unnecessary packaging (projecting) at the start of the line, you spend far less energy moving that weight through the rest of the factory.
Key Takeaway: Refactoring for performance means mathematically rearranging operations to minimize the size of intermediate data sets before they reach expensive operators like joins.