What is a Query Execution Plan?

by

A query execution plan is a detailed roadmap that shows exactly how a database will execute your SQL query. When you submit a query, the database doesn’t just start grabbing data randomly. Rather, it creates a step-by-step strategy for retrieving and processing your data in the most efficient way possible.

The query execution plan is that strategy made visible.

Basically, the SQL you write tells the database what you want, but the execution plan shows you how it’s actually going to get it. This includes which tables it’ll scan, what indexes it’ll use, how it’ll join tables together, and in what order everything will happen.

The Purpose of Execution Plans

The main purpose of an execution plan is to determine the most efficient way to retrieve the requested data. The database’s query optimizer generates this plan by evaluating various methods (such as using specific indexes, performing table scans, deciding the optimal order for joins, etc) and selecting the plan with the lowest estimated cost (typically measured in disk I/O, CPU usage, and memory). By finding the best execution path, the plan ensures queries run as fast as possible, minimizing resource usage and improving overall database performance.

So the purpose of the execution plan is directly linked to performance optimization, and it’s there for your benefit.

If your query is running slowly, the execution plan is usually where you’ll find out why. Maybe it’s scanning millions of rows when an index could narrow it down to hundreds. Maybe it’s joining tables in an inefficient order. Maybe it’s not using an index you thought it would.

Without looking at the execution plan, you’re basically guessing about what’s causing performance problems. With it, you can see exactly where the bottlenecks are and make informed decisions about how to fix them.

What’s Inside an Execution Plan

Execution plans contain a wealth of information, although the exact details vary by database system. Here’s what you’ll typically find:

  • The execution plan will show you which tables and indexes are being accessed. For each table, you’ll see whether it’s doing a full table scan (reading every row) or using an index to narrow things down. You’ll see the join methods being used, such as nested loops, hash joins, or merge joins, and the order in which tables are joined together.
  • Cost estimates are a big part of execution plans. The database assigns estimated costs to each operation based on factors like how many rows it expects to process and how much I/O will be required. These are relative numbers, not actual time measurements, but they help you identify the most expensive parts of your query.
  • Many databases also show estimated versus actual row counts. If the database estimates it’ll process 100 rows but actually processes 100,000, that’s a sign your statistics might be outdated or that your data distribution is unusual.

Here’s an example of a simple execution plan in SQL Server:

Screenshot of execution plan in SQL Server

This is a graphical execution plan that uses charts with icons, arrows, etc to help convey how the database has chosen to deal with the query. We can also get textual representations of the query plan, which is useful when using a command line tool.

If you’ve never seen a query execution plan before, the above graphic might look like a cryptic mess. But don’t worry, we’ll cover how to read a query plan later in the article.

About Database Statistics

We just mentioned outdated statistics as being a potential cause of discrepancies in the query plan’s estimated row count and the actual row count. But what exactly are these “statistics”?

When we talk about database statistics in the context of execution plans, we’re referring to metadata that the database collects about your tables and indexes. This isn’t the data itself, but information about the data. This includes things like how many rows are in each table, how many distinct values exist in each column, the distribution of values, and the range of values in indexed columns.

The query optimizer relies heavily on these statistics to make decisions. When it’s choosing between different execution strategies, it uses statistics to estimate how many rows each operation will process, which directly affects its cost calculations. For example, if statistics show that only 2% of customers are premium members, the optimizer knows that filtering on that column early will dramatically reduce the amount of data to process.

Statistics can become outdated as your data changes. If you insert 500,000 new rows but the statistics still reflect the old row count, the optimizer might make poor decisions based on obsolete information. Most databases have commands like ANALYZE (PostgreSQL, MySQL) or UPDATE STATISTICS (SQL Server) to refresh this metadata. Some databases update statistics automatically, but for tables with frequent changes, manual updates can be important for maintaining good performance.

When you see execution plans with wildly inaccurate row estimates – like estimating 100 rows but actually processing 100,000 – outdated statistics are often the culprit.

How to View Execution Plans

Each database system has its own way of displaying execution plans, but most follow similar patterns.

MySQL and PostgreSQL use EXPLAIN to show the planned execution without running the query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

If you want to see actual execution statistics, use EXPLAIN ANALYZE, which runs the query and shows real timings:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

SQL Server requires you to enable plan display before running your query:

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders WHERE customer_id = 12345;
GO
SET SHOWPLAN_TEXT OFF;
GO

For actual execution statistics, use SET STATISTICS PROFILE ON instead.

Oracle uses a two-step process where you generate the plan first, then display it:

EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = 12345;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Using GUI Tools

When we run such commands, the output is typically in a textual format. But most database management interfaces also offer graphical execution plan viewers (like the one in the screenshot above). These tools often highlight expensive operations automatically, which can help you spot problems faster.

The way to access the query plan via the GUI depends on the tool. But look for buttons that read something like Explain, Query Plan, or similar. Some enable you to view the estimated query plan vs the actual query plan. For example, in SSMS, click Display Estimated Execution Plan or Include Actual Execution Plan.

Reading an Execution Plan

At first, trying to decipher an execution plan can be daunting. There’s a lot of information to take in, and it can be difficult to know which parts to focus on. And the format will depend on the database management system (DBMS). Some display plans as trees, some as tables, and some as flowcharts. But with that said, there are some common principles between them:

  • Most (but not all) plans need to be read from the inside out or bottom up, depending on the database. The operations that happen first are usually the most deeply nested or lowest in the visual hierarchy. Operations closer to the top or outer edges happen later in the execution process.
  • Look for operations that process large numbers of rows. Table scans on big tables, operations with high estimated costs, or steps where the actual row count vastly exceeds the estimated count are all red flags. These are often where your performance problems live.
  • Pay attention to which indexes are being used – or more importantly, which ones aren’t. If you created an index specifically to speed up a certain query but the execution plan shows a table scan instead, something’s preventing the database from using that index.

Example of an Execution Plan

Let’s look at a simple example to see what an execution plan reveals. We’ll create a basic e-commerce database:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(100),
    country VARCHAR(50),
    account_type VARCHAR(20)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES
(1, '[email protected]', 'USA', 'premium'),
(2, '[email protected]', 'Canada', 'standard'),
(3, '[email protected]', 'UK', 'premium'),
(4, '[email protected]', 'Australia', 'standard'),
(5, '[email protected]', 'USA', 'premium');

INSERT INTO orders VALUES
(101, 1, '2024-11-15', 250.00, 'shipped'),
(102, 1, '2024-11-20', 180.00, 'delivered'),
(103, 2, '2024-11-18', 95.00, 'processing'),
(104, 3, '2024-11-22', 420.00, 'shipped'),
(105, 4, '2024-11-25', 310.00, 'delivered'),
(106, 1, '2024-11-28', 75.00, 'processing'),
(107, 5, '2024-11-27', 540.00, 'shipped');

Now let’s say you plan to run the following query, which finds all premium customers who have placed orders:

SELECT c.email, COUNT(o.order_id) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.account_type = 'premium'
GROUP BY c.email;

But before you run it, you’d like to see the query execution plan. In that case you can prepend it with EXPLAIN if you’re using MySQL or PostgreSQL:

EXPLAIN SELECT c.email, COUNT(o.order_id) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.account_type = 'premium'
GROUP BY c.email;

This will produce a different output, depending on the DBMS.

MySQL’s Execution Plan

When I run the above EXPLAIN query in MySQL, I get this:

+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref                                  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL                                 |    5 |    20.00 | Using where; Using temporary |
|  1 | SIMPLE      | o     | NULL       | ref  | customer_id   | customer_id | 5       | sandbox_db.c.customer_id |    1 |   100.00 | Using index                  |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------------------------------+------+----------+------------------------------+

The plan reads from top to bottom:

  1. MySQL starts with the customers table (aliased as c), doing a full table scan (type: ALL) of all 5 rows. The filtered value of 20.00 means about 20% of rows pass the WHERE filter for account_type = 'premium'. The “Using temporary” in the Extra column indicates MySQL is creating a temporary table to handle the GROUP BY operation.
  2. Next, for each matching customer, MySQL looks up their orders in the orders table (aliased as o) using the customer_id index (type: ref). The rows value of 1 is an estimate of how many orders it expects to find per customer. “Using index” means it can satisfy this lookup entirely from the index without accessing the actual table data.

The main thing we might notice here is the full table scan on customers. With only 5 rows, this is fine. But if you had 100,000 customers, that type: ALL would be a red flag suggesting you need an index on the account_type column. The plan also shows that the customer_id index on orders is being used effectively for the join, which is good.

PostgreSQL’s Execution Plan

Here’s PostgreSQL’s query plan for the same EXPLAIN statement:

                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 GroupAggregate  (cost=31.44..31.48 rows=1 width=226)
   Group Key: c.email
   ->  Sort  (cost=31.44..31.45 rows=4 width=222)
         Sort Key: c.email
         ->  Hash Join  (cost=12.39..31.40 rows=4 width=222)
               Hash Cond: (o.customer_id = c.customer_id)
               ->  Seq Scan on orders o  (cost=0.00..17.10 rows=710 width=8)
               ->  Hash  (cost=12.38..12.38 rows=1 width=222)
                     ->  Seq Scan on customers c  (cost=0.00..12.38 rows=1 width=222)
                           Filter: ((account_type)::text = 'premium'::text)
(10 rows)

PostgreSQL’s plans read from bottom-up and inside-out based on indentation. The execution starts with the most indented operations and works its way up.

Here’s what PostgreSQL’s query plan tells us:

  1. First, PostgreSQL does a sequential scan on customers (Seq Scan on customers c), filtering for account_type = 'premium'. It estimates this will return 1 row.
  2. Next, it builds a hash table from those filtered customers.
  3. Meanwhile, it does a sequential scan on orders (Seq Scan on orders o), estimating 710 rows.
  4. Then it performs a hash join between the orders and the customer hash table, matching on customer_id.
  5. After the join, it sorts the results by c.email (required for the GROUP BY).
  6. Finally, it performs the group aggregation to count orders per customer.

The cost values show relative expense (higher numbers mean more work). These are the numbers separated by two periods. For example, when we see cost=31.44..31.48, the first number is 31.44 and the second is 31.48. The first number is the startup cost (work before returning the first row) and the second is the total cost. The rows value is the estimated number of rows each operation will produce.

Comparing the Two Plans

You’ll notice that PostgreSQL chose hash join for combining the tables, while MySQL used nested loops with index lookups. Both are doing sequential scans on the small tables because with only a few rows, scanning everything is faster than using indexes. With larger tables, you’d likely see index scans instead of sequential scans, and the costs would be much higher.

In a nutshell, running EXPLAIN on the query shows:

  • Whether it’s scanning the entire customers table or using an index
  • How it’s joining the two tables together
  • The order in which it applies filters
  • Estimated costs for each operation

So if the query is running slow, the execution plan might reveal that it’s doing a sequential scan on customers when an index on account_type would help. Or it might show that the join is processing way more rows than necessary because filters aren’t being applied early enough.

Common Things to Look For

When you’re analyzing an execution plan, certain patterns often indicate problems. Here are some of the main ones:

  • Full table scans on large tables are expensive. If you’re scanning millions of rows to find a handful of matches, you probably need an index. Table scans are fine for small tables or when you genuinely need most of the rows, but they can be a big problem when you’re filtering down to a small result set.
  • Missing index usage is another common issue. You might have created an index, but if you’re using functions on indexed columns in your WHERE clause (like WHERE YEAR(order_date) = 2024) the database might not be able to use the index. Rewriting to WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' often solves this.
  • Wildly inaccurate row estimates suggest your statistics are outdated. If the plan estimates 100 rows but actually processes 50,000, the database might make poor decisions about join methods or memory allocation. Updating statistics usually fixes this.
  • Inefficient join orders can also hurt performance. If the database joins two large tables together first and then filters the result, it’s doing a lot of unnecessary work. Ideally, filters should be applied early to reduce the amount of data being joined.

Execution Plans and Performance Tuning

Execution plans are your primary tool for performance tuning. When a query is slow, start by looking at its plan. Identify the most expensive operations, then ask yourself why they’re expensive.

Sometimes the fix is obvious. It could be to add an index, update statistics, or rewrite part of the query. Other times, you might discover that your query is fundamentally doing a lot of work and there’s no magic solution. But at least you’ll know what’s actually happening instead of just guessing.

Remember that execution plans can change as your data changes. A plan that works great with 1,000 rows might become inefficient with 1,000,000 rows. As your database grows, periodically review the execution plans of your important queries to make sure they’re still performing well.

Quick Article Summary

The execution plan is ultimately about visibility. SQL is a declarative language where you describe what you want, not how to get it. Execution plans bridge that gap by showing you the “how”. Once you can see how your queries actually execute, you can make much smarter decisions about how to improve them.