PostgreSQL Explain for Query Performance Tuning

Problem

In this tip I will show how to analyze query execution plans for tuning and optimizing PostgreSQL queries.

Solution

A typical task DBAs and Developers perform is optimizing query performance. The first step, after identifying troublesome queries using a tool like the pg_stat_statements view, is to look at the execution plan to determine what is happening and how to improve.

In PostgreSQL this can be done using EXPLAIN or using third-party tools which use the same process to gather query execution data.

Find Queries to Improve

The query below shows how to find the most time consuming queries. See this article for more info PostgreSQL Monitoring with pg_stat_statements.

--MSSQLTips.com
 
--Top 20 slowest queries
SELECT userid::regrole,datname as dbname,  substring(query, 1, 100) AS short_query,
round(total_exec_time::numeric, 2) AS total_exec_time,calls,round(mean_exec_time::numeric, 2) AS mean,
round((100 * total_exec_time /sum(total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM    pg_stat_statements
inner join pg_database
on dbid=oid
ORDER BY total_exec_time DESC
limit 20;

Here is the output. We will examine the first query in the output below.

EXPLAIN statement in PostgreSQL

Let’s dive in and start with the basics of EXPLAIN.

The EXPLAIN feature has been available since version 16 of PostgreSQL and returns a query plan for the query statement. This is an idea of what the plan might being doing (estimated) and not the actual plan, because we are not providing actual parameter values or executing the query.

We will look at the first query from the above output and use EXPLAIN to analyze and see how we can improve peformance.

We also used the generic_plan parameter because this query contains parameters. Instead of using actual values we just use paramter placeholders. I wrote about this in my tip about PREPARED STATEMENTS: PostgreSQL Prepared Statements as a Performance Improvement. If we did not use this option, it would produce an error.

--MSSQLTips.com
 
explain (generic_plan) UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;

Here is the output from the above command.

So for our example, we have three lines of output:

An UPDATE on pgbench_branches with an estimated cost and rows involved as well as width of the rows
Then the access method, in this case an Index Scan using the primary key pgbench_branches_pkey
and, finally the condition.

In all these different steps we have four numbers in parentheses:

Estimated start-up cost
Estimated total cost
Estimated number of rows output by this plan node
Estimated average width (in bytes) of rows output by this plan node

It is important to note that cost is an arbitrary number, based on a projected resource usage for a plan, these cost estimates rely on table statistics, gathered with ANALYZE (or AUTOANALYZE that is automatically run together with AUTOVACUUM) see my tip on this topic: PostgreSQL VACUUM, AUTOVACUUM and ANALYZE Processes.

Costs also rely on config parameters such as seq_page_cost and random_page_cost, in which we can specify values different than the default, in order to influence the optimizer to choose a different plan. In fact the optimizer determines the most efficient execution plan based on these costs. I would like to emphasize that these costs are primarily based on table statistics.

EXPLAIN Analyze in PostgreSQL

EXPLAIN ANALYZE gives you the real execution plan of the query, but, in order to do that, it executes the query, so we must pay attention if the statement is an INSERT, UPDATE or DELETE because it will be executed and the data will be changed!

Let’s look at at an example using EXPLAIN ANALYZE with a SELECT query.

--MSSQLTips.com
 
EXPLAIN ANALYZE 
SELECT abalance 
FROM pgbench_accounts 
WHERE aid = 10000;

Below we can see the output of this SELECT statement which is similar to the output we saw above.

An index scan occurs on the table
The index condition is applied
The time it takes to prepare the execution plan
The time it takes to execute the query

EXPLAIN Analyze for Insert, Update, or Delete

In order to avoid the effects of executing the statements we can use a small trick using BEGIN and ROLLBACK:

--MSSQLTips.com
 
begin;
explain analyze
UPDATE pgbench_branches 
SET bbalance = bbalance + 1000 
WHERE bid = 10000;

Here is the plan for the above query.

We can see the steps above for executing this query.

We can then use rollback to undo the UPDATE transaction.

--MSSQLTips.com
 
rollback;

As you may have noticed, with the ANALYZE option we have the actual time spent by the optimizer for creating the execution plan and the real execution time. In both cases we notice immediately that the planning generation phase took more time than the actual execution of the statement. Again this is something that we can partially avoid when using PREPARED statements.

Note: ANALYZE and GENERIC_PLAN cannot be used together as EXPLAIN options.

BUFFERS with ANALYZE

Another important option of EXPLAIN is BUFFERS, normally used in conjunction with ANALYZE in order to see information on I/O reads and writes times. An important parameter track_io_timing is enabled as I explained in my previous tip speaking about pg_stat_io: PostgreSQL Monitoring with pg_stat_statements

Let’s look at an example.

--MSSQLTips.com
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT abalance 
FROM pgbench_accounts 
WHERE aid = 10000;

With this option, we can now see IO and buffer related information.

If we issue the same statement again in the same session, we have a different result. As you can see below, this time PostgreSQL doesn’t need to read from disk and we have a hit in memory so no I/O, plus less time for planning. Also, we can see the overall execution time is faster for this second run.

WAL with ANALYZE

Another option that can be used with ANALYZE is WAL. WAL stands for Write-Ahead Logging.

In order to have WAL generated, the statement must be a DML statement where data is changed. Also, if no new WAL record is added then we will not have any additional info we use the WAL option.

--MSSQLTips.com
 
explain (analyze, wal)
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) 
VALUES (1, 2, 3, 4, CURRENT_TIMESTAMP);

Below we can see there was one record impacted.

Additional Output Formats

Above we saw how we can get the output in a text format, there are also other output options such as XML and JSON.

FORMAT XML

Here is the option to get the output in an XML format.

--MSSQLTips.com
 
EXPLAIN (ANALYZE, FORMAT XML)
SELECT abalance 
FROM pgbench_accounts 
WHERE aid = 10000;

This output can then be used for exporting the result to different third party plan visualizers that can give us a graphical view of the plan.

FORMAT JSON

Here is the option to get the output in a JSON format.

--MSSQLTips.com
 
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT abalance 
FROM pgbench_accounts 
WHERE aid = 10000;

We can see that the query results switches automatically to Explain and that we have a graphical format of the plan.

Using pgAdmin for EXPLAIN and EXPLAIN ANALYZE

If we use pgAdmin we can also get query plan information.

There are two icons in pgAdmin, one with an E (EXPLAIN) and the other with an histogram (EXPLAIN ANALYZE). You could also use the shortcuts F7 or Shift+F7.

Graphical Tab

Here is the graphical query plan output.

Analysis Tab

Here is the text version of the steps.

Statistics Tab

Here are additional statistics.

These three screens give us basically the same information as the plain text format, but presented in a more readable way. Obviously this is a very simple query, so the output is quite simple.

Deeper Dive into Graphical Query Plan Features

To give you an idea of the visualization of a more complex query plan, we can do another example with the Chinook database. We will use a query with a window function from one of my tips https://www.mssqltips.com/sqlservertip/6796/sql-window-functions-sql-server-oracle-postgresql/.

Using Explain

Here is a look at a query using EXPLAIN.

pgadmin explain graphical query plan output

Clicking on the InvoiceLine table icon we can see more information:

We can see additoinal info on the Analysis tab.

Using Explain Analyze

We can also take a look at the EXPLAIN ANALYZE visualization and can see additional details:

In which we can see that we have the actual rows scanned in the InvoiceLine table, as well as the actual statistics of the execution plan:

Using ANALYZE with FORMAT JSON

If we need more options in addition to ANALYZE and we still want to use the pgAdmin plan visualization tool, we can use the FORMAT JSON trick that I showed before:

--MSSQLTips.com
 
explain (analyze, buffers, format json)
select 
   distinct "Genre"."Name", 
   cast(SUM("Quantity") OVER (PARTITION BY "Genre"."Name") /cast(SUM("Quantity") OVER () as decimal (8,3))*100 as decimal (5,3)) as Perc
from "InvoiceLine"
inner join "Track" on "InvoiceLine"."TrackId"="Track"."TrackId"
inner join "Genre" on "Track"."GenreId"="Genre"."GenreId"
order by Perc desc;

We have all the info about buffers, rows and timings by just clicking on each icon.

I particularly like the Analysis tab which has a whole host of well-presented info, especially regarding the various timings of each phase.

Please note the difference between the actual and plan (estimated) rows. This is something that should be looked at in order to optimize a plan (or find problems).

Next Steps

In this tip, we reviewed the most important options of the EXPLAIN statement in order to get the execution plan for a query and visualize with pgAdmin. There are also other third party execution plan visualizers for PostgreSQL which you can research.

As always some links to the official documentation:

And some to other interesting execution plan related tips:

Resources

Community

Subscribe

PostgreSQL EXPLAIN for Query Tuning