How to Use the PostgreSQL EXPLAIN Statement
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.
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
$ 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:
CREATE DATABASE database_name;
Create PostgreSQL table
Next, we’ll create a PostgreSQL table using the following statement:
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 INTO TABLE_NAME (column_1, column_2)
VALUES (value_1, value_2);
We’ll use this statement to insert our sample data:
INSERT INTO payroll (name,wage)
INSERT 0 5
We can use the
SELECT statement to perform a simple query and see if our
INSERT was successful:
db_name=# SELECT * FROM payroll;
emp_id | name | wage
1 | johny | 800
2 | sam | 890
3 | jorry | 1000
4 | lisa | 590
5 | ryan | 970
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:
SELECT name FROM payroll WHERE wage >= '900';
To see the query plan for this query, use the following:
EXPLAIN SELECT name
WHERE wage >=900;
The output will look like this:
Seq Scan ON payroll (cost=0.00..25.00 ROWS=400 width=32)
FILTER: (wage >= 900)
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:
EXPLAIN ANALYZE SELECT name
WHERE wage >=900;
You’ll receive this result:
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
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.
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