Benchmarking Execution Plan Changes After Refactoring

Learners will use comparative performance metrics to validate that query plan optimizations successfully reduce execution time and resource consumption.

In this lesson, you will learn how to quantitatively validate that your query refactoring efforts actually deliver the expected performance gains by comparing execution plan metrics.

Refactoring a query—such as decomposing a complex Common Table Expression (CTE) or adjusting join orders to leverage partition pruning—often looks better on paper, but it must be verified through empirical benchmarking. A "cleaner" query does not always translate to a more efficient execution plan. The goal is to prove that your changes have lowered the cost, duration, and resource consumption of the operation.

The Benchmarking Methodology

To validate your optimizations, you should follow a controlled testing cycle:

Capture the Baseline: Run the original query under a cold-start scenario (to clear caches) and capture the execution plan along with CPU Time, I/O Wait, and Total Execution Time.
Execute the Refactored Query: Run the optimized version in an identical environment.
Perform a Side-by-Side Plan Comparison: Look specifically for shifts in the Cost estimate provided by the Cost-Based Optimizer (CBO) and changes in the physical operators used (e.g., shifting from a Broadcast Join to a Shuffle Hash Join).

Concrete Illustration

Suppose you refactored a query to force a join that avoids a Full Table Scan. You can compare the metrics using a pseudo-code approach in your monitoring tool:

-- Benchmark original query
EXPLAIN ANALYZE SELECT * FROM sales s JOIN customers c ON s.id = c.id WHERE s.date > '2023-01-01';

-- Benchmark refactored query (with partition pruning applied)
EXPLAIN ANALYZE SELECT * FROM sales s PARTITION(y2023) JOIN customers c ON s.id = c.id;

When analyzing the output, look beyond the Total Execution Time. A successful refactor should show a reduction in Cardinality Estimates (making the optimizer's life easier) and a decrease in Data Shuffling (the number of rows moved across nodes). If the execution time drops but the data scanned increases, your refactor may have introduced hidden inefficiencies that will hurt you as data volume grows. Always store these plan outputs in a metadata table to track performance trends over time, ensuring that your changes remain performant as the underlying dataset evolves.

Key Takeaway: Benchmarking validates refactoring by comparing objective metrics like I/O, cardinality, and shuffle volume, ensuring that architectural intent aligns with actual hardware performance.