Eric Campbell 5 min read SQL

SQL - Query Execution Plan Explained

• Query execution plans reveal how the database engine processes your SQL statements, showing the actual operations, join methods, and data access patterns that determine query performance

Key Insights

• Query execution plans reveal how the database engine processes your SQL statements, showing the actual operations, join methods, and data access patterns that determine query performance • Reading execution plans requires understanding key operators like Index Seek vs Scan, Nested Loops vs Hash Joins, and identifying bottlenecks through metrics like estimated rows vs actual rows and cost percentages • Optimizing queries based on execution plans involves strategic index creation, rewriting queries to avoid table scans, and restructuring joins to minimize intermediate result sets

Understanding Query Execution Plans

A query execution plan is the database engine’s roadmap for executing your SQL statement. When you submit a query, the optimizer evaluates multiple execution strategies and selects the most efficient path based on statistics, indexes, and available resources. Understanding these plans is essential for diagnosing performance issues and writing efficient queries.

Most database systems provide tools to view execution plans. In SQL Server, use SET STATISTICS PROFILE ON or the graphical execution plan in SSMS. PostgreSQL offers EXPLAIN ANALYZE, while MySQL uses EXPLAIN or EXPLAIN ANALYZE in version 8.0+.

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT o.OrderID, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS Total
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '2024-01-01'
GROUP BY o.OrderID, c.CustomerName;

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.customer_name, SUM(od.quantity * od.unit_price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.order_id, c.customer_name;

Key Execution Plan Operators

Execution plans consist of operators that represent specific operations. Understanding these operators helps you identify inefficiencies.

Index Seek vs Index Scan: An Index Seek directly locates rows using an index, like looking up a word in a dictionary. An Index Scan reads the entire index sequentially. Seeks are typically faster for selective queries.

-- This should produce an Index Seek (assuming index on CustomerID)
SELECT * FROM Orders WHERE CustomerID = 12345;

-- This might produce an Index Scan
SELECT * FROM Orders WHERE CustomerID > 10000;

Table Scan: Reads every row in the table. This is acceptable for small tables but catastrophic for large ones.

-- Likely causes a Table Scan without proper indexing
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- Better: Sargable predicate that can use an index
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

Join Operators: The optimizer chooses between Nested Loops, Hash Match, and Merge Join based on table sizes and available indexes.

  • Nested Loops: Efficient for small outer tables with indexed inner tables
  • Hash Match: Good for large tables without suitable indexes
  • Merge Join: Optimal when both inputs are sorted on the join key
-- Create scenario for different join types
CREATE INDEX idx_orders_customerid ON Orders(CustomerID);
CREATE INDEX idx_customers_customerid ON Customers(CustomerID);

-- Small result set: likely Nested Loops
SELECT o.*, c.*
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID = 1001;

-- Large unindexed join: likely Hash Match
SELECT o.*, c.*
FROM Orders o
JOIN Customers c ON YEAR(o.OrderDate) = c.RegistrationYear;

Reading Execution Plan Metrics

Execution plans provide critical metrics that reveal performance bottlenecks.

Estimated Rows vs Actual Rows: Large discrepancies indicate stale statistics, leading to poor optimization decisions.

-- Update statistics to improve estimates
UPDATE STATISTICS Orders WITH FULLSCAN;
UPDATE STATISTICS Customers WITH FULLSCAN;

-- PostgreSQL
ANALYZE orders;
ANALYZE customers;

Cost Percentage: Shows which operators consume the most resources. Focus optimization efforts on high-cost operations.

Logical Reads: Number of pages read from cache. High logical reads indicate excessive data access.

-- SQL Server: Check IO statistics
SET STATISTICS IO ON;
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01';
-- Look for "logical reads" in the output

Identifying Common Performance Issues

Missing Indexes: The execution plan often suggests missing indexes. However, don’t blindly create every suggested index—consider write overhead and maintenance costs.

-- Problem: Table scan on frequently filtered column
SELECT * FROM Orders WHERE Status = 'Pending';

-- Solution: Create filtered index
CREATE INDEX idx_orders_pending 
ON Orders(Status) 
WHERE Status = 'Pending';

-- Even better: Covering index for specific query
CREATE INDEX idx_orders_status_covering 
ON Orders(Status) 
INCLUDE (OrderID, CustomerID, OrderDate, TotalAmount);

Implicit Conversions: Occur when comparing columns of different data types, preventing index usage.

-- Problem: OrderID is INT, but comparing to string
SELECT * FROM Orders WHERE OrderID = '12345'; -- Causes conversion

-- Solution: Use correct data type
SELECT * FROM Orders WHERE OrderID = 12345;

-- Check for warnings in execution plan about implicit conversions

Parameter Sniffing: The optimizer creates a plan based on the first parameter value, which may not suit subsequent executions.

-- Problem: Plan optimized for one parameter value
CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(20)
AS
SELECT * FROM Orders WHERE Status = @Status;

-- Solution 1: Use OPTION (RECOMPILE) for varying parameters
CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(20)
AS
SELECT * FROM Orders WHERE Status = @Status
OPTION (RECOMPILE);

-- Solution 2: Use local variable to prevent sniffing
CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(20)
AS
DECLARE @LocalStatus VARCHAR(20) = @Status;
SELECT * FROM Orders WHERE Status = @LocalStatus;

Optimizing Based on Execution Plans

Rewriting Subqueries: Correlated subqueries often perform poorly. Convert them to joins when possible.

-- Inefficient: Correlated subquery
SELECT c.CustomerName,
       (SELECT SUM(TotalAmount) 
        FROM Orders o 
        WHERE o.CustomerID = c.CustomerID) AS TotalSpent
FROM Customers c;

-- Optimized: JOIN with aggregation
SELECT c.CustomerName, COALESCE(SUM(o.TotalAmount), 0) AS TotalSpent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName;

Reducing Result Set Size Early: Apply filters before joins when possible to minimize intermediate result sets.

-- Less efficient: Filter after join
SELECT o.*, c.*
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01';

-- More efficient: Filter before join (when appropriate)
SELECT o.*, c.*
FROM (SELECT * FROM Orders WHERE OrderDate >= '2024-01-01') o
JOIN Customers c ON o.CustomerID = c.CustomerID;

Using Appropriate Indexes: Create indexes that match your query patterns, considering column order in composite indexes.

-- Query filtering on Status and OrderDate
SELECT * FROM Orders 
WHERE Status = 'Pending' 
AND OrderDate >= '2024-01-01'
ORDER BY OrderDate DESC;

-- Optimal index: Most selective column first, with sort column
CREATE INDEX idx_orders_status_date 
ON Orders(Status, OrderDate DESC);

-- Include frequently selected columns to create covering index
CREATE INDEX idx_orders_status_date_covering
ON Orders(Status, OrderDate DESC)
INCLUDE (CustomerID, TotalAmount);

Advanced Analysis Techniques

Comparing Actual vs Estimated Execution Plans: Actual plans show runtime statistics, revealing issues not visible in estimated plans.

-- PostgreSQL: Compare estimated vs actual
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

-- Look for differences in row estimates and actual execution time

Analyzing Query Store Data (SQL Server 2016+): Track query performance over time to identify regressions.

-- Enable Query Store
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

-- Find queries with high resource consumption
SELECT TOP 10
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration/1000 AS avg_duration_ms,
    rs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

Mastering execution plans transforms you from writing queries that work to writing queries that perform. Regularly analyze your critical queries, understand the optimizer’s decisions, and make informed adjustments based on actual execution patterns rather than assumptions.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.