How to Use the PostgreSQL EXPLAIN Statement

Introduction

Each time that PostgreSQL receives a query, it formulates a query plan for it. Selecting a plan that aligns with the query structure is essential for optimizing performance, so PostgreSQL’s built-in planner attempts to choose the best plan for each query. You can view the query plan that PostgreSQL devises for a query using the EXPLAIN statement. In this article, we’ll take a closer look at the PostgreSQL EXPLAIN statement and review some examples of its use.

Prerequisites

If you’re planning to follow along with this tutorial, you’ll need to have access to psql, the interactive terminal for PostgreSQL. You can use the command psql -V to see the version number for your Postgres installation.

To check the status of PostgreSQL on your machine, use the command sudo systemctl status postgresql, which will let you know if the service is working.

Open and access the command-line interface in PostgreSQL

By default, the postgres user has full admin status in PostgreSQL. Enter the command shown below to access the psql command-line terminal as the postgres superuser:

Once you have access to the command-line console, you can execute commands such as the Postgres EXPLAIN statement.

PostgreSQL EXPLAIN statement

As we mentioned earlier, the EXPLAIN statement allows you to view the execution plan for a query. Analyzing the execution plan can show you how to improve performance by optimizing the database. Be patient if you’re new to analyzing query plans– it can take time to master the art of interpreting them.

Create PostgreSQL database

Before we can test out examples of the EXPLAIN statement, we’ll need to create a database. We’ll use the following statement to create the database:

Create PostgreSQL table

Next, we’ll create a PostgreSQL table using the following statement:

NOTE: The constraint applied to the column is optional.

Insert data in PostgreSQL table

To insert data into our PostgreSQL table, we’ll use the INSERT INTO statement. This statement can be used to insert one row or multiple rows. Here’s the basic syntax of the INSERT statement:

We’ll use this statement to insert our sample data:

We can use the SELECT statement to perform a simple query and see if our INSERT was successful:

PostgreSQL using EXPLAIN

In this section, we’ll demonstrate the PostgreSQL EXPLAIN statement using examples based on the payroll table:

Here’s the query we’ll be analyzing:

To see the query plan for this query, use the following:

The output will look like this:

Using the EXPLAIN statement is easy; understanding the query plan that’s returned is a bit more challenging. In this example, we see that the start up cost is cost= 0.00, the max time is the 25.00 and the rows is 400.

To execute the plan and inspect it, use the following command:

You’ll receive this result:

In this example, we used the EXPLAIN ANALYZE statement. This statement doesn’t just offer estimates for these metrics but also provides the true run time of the query. The three important items shown above are the start up cost, maximum time and the number of rows returned.

The execution plan tells the start up cost is 0.024, the maximum time is the 0.027, and the number of rows returned was 2.

Conclusion

If you want to get an estimate of how long a given query will take to execute, you’ll need to view its query plan. You can inspect the execution plan for a query using the PostgreSQL EXPLAIN statement. In this article, we looked at examples that use the EXPLAIN statement and showed you how to interpret the execution plan that’s returned. With these instructions, you’ll be able to obtain query plans and optimize performance in your own PostgreSQL database.