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:
1 | $ sudo -u postgres psql |
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:
1 | CREATE DATABASE database_name; |
Create PostgreSQL table
Next, we’ll create a PostgreSQL table using the following statement:
1 2 | CREATE TABLE payroll (COLUMN NAME + DATA TYPE + CONSTRAINT [NULL | NOT NULL] ); |
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:
1 2 | INSERT INTO TABLE_NAME (column_1, column_2) VALUES (value_1, value_2); |
We’ll use this statement to insert our sample data:
1 2 3 4 5 6 7 | INSERT INTO payroll (name,wage) VALUES ('johny','800'), ('sam','890'), ('jorry','1000'), ('lisa','590'), ('ryan','970'); INSERT 0 5 |
We can use the SELECT
statement to perform a simple query and see if our INSERT
was successful:
1 2 3 4 5 6 7 8 9 | db_name=# SELECT * FROM payroll; emp_id | name | wage --------+-------+------ 1 | johny | 800 2 | sam | 890 3 | jorry | 1000 4 | lisa | 590 5 | ryan | 970 (5 ROWS) |
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:
1 | SELECT name FROM payroll WHERE wage >= '900'; |
To see the query plan for this query, use the following:
1 2 3 | EXPLAIN SELECT name FROM payroll WHERE wage >=900; |
The output will look like this:
1 2 3 4 5 | QUERY PLAN ----------------------------------------------------------- Seq Scan ON payroll (cost=0.00..25.00 ROWS=400 width=32) FILTER: (wage >= 900) (2 ROWS) |
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:
1 2 3 | EXPLAIN ANALYZE SELECT name FROM payroll WHERE wage >=900; |
You’ll receive this result:
1 2 3 4 5 6 7 8 | QUERY PLAN ---------------------------------------------------- Seq Scan ON payroll (cost=0.00..25.00 ROWS=400 width=32) (actual TIME=0.024..0.027 ROWS=2 loops=1) FILTER: (wage >= 900) ROWS Removed BY FILTER: 3 Planning TIME: 0.108 ms Execution TIME: 0.061 ms (5 ROWS) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started