What is a Query Plan?

4
min read

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:


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

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.


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.

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

CODE:https://gist.github.com/fronofro/e25d2f820698ef3fd34fb0af407d6b34.js

Will return the same thing as the query:

CODE:https://gist.github.com/fronofro/c5b7e5a15d1d5714518d7618315f3c7a.js

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:

(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:

(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

Resources

  1. https://www.postgresql.org/docs/9.4/using-explain.html
  2. https://www.essentialsql.com/what-is-a-query-plan/
  3. https://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i82005
  4. https://www.techopedia.com/definition/26224/query-optimizer

Give Feedback on our Google Doc

Chapter topics