It is no secret that most, if not all, modern software products critically rely on data to provide value for users. One of the most commonly used tools for interacting with this data, SQL, is at face value quite approachable to most developers. It is not a difficult task to glean simple information from a database; however, doing so in the most efficient manner possible is a completely different story. To solve this issue, query tuning is performed. This tuning is used to help improve the performance and efficiency of the queries suppling necessary data. My hope is that this blog provides you a basic understanding of how to read a query plan to tune queries and improve performance.

For the purposes of this blog, I will be walking through a sample dataset using PostgreSQL comprised of DVD rental data. The dataset and corresponding ERD can be found here. While not everything in this post will correspond 1:1 to all scenarios, the ideas will hopefully provide some insight.

The most accessible and useful tool we have to investigate how a query is being executed is the EXPLAIN statement, which allows us to peek into how the server thinks it should execute the supplied query. One way to make our analysis even more insightful is by including the ANALYZE option. In doing so we are telling our server to actually execute the query, and return information on exactly how it did so. This is performed by prepending EXPLAIN ANALYZE to your query. Here is an example using our sample data set:

EXPLAIN ANALYZE
SELECT *
FROM FILM
LIMIT 10
Limit (cost=0.00..0.64
rows=10 width=384) (actual
time=0.011..0.014 rows=10
loops=1)
-> Seq Scan on film
(cost=0.00..64.00 rows=1000
width=384) (actual time=0.
010..0.011 rows=10 loops=1)
Planning Time: 0.076 ms
Execution Time: 0.033 ms

The EXPLAIN ANALYZE keyword provides a detailed look into how our server is performing the query. The tree structured output is broken into “nodes”, each denoted by an arrow (->). Each of these nodes will include valuable information into understanding how the query is executed. The first set of parentheses tells us estimated values of the following:
(cost=0.00..64.00 rows=1000 width=384) “cost” (cost=0.00..0.64) tells the start-up cost, measured in cost units that are arbitrary, but conventionally mean disk page fetches,
as well as the total cost of a node.

“rows” (rows=10) represents the number of rows output by this plan node.
“width” (width=384) shows us the average width (in bytes) of rows output by this plan node.

The second set of parenthesis gives us actual values of the following:
(actual time=0.010..0.011 rows=10 loops=1)
“actual time” (actual time=0.011..0.014) gives us the startup time of the node, or the time it takes to retrieve the first record, as
well as the total time for a node. (the second time value being the more important of the two)
“rows” (rows=10) in this context shows the actual number of rows this node was executed on.

The “loops” (loops=1) gives us the number of times the node was executed.

The execution plan also includes statistics around planning and execution times. Planning Time reports the amount of time the server takes to decide how it wants to execute the query. Execution Time tells us how long the query took from beginning to end of query execution.

We can see the single node executed here was a sequence scan on the film table (Seq Scan on film), meaning that in order to provide this information, PostgreSQL needed to scan every record in the table. In the context of this query, that’s nothing to worry about, but what if we wanted to get information on a specific record? There has to be a better way.

Let’s take a look at a slightly more realistic example. Say we want to retrieve the record for the film ‘Legend Jedi’. We could do this and gather the query plan by doing the following:

EXPLAIN ANALYZE
SELECT *
FROM FILM
WHERE TITLE = 'Legend Jedi'
Index Scan using idx_title on
film (cost=0.28..8.29 rows=1
width=384) (actual time=0.
426..0.427 rows=1 loops=1)
Index Cond: ((title)::text
= 'Legend Jedi'::text)
Planning Time: 1.256 ms
Execution Time: 0.455 ms

The output shows us that rather than using a sequence scan, the server utilized a preexisting index idx_title on the TITLE field to find the exact record we were interested in. To show the role that this index plays in query execution, let’s drop the index and run that query again.

DROP INDEX idx_title
EXPLAIN ANALYZE
SELECT *
FROM FILM
WHERE TITLE = 'Legend Jedi'
Seq Scan on film (cost=0.00..
66.50 rows=1 width=384)
(actual time=0.441..0.804
rows=1 loops=1)
Filter: ((title)::text =
'Legend Jedi'::text)
Rows Removed by Filter: 999
Planning Time: 1.018 ms
Execution Time: 0.884 ms

After removing the index we can see that the planner executed a full sequence scan, looking over the entire table until it found the record we requested, and filtered out irrelevant records. One interesting thing to note is that the planning time actually went down when removing the index. This is likely due to the fact that PostgreSQL didn’t need to locate an index to use in executing the query.

With Index

Index Scan using idx_title on
film (cost=0.28..8.29 rows=1
width=384) (actual time=0.
426..0.427 rows=1 loops=1)
Index Cond: ((title)::text
= 'Legend Jedi'::text)
Planning Time: 1.256 ms
Execution Time: 0.455 ms

Without Index

Seq Scan on film (cost=0.00..
66.50 rows=1 width=384)
(actual time=0.441..0.804
rows=1 loops=1)
Filter: ((title)::text =
'Legend Jedi'::text)
Rows Removed by Filter: 999
Planning Time: 1.018 ms
Execution Time: 0.884 ms

Comparing the two query plans, we see that the indexed query took .455 ms, while the non-indexed query took almost twice as long, .884 ms.

While this instance doesn’t provide a noticeable difference in time, the message is clear: Utilizing indexes can drastically improve query
performance. A good rule is to use indexes on fields included in filter and join criteria, but there are many other circumstances where indexes can provide a benefit.

Now, say we want to dig deeper and find all the actors to star in ‘Legend Jedi’ we can see further examples of using the query plan to identify possible ways to improve query performance.

EXPLAIN ANALYZE
SELECT FIRST_NAME, LAST_NAME
FROM FILM
LEFT JOIN FILM_ACTOR
ON FILM.FILM_ID =
FILM_ACTOR.FILM_ID
LEFT JOIN ACTOR
ON ACTOR.ACTOR_ID =
FILM_ACTOR.ACTOR_ID
WHERE TITLE = 'Legend Jedi'
Nested Loop Left Join
(cost=4.74..27.43 rows=5
width=13) (actual time=0.107..
0.118 rows=4 loops=1)
-> Nested Loop Left Join
(cost=4.60..26.60 rows=5
width=2) (actual time=0.099..
0.104 rows=4 loops=1)
-> Index Scan using
idx_title on film (cost=0.
28..8.29 rows=1 width=4)
(actual time=0.062..0.063
rows=1 loops=1)
Index Cond:
((title)::text = 'Legend
Jedi'::text)
-> Bitmap Heap Scan
on film_actor (cost=4.32..
18.26 rows=5 width=4) (actual
time=0.033..0.036 rows=4
loops=1)
Recheck Cond:
(film.film_id = film_id)
Heap Blocks:
exact=4
-> Bitmap
Index Scan on idx_fk_film_id
(cost=0.00..4.32 rows=5
width=0) (actual time=0.028..
0.028 rows=4 loops=1)
Index
Cond: (film_id = film.film_id)
-> Index Scan using
actor_pkey on actor (cost=0.
14..0.17 rows=1 width=17)
(actual time=0.003..0.003
rows=1 loops=4)
Index Cond: (actor_id
= film_actor.actor_id)
Planning Time: 1.588 ms
Execution Time: 0.185 ms

While this seems like an efficient query, it does have some room for improvement. Looking at the ERD for our dataset, we can see that there is a 1:1 relationship between FILM, the bridge table FILM_ACTOR, and ACTOR.

Since we are only interested in records from actor that relate directly to the ‘Legend Jedi’ record from the FILM table, we can use inner joins rather than left joins. Many people use left joins as a default when there are no obvious reasons not to in order to make associations between tables. If we swap the left joins for inner joins we get a significantly faster execution plan:

EXPLAIN ANALYZE
SELECT FIRST_NAME, LAST_NAME
FROM FILM
INNER JOIN FILM_ACTOR
ON FILM.FILM_ID =
FILM_ACTOR.FILM_ID
INNER JOIN ACTOR
ON ACTOR.ACTOR_ID =
FILM_ACTOR.ACTOR_ID
WHERE TITLE = 'Legend Jedi'
Nested Loop (cost=4.74..
27.43 rows=5 width=13)
(actual time=0.033..0.043
rows=4 loops=1)
-> Nested Loop (cost=4.
60..26.60 rows=5 width=2)
(actual time=0.027..0.032
rows=4 loops=1)
-> Index Scan using
idx_title on film (cost=0.
28..8.29 rows=1 width=4)
(actual time=0.007..0.008
rows=1 loops=1)
Index Cond:
((title)::text = 'Legend
Jedi'::text)
-> Bitmap Heap Scan
on film_actor (cost=4.32..
18.26 rows=5 width=4) (actual
time=0.017..0.020 rows=4
loops=1)
Recheck Cond:
(film_id = film.film_id)
Heap Blocks:
exact=4
-> Bitmap
Index Scan on idx_fk_film_id
(cost=0.00..4.32 rows=5
width=0) (actual time=0.013..
0.013 rows=4 loops=1)
Index
Cond: (film_id = film.film_id)
-> Index Scan using
actor_pkey on actor (cost=0.
14..0.17 rows=1 width=17)
(actual time=0.002..0.002
rows=1 loops=4)
Index Cond: (actor_id
= film_actor.actor_id)
Planning Time: 0.336 ms
Execution Time: 0.079 ms

We can easily see that replacing left joins with inner joins in this example leads to a planning time 1/5th the original, and an execution time which is 40% that of the query using left joins.

Using Left Joins

Nested Loop (cost=4.74..
27.43 rows=5 width=13)
(actual time=0.033..0.043
rows=4 loops=1)
-> Nested Loop (cost=4.
60..26.60 rows=5 width=2)
(actual time=0.027..0.032
rows=4 loops=1)
-> Index Scan using
idx_title on film (cost=0.
28..8.29 rows=1 width=4)
(actual time=0.007..0.008
rows=1 loops=1)
Index Cond:
((title)::text = 'Legend
Jedi'::text)
-> Bitmap Heap Scan
on film_actor (cost=4.32..
18.26 rows=5 width=4) (actual
time=0.017..0.020 rows=4
loops=1)
Recheck Cond:
(film_id = film.film_id)
Heap Blocks:
exact=4
-> Bitmap
Index Scan on idx_fk_film_id
(cost=0.00..4.32 rows=5
width=0) (actual time=0.013..
0.013 rows=4 loops=1)
Index
Cond: (film_id = film.film_id)
-> Index Scan using
actor_pkey on actor (cost=0.
14..0.17 rows=1 width=17)
(actual time=0.002..0.002
rows=1 loops=4)
Index Cond: (actor_id
= film_actor.actor_id)
Planning Time: 0.336 ms
Execution Time: 0.079 ms

Using Inner Joins

Nested Loop (cost=4.74..
27.43 rows=5 width=13)
(actual time=0.033..0.043
rows=4 loops=1)
-> Nested Loop (cost=4.
60..26.60 rows=5 width=2)
(actual time=0.027..0.032
rows=4 loops=1)
-> Index Scan using
idx_title on film (cost=0.
28..8.29 rows=1 width=4)
(actual time=0.007..0.008
rows=1 loops=1)
Index Cond:
((title)::text = 'Legend
Jedi'::text)
-> Bitmap Heap Scan
on film_actor (cost=4.32..
18.26 rows=5 width=4) (actual
time=0.017..0.020 rows=4
loops=1)
Recheck Cond:
(film_id = film.film_id)
Heap Blocks:
exact=4
-> Bitmap
Index Scan on idx_fk_film_id
(cost=0.00..4.32 rows=5
width=0) (actual time=0.013..
0.013 rows=4 loops=1)
Index
Cond: (film_id = film.film_id)
-> Index Scan using
actor_pkey on actor (cost=0.
14..0.17 rows=1 width=17)
(actual time=0.002..0.002
rows=1 loops=4)
Index Cond: (actor_id
= film_actor.actor_id)
Planning Time: 0.336 ms
Execution Time: 0.079 ms

This may seem overly simple, but effective query tuning is often the result of many small changes combining to achieve the intended result.

In this last example, we’ll cover another powerful use of query plans: filtering https://www.postgresql.org/docs/9.5/functions-aggregate.html and https://www.postgresql.org/docs/9.5/functions-window.html. While both can be exceptionally helpful, they both have a time and place. It is important to know when to use each option and when they will result in the best outcome. Imagine we want to look up our longest overdue movie, who checked it out, and when:

EXPLAIN ANALYZE
SELECT TITLE, RENTAL_DATE,
CUSTOMER_NAME
FROM (
SELECT TITLE,
RENTAL_DATE,
FIRST_NAME || ' ' ||
LAST_NAME AS CUSTOMER_NAME,
ROW_NUMBER() OVER (ORDER BY
RENTAL_DATE) AS ROW
FROM RENTAL
LEFT JOIN INVENTORY
ON INVENTORY.
INVENTORY_ID = RENTAL.
INVENTORY_ID
LEFT JOIN FILM
ON FILM.
FILM_ID = INVENTORY.FILM_ID
LEFT JOIN CUSTOMER
ON CUSTOMER.
CUSTOMER_ID = RENTAL.
CUSTOMER_ID
WHERE RETURN_DATE IS NULL
) RENTAL_INFO
WHERE ROW = 1
Subquery Scan on rental_info
(cost=518.24..524.65 rows=1
width=55) (actual time=4.480..
4.653 rows=1 loops=1)
Filter: (rental_info.row =
1)
Rows Removed by Filter: 182
-> WindowAgg (cost=518.
24..522.36 rows=183 width=63)
(actual time=4.478..4.629
rows=183 loops=1)
-> Sort (cost=518.
24..518.70 rows=183 width=36)
(actual time=4.468..4.490
rows=183 loops=1)
Sort Key:
rental.rental_date
Sort Method:
quicksort Memory: 39kB
-> Hash Left
Join (cost=335.48..511.36
rows=183 width=36) (actual
time=1.678..4.397 rows=183
loops=1)
Hash
Cond: (rental.customer_id =
customer.customer_id)
->
Nested Loop Left Join
(cost=313.00..488.41 rows=183
width=25) (actual time=1.328..
3.960 rows=183 loops=1)
->
Hash Right Join (cost=312.
73..425.45 rows=183 width=12)
(actual time=1.322..2.983
rows=183 loops=1)
Hash Cond: (inventory.
inventory_id = rental.
inventory_id)
-> Seq Scan on inventory
(cost=0.00..70.81 rows=4581
width=6) (actual time=0.004..
0.685 rows=4581 loops=1)
-> Hash (cost=310.44..
310.44 rows=183 width=14)
(actual time=1.304..1.305rows=183 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 17kB
-> Seq Scan on rental
(cost=0.00..310.44 rows=183
width=14) (actual time=0.900..
1.270 rows=183 loops=1)
Filter: (return_date IS NULL)
Rows Removed by Filter: 15861
->
Index Scan using film_pkey on
film (cost=0.28..0.34 rows=1
width=19) (actual time=0.005..
0.005 rows=1 loops=183)
Index Cond: (film_id =
inventory.film_id)
-> Hash
(cost=14.99..14.99 rows=599
width=17) (actual time=0.318..
0.319 rows=599 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 39kB
->
Seq Scan on customer (cost=0.
00..14.99 rows=599 width=17)
(actual time=0.011..0.202
rows=599 loops=1)
Planning Time: 0.633 ms
Execution Time: 4.727 ms

The query returns the result:

TITLE, RENTAL_DATE, CUSTOMER_NAME
Academy Dinosaur, 8/21/2005, Dwayne Olvera

While the query plan looks alright, we can see we have some costly joins especially related to the subquery, one example being
-> Hash Left Join (cost=335.48..511.36 rows=183 width=36) (actual time=1.678..4.397 rows=183 loops=1)
Hash Cond: (rental.customer_id = customer.customer_id)

Looking at the actual time section (actual time=1.678..4.397) we see that this node takes 2.719 ms (4.397-1.678) from processing the first record, to finish processing the 183rd. If we rethink our query to filter the dataset down before performing the many joins we need to achieve our answer, the time it takes to execute this node will surely decrease. Notice that in the query above we are only filtering on movies without a return date. That means we join from RENTAL to CUSTOMER for every rental without a RETURN_DATE. Let’s try filtering down to the longest outstanding rental before we execute all the joins.

Original Query

EXPLAIN ANALYZE
SELECT TITLE, RENTAL_DATE,
CUSTOMER_NAME
FROM (
SELECT TITLE,
RENTAL_DATE,
FIRST_NAME || ' ' ||
LAST_NAME AS CUSTOMER_NAME,
ROW_NUMBER() OVER (ORDER BY
RENTAL_DATE) AS ROW
FROM RENTAL
LEFT JOIN INVENTORY
ON INVENTORY.
INVENTORY_ID = RENTAL.
INVENTORY_ID
LEFT JOIN FILM
ON FILM.
FILM_ID = INVENTORY.FILM_ID
LEFT JOIN CUSTOMER
ON CUSTOMER.
CUSTOMER_ID = RENTAL.
CUSTOMER_ID
WHERE RETURN_DATE IS NULL
) RENTAL_INFO
WHERE ROW = 1

Refactored Query

EXPLAIN ANALYZE
SELECT TITLE, RENTAL_DATE,
FIRST_NAME || ' ' ||
LAST_NAME AS CUSTOMER_NAME
FROM RENTAL
LEFT JOIN INVENTORY ON
INVENTORY.INVENTORY_ID =
RENTAL.INVENTORY_ID
LEFT JOIN FILM ON FILM.
FILM_ID = INVENTORY.FILM_ID
LEFT JOIN CUSTOMER ON
CUSTOMER.CUSTOMER_ID = RENTAL.
CUSTOMER_ID
WHERE RETURN_DATE IS NULL
AND RENTAL_DATE = (SELECT MIN
(RENTAL_DATE) FROM RENTAL
WHERE RETURN_DATE IS NULL)
Nested Loop Left Join
(cost=6.26..30.41 rows=1
width=55) (actual time=2.022..
2.025 rows=1 loops=1)
InitPlan 2 (returns $1)
-> Result (cost=5.13..
5.14 rows=1 width=8) (actual
time=1.984..1.985 rows=1
loops=1)
InitPlan 1 (returns
$0)
-> Limit
(cost=0.29..5.13 rows=1
width=8) (actual time=1.982..
1.983 rows=1 loops=1)
-> Index
Scan using
idx_unq_rental_rental_date_inv
entory_id_customer_id on
rental rental_1 (cost=0.29..
886.73 rows=183 width=8)
(actual time=1.981..1.982
rows=1 loops=1)
Index
Cond: (rental_date IS NOT
NULL)
Filter: (return_date IS NULL)
Rows
Removed by Filter: 13987
-> Nested Loop Left Join
(cost=0.84..16.95 rows=1
width=25) (actual time=2.015..
2.016 rows=1 loops=1)
-> Nested Loop Left
Join (cost=0.57..16.61
rows=1 width=12) (actual
time=2.006..2.007 rows=1
loops=1)
-> Index Scan
using
idx_unq_rental_rental_date_inv
entory_id_customer_id on
rental (cost=0.29..8.30
rows=1 width=14) (actual
time=1.992..1.992 rows=1
loops=1)
Index
Cond: (rental_date = $1)
Filter:
(return_date IS NULL)
-> Index Scan
using inventory_pkey on
inventory (cost=0.28..8.30
rows=1 width=6) (actual
time=0.009..0.009 rows=1
loops=1)
Index
Cond: (inventory_id = rental.
inventory_id)
-> Index Scan using
film_pkey on film (cost=0.
28..0.34 rows=1 width=19)
(actual time=0.007..0.007
rows=1 loops=1)
Index Cond:
(film_id = inventory.film_id)
-> Index Scan using
customer_pkey on customer
(cost=0.28..8.29 rows=1
width=17) (actual time=0.005..
0.005 rows=1 loops=1)
Index Cond:
(customer_id = rental.
customer_id)
Planning Time: 0.348 ms
Execution Time: 2.061 ms

After reworking the query we can see that changing our filter criteria cut the total query time in half by including AND RENTAL_DATE =
(SELECT MIN(RENTAL_DATE) FROM RENTAL WHERE RETURN_DATE IS NULL) This forced our server to only perform joins on the one
record where RETURN_DATE IS NULL and RENTAL_DATE = (SELECT MIN(RENTAL_DATE) FROM RENTAL WHERE RETURN_DATE IS
NULL), rather than performing multiple loops of an operation, or executing logic across multiple rows. This change in query structure also allows our server to take advantage of indexes on customer_id which accounts for significant performance improvements.

Should you come across new problems to solve regarding query construction, Performance Tuning Queries in PostgreSQL by Brady Holt is a great article I use as a guideline. I hope this post helps equip you with the knowledge of how to read a query plan, use it to tune queries, and improve performance.

If this article is helpful, we’d love to hear from you.

What other resources have you found for optimizing queries? Leave a comment below and let us know.

Subscribe for Updates

Top 5 Reasons Why You Should Subscribe

  • #1

    Stay up-to-date on industry news and trends

  • #2

    Learn about claims management best practices

  • #3

    Gain insights into customer behavior and preferences

  • #4

    Discover new technologies and innovations

  • #5

    Engage with a community of industry professionals

STREAMLINE

Appraisals

Reduce cycle time and improve appraisal accuracy

SIMPLIFY

Claims

Claims management with our modular, end-to-end solutions

SCALE

Payments

Deliver payments effortlessly with automated tools and no-code workflows