Getting Started With Explain
This class helps you understand how to interpret and use EXPLAIN with your PostgreSQL queries. EXPLAIN (and EXPLAIN ANALYZE) show you the query plan (and actual timings obtained by running the query) the PostgreSQL query planner uses to satisfy your query.
This tool is one of the first things people will ask you to use when you say "My query is slow". While this tool will not fix your query, it will give you insight into how PostgreSQL planned the query and where time is spent during query execution.
One of the most important part of any RDBMS is the query planner - the piece of the stack that takes your SQL and determines the most efficient method to find and process the data to get your result. Quoting the PostgreSQL documentation
The task of the planner/optimizer is to create an optimal execution plan. A given SQL query (and hence, a query tree) can be actually executed in a wide variety of different ways, each of which will produce the same set of results. If it is computationally feasible, the query optimizer will examine each of these possible execution plans, ultimately selecting the execution plan that is expected to run the fastest.
What is EXPLAIN and EXPLAIN ANALYZE
When you put EXPLAIN in front of your SQL query, instead of getting the results of the query, you get the plan the Query Planner would have used to execute your query. The format of the results is plain text, but you can also ask for other formats. The results will include estimates of how many rows will be returned, the estimated average size of each row, and what indices, if any, will be used. To actually run the query and see the timing for each part of the plan, you would put EXPLAIN ANALYZE in front of the query.
WARNING: EXPLAIN ANALYZE actually executes the SQL - so if your SQL is an update or delete it will actually change your underlying data! If you want to see actual timings for update, create, or delete queries but not change your data, wrap the statement in a transaction block that you rollback.
One of your primary diagnostic tools for query performance will be using EXPLAIN.
So let's dig in and see how to use EXPLAIN.
We have loaded up a PostgreSQL database with Storm Event data from 2018 in the US. Here are the details on the database we are connecting to:
- Username: groot
- Password: password (same password for the postgres user as well)
- A database named: workshop
Warning: Depending on the version of the data loaded in the scenario the actual numbers may vary from the numbers shown here. The explanations and format will be the same, but there might be an difference in metrics such as actual rows returned or timings.
Using Explain to Write Better Queries
Understand Explain Output
The very basics of EXPLAIN
Let's start by doing some simple uses of EXPLAIN and the various output options.
For our first query let's just return rows from the Storm Details table (se_details). We'll start east with a simple SQL query and limit the results to 5 rows (there are almost 60K storms in the table).
NOTE: Clicking on the black box below will execute the statement in the console window to the right.
select * from se_details limit 5;
Now let's see how the Query Planner analyzed this query (without the limit):
EXPLAIN select * from se_details;
Your output should look like this (without the red numbers)
Time to explain the basic pieces of explain output.
Since we have a very simple query we only have one line grouping in our query plan. This line is called a node in the PostgreSQL technical documentation because it represents a node in a tree. As you will see later, more involved trees are usually generated.
Now let's dissect the information in the node - each number below corresponds to the red number on the image.
(1) Seq Scan - this stands for sequential scan. This first part of the information says the type of operation the node will perform. Since we are pulling back the whole table there is no use of an index or a filter and the query planner is going to use a simple sequential scan to return all rows in the table. Another name for a sequential scan is a Full Table Scan.
The next cost section has two parts.
(2) This first number (0.00) represents the cost, in arbitrary units, required before this node can start returning results. Since there is only one node in this query plan it can start right away. The units for this figure are "in arbitrary units determined by the planner's cost parameters". By default, 1 unit is the cost to fetch one disk page during a squential scan. You can read more in the explain documentation
(3) The second number represents the total estimated cost for this node to complete. Again, the units here are in the same arbitrary units given above. It is important to note that this cost is NOT comparable between queries.
(4) Rows represents the number of estimated rows returned when the node is finished. In this case, since we are returning every row it is the same as the estimated table size. Note this is the maximum rows that might be returned; the node can return earlier if it meets some criteria such as a LIMIT.
(5) The final number is the estimated average size, in bytes, for each row returned from the node. Since we are returning an entire row we get 712 bytes. If you want to try something change the select to
select episode_id from ... and watch the return row size decrease.
All of these estimates on the node apply per time the node is executed. You will see why this is important later when we get to query plan that contain loops.
You have seen how we can get some basic information regarding what the query planner has chosen as the "optimal" tree of execution. In the next section we will look at what happens when we ask EXPLAIN to run the query and give us actual timing from running the plan.