February 24, 2025
Summary: In this tutorial, you have learned how to use the PostgreSQL EXPLAIN statement to display the execution plan of a statement.
Table of Contents
Introduction to PostgreSQL EXPLAIN statement
The EXPLAIN statement returns the execution plan which PostgreSQL planner generates for a given statement.
The EXPLAIN shows how tables involved in a statement will be scanned by index scan or sequential scan, etc., and if multiple tables are used, what kind of join algorithm will be used.
The most important and useful information that the EXPLAIN statement returns are start-cost before the first row can be returned and the total cost to return the complete result set.
The following shows the syntax of the EXPLAIN statement:
EXPLAIN [ ( option [, ...] ) ] sql_statement;
where option can be one of the following:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
The boolean specifies whether the selected option should be turned on or off. You can use TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. If you omit the boolean, it defaults to ON.
ANALYZE
The ANALYZE option causes the sql_statement to be executed first and then actual run-time statistics in the returned information including total elapsed time expended within each plan node and the number of rows it actually returned.
The ANALYZE statement actually executes the SQL statement and discards the output information, therefore, if you want to analyze any statement such as INSERT, UPDATE, or DELETE without affecting the data, you should wrap the EXPLAIN ANALYZE in a transaction, as follows:
BEGIN;
EXPLAIN ANALYZE sql_statement;
ROLLBACK;
VERBOSE
The VERBOSE parameter allows you to show additional information regarding the plan. This parameter sets to FALSE by default.
COSTS
The COSTS option includes the estimated startup and total costs of each plan node, as well as the estimated number of rows and the estimated width of each row in the query plan. The COSTS defaults to TRUE.
SETTINGS
Include information on configuration parameters. Specifically, include settings affecting query planning with value different from the built-in default value. This parameter defaults to FALSE.
BUFFERS
This parameter adds information to the buffer usage. BUFFERS only can be used when ANALYZE is enabled. By default, the BUFFERS parameter set to FALSE.
TIMING
This parameter includes the actual startup time and time spent in each node in the output. The TIMING defaults to TRUE and it may only be used when ANALYZE is enabled.
SUMMARY
The SUMMARY parameter adds summary information such as total timing after the query plan. Note that when ANALYZE option is used, the summary information is included by default.
FORMAT
Specify the output format of the query plan such as TEXT, XML, JSON, and YAML. This parameter is set to TEXT by default.
PostgreSQL EXPLAIN examples
The following statement shows the plan for a simple query on the film table:
EXPLAIN SELECT * FROM film;
The output is as follows:
QUERY PLAN
----------------------------------------------------------
Seq Scan on film (cost=0.00..64.00 rows=1000 width=384)
(1 row)
The following example shows the plan for a query that returns a film by a specific film_id.
EXPLAIN SELECT * FROM film WHERE film_id = 100;
Here is the output:
QUERY PLAN
------------------------------------------------------------------------
Index Scan using film_pkey on film (cost=0.28..8.29 rows=1 width=384)
Index Cond: (film_id = 100)
(2 rows)
Because the film_id is indexed, the statement returned a different plan. In the output, the planner used an index scan instead of a sequential scan on the film table.
To suppress the cost, you can use the COSTS option:
EXPLAIN (COSTS FALSE)
SELECT *
FROM
film
WHERE
film_id = 100;
QUERY PLAN
------------------------------------
Index Scan using film_pkey on film
Index Cond: (film_id = 100)
(2 rows)
The following example displays the plan for a query that uses an aggregate function:
EXPLAIN SELECT COUNT(*) FROM film;
The output is:
QUERY PLAN
--------------------------------------------------------------
Aggregate (cost=66.50..66.51 rows=1 width=8)
-> Seq Scan on film (cost=0.00..64.00 rows=1000 width=0)
(2 rows)
EXPLAIN ANALYZE examples
The following example returns a plan for a statement that joins multiple tables:
EXPLAIN
SELECT
f.film_id,
title,
name category_name
FROM
film f
INNER JOIN film_category fc
ON fc.film_id = f.film_id
INNER JOIN category c
ON c.category_id = fc.category_id
ORDER BY
title;
The output is:
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=149.64..152.14 rows=1000 width=87)
Sort Key: f.title
-> Hash Join (cost=77.86..99.81 rows=1000 width=87)
Hash Cond: (fc.category_id = c.category_id)
-> Hash Join (cost=76.50..95.14 rows=1000 width=21)
Hash Cond: (fc.film_id = f.film_id)
-> Seq Scan on film_category fc (cost=0.00..16.00 rows=1000 width=4)
-> Hash (cost=64.00..64.00 rows=1000 width=19)
-> Seq Scan on film f (cost=0.00..64.00 rows=1000 width=19)
-> Hash (cost=1.16..1.16 rows=16 width=72)
-> Seq Scan on category c (cost=0.00..1.16 rows=16 width=72)
(11 rows)
To add the actual runtime statistics to the output, you need to execute the statement using the ANALYZE option:
EXPLAIN ANALYZE
SELECT
f.film_id,
title,
name category_name
FROM
film f
INNER JOIN film_category fc
ON fc.film_id = f.film_id
INNER JOIN category c
ON c.category_id = fc.category_id
ORDER BY
title;
Here is the output:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=149.64..152.14 rows=1000 width=87) (actual time=1.373..1.406 rows=1000 loops=1)
Sort Key: f.title
Sort Method: quicksort Memory: 93kB
-> Hash Join (cost=77.86..99.81 rows=1000 width=87) (actual time=0.317..0.762 rows=1000 loops=1)
Hash Cond: (fc.category_id = c.category_id)
-> Hash Join (cost=76.50..95.14 rows=1000 width=21) (actual time=0.294..0.578 rows=1000 loops=1)
Hash Cond: (fc.film_id = f.film_id)
-> Seq Scan on film_category fc (cost=0.00..16.00 rows=1000 width=4) (actual time=0.003..0.074 rows=1000 loops=1)
-> Hash (cost=64.00..64.00 rows=1000 width=19) (actual time=0.286..0.287 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 59kB
-> Seq Scan on film f (cost=0.00..64.00 rows=1000 width=19) (actual time=0.003..0.172 rows=1000 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=72) (actual time=0.017..0.017 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on category c (cost=0.00..1.16 rows=16 width=72) (actual time=0.011..0.013 rows=16 loops=1)
Planning Time: 0.262 ms
Execution Time: 1.456 ms
(16 rows)
EXPLAIN SETTINGS examples
From PostgreSQL 12 on, you can ask EXPLAIN to display any setting that has been changed and influenced the decision on which plan to choose. This might be optimizer parameters as here, but this might also be others when they differ from the global setting:
SET work_mem = '64MB';
EXPLAIN (analyze, settings)
SELECT count(*) FROM pg_class;
Output:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=20.01..20.02 rows=1 width=8) (actual time=0.066..0.066 rows=1 loops=1)
-> Seq Scan on pg_class (cost=0.00..18.81 rows=481 width=0) (actual time=0.009..0.044 rows=481 loops=1)
Settings: work_mem = '64MB'
Planning Time: 0.062 ms
Execution Time: 0.087 ms
(5 rows)
Let’s disable the sequential scan, and see how it affects the query plan:
SET enable_seqscan = off;
EXPLAIN (analyze, settings)
SELECT count(*) FROM pg_class;
Output:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=29.43..29.44 rows=1 width=8) (actual time=0.128..0.129 rows=1 loops=1)
-> Index Only Scan using pg_class_tblspc_relfilenode_index on pg_class (cost=0.15..28.23 rows=481 width=0) (actual time=0.045..0.105 rows=481 loops=1)
Heap Fetches: 145
Settings: enable_seqscan = 'off', work_mem = '64MB'
Planning Time: 0.065 ms
Execution Time: 0.149 ms
(6 rows)
By using the “settings” switch with analyze, you can be sure on what was changed from the global settings so it is much easier to reproduce the issue and to see what’s going on.
In this tutorial, you have learned how to use the PostgreSQL EXPLAIN statement to show the query’s plan for a specific SQL statement.