What is a Query Plan

Last modified: December 09, 2019

A Query plan is a list of instructions that the database needs to follow in order to execute a query on the data.

Below is an example query plan for a given query:

Shows a query plan and points out the key parts: costs, rows, data size, and the method being used

This query plan shows the particular steps taken to execute the given command. It also specifies the expected cost for each section.

Video

The Query Optimizer

SQL is a declarative language. This means that SQL queries describe what the user wants and then the query is transformed into executable commands by the Query Optimizer. Those executable commands are known as Query Plans.

The Query Optimizer generates multiple Query Plans for a single query and determines the most efficient plan to run.

Graphic showing how the query optimizer creates query plans and selects the best plan

There are often many different ways to search a database. Take for example the following database of tools that has five entries. Each entry has a unique ID number and a non-unique name.

Table containing an ID column and a Name column. the IDs are the numbers 1-5. The names are: Callipers, Hammer, Screwdriver, Wrench, and Hammer.

In order to find a particular tool, there are several possible queries that could be run. For example, the query:

SELECT *
FROM tools
WHERE name='Screwdriver';

Will return the same thing as the query:

SELECT *
FROM tools
WHERE id=3;

Scan Vs. Seek

These queries will return the same results, but may have different final query plans. The first query will have a query plan that uses a sequential scan. This means that all five rows of the database will be checked to see if the name is screwdriver and, when run, would look like the following table:

table showing that the correct entry is at slot 3 in the table, but in a scan, the last two values must be checked anyways

(green = match)

(red = miss)

(white = not checked)

The second query will use a query plan which implements a sequential seek since the second query handles unique values. Like a scan, a seek will go through each entry and check to see if the condition is met. However unlike a scan, a seek will stop once a matching entry has been found. A seek for ID = 3 would look like the following figure:

Table showing that with a seek, the last two values of the table can be ignored.

(green = match)

(red = miss)

(white = not checked)

This seek only needs to check three rows in order to return the result unlike a scan which must check the entire database.

For more complicated queries there may be situations in which one query plan implements a seek while the other implements a scan. In this case, the query optimizer will choose the query plan that implements a seek, since seeks are more efficient than scans. There are also different types of scans that have different efficiencies in different situations.

Summary

Query plans are a set of instructions generated by the Query Optimizer.
They generate estimates of query efficiency
The Query Optimizer generates multiple query plans and determines which plan is most efficient for a given query.
Scan Vs. Seek
Scans search the whole database for matches
Seeks search the database for a single match and stop once they have found it

Written by: Matthew Layne
Reviewed by: Blake Barnhill , Matt David

Video

The Query Optimizer

Scan Vs. Seek

Created by