Published at Jul 30, 2024
Start supercharging your PostgreSQL today.
Written by Dylan Paulus
One day, everything was running great, and queries were running fast. Then, all of a sudden, after your application gained 3,000 new users, queries that used to run fast are now taking one, two, or even 10 seconds to return. You have no idea why! In situations like this, we want to know what operations PostgreSQL is running to perform a query or, in other terms, the query plan.
In this article, we'll look at EXPLAIN and EXPLAIN ANALYZE to help us understand what happens under the hood of PostgreSQL and use it to optimize query performance.
EXPLAIN is a command we prepend to queries to tell us what operations PostgreSQL plans to run for that query, how many rows each operation touches in the database(s), and how long those operations take. EXPLAIN is the go-to performance diagnostic tool for discovering what is causing a query to run slowly.
But before diving into EXPLAIN, what happens when we give PostgreSQL a query?
If we think about how computers operate on a list of data, all operations happen imperatively. We provide the computer with a list of commands to run. When given a list, the computer loops through each individual item and performs some operation. In Python, this would look like:
items = [
{ "name": "Chair", "location": "Stock"},
{ "name": "Pens", "location": "Warehouse"},
{ "name": "Printer Ink", "location": "Stock"},
{ "name": "Paper", "location": "Warehouse"},
]
# Get a list of only items in the Warehouse
items_in_warehouse = []
for item in items: # Loop through all items one by one
if item["location"] == "Warehouse":
items_in_warehouse.append(item)
On the other hand, SQL is not imperative but a declarative language. We describe what we want from PostgreSQL instead of writing every command that PostgreSQL will execute. Writing the same Python logic in SQL would look like this:
SELECT * FROM items WHERE location = 'Warehouse';
Though SQL is more compact and straightforward, PostgreSQL still needs to translate SQL into imperative code that computers can run. Providing PostgreSQL with a query like the one above will turn it into a set of operations it needs to run that looks similar to the imperative Python code.
To get an idea of how PostgreSQL translates SQL into imperative operations, we can use EXPLAIN. Let's look at an example. Assume we have a table in our database that stores the items we have in inventory:
CREATE TABLE items (
id SERIAL PRIMARY KEY,
name TEXT,
quantity INTEGER,
location TEXT
);
Next, we can populate the table with test data by running:
INSERT INTO items (name, quantity, LOCATION)
SELECT CONCAT((array['Pens', 'Paper', 'Printer Ink', 'Chairs'])[floor(random() * 4 + 1)], ' ', INDEX),
floor(random()*INDEX), (array['Stock',
'Warehouse'])[floor(random() * 2 + 1)]
FROM generate_series(1, 1000) AS INDEX;
Now, we want to query for all the items in the Warehouse. The query would look like this:
SELECT * FROM items WHERE location = 'Warehouse';
Finally, add EXPLAIN before SELECT to get the query plan.
EXPLAIN SELECT * FROM items WHERE location = 'Warehouse';
The output of EXPLAIN is structured as a tree where indented entries are child nodes. In the EXPLAIN output, the query plan includes a Seq Scan operation with a Filter as a child operation. These are the imperative operations PostgreSQL determined would run from the declarative SQL statement given. But what do these operations mean?
Seq Scan says that PostgreSQL is looping over every row in the database—we can think of seq scan as a for-loop over the whole table.
Filter is doing precisely that, filtering out rows based on the criteria given.
In other words, EXPLAIN tells us that PostgreSQL will loop through all rows in the items table and return any row where location = 'Warehouse' when given SELECT * FROM items WHERE location = 'Warehouse';.
Next to each node of the query plan is metadata about that operation.
Cost: it has two numbers.
The first number is the estimated start-up cost or the time until the operation outputs the first result.
The second number is the estimated total cost (how long the operation took to complete).
Rows: it tells you how many rows the operation output has.
Width: it shows the average width of rows output by the operation in bytes.
EXPLAIN estimates the cost of each operation in the execution tree without running the query itself. PostgreSQL constantly tracks query statistics, so though EXPLAIN gives us an estimate, it is a good baseline for finding performance issues. If we want to get the cost information for a query by actually executing the query, we can use EXPLAIN ANALYZE.
The output of running EXPLAIN ANALYZE SELECT * FROM items WHERE location = 'Warehouse'; would be:
As you can see, we get additional information about the query, like actual time (the startup time and total execution of the query actually running) and Planning/Execution Time detailing how long it took to execute.
Note: The time provided by EXPLAIN ANALYZE will be slightly slower than the actual query time since measuring query execution incurs overhead.
When would you want to use EXPLAIN ANALYZE over EXPLAIN?
EXPLAIN gives a cost estimate and doesn't run the query provided. This is great when measuring expensive queries in production to reduce server load or when measuring queries that mutate data (INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS, or EXECUTE).
EXPLAIN ANALYZE runs the query. It should be used when you need the exact execution times of a query.
Let's explore a real-world example to get a grasp of using EXPLAIN to optimize queries. We maintain an ordering system as the backbone of a storefront. The system has three tables: orders, line_items, and users.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
address TEXT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users,
created_at TIMESTAMP DEFAULT now()
);
CREATE TABLE line_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders,
name TEXT,
quantity INTEGER
);
Then insert test data:
-- Add users
INSERT INTO users (name, address)
VALUES ('Karisa', '1234 E 49 st.');
INSERT INTO users (name, address)
VALUES ('John', '35 12th st.');
-- Add orders
INSERT INTO orders (user_id, created_at)
SELECT (floor(random() * 2 + 1)::int), time_hour
FROM generate_series(TIMESTAMPTZ '2024-01-01', TIMESTAMPTZ '2024-11-07', INTERVAL '1 hour') AS time_hour;
-- Add line items
INSERT INTO line_items (order_id, name, quantity)
SELECT order_id.id,
CONCAT('item', ' ', INDEX),
floor(random() * 10 + 1)::int
FROM generate_series(1, floor(random() * 10 + 1)::int) AS INDEX,
(SELECT id
FROM orders
ORDER BY RANDOM()) AS order_id;
A query in our ordering application fetches all the line items ordered by a given user.
SELECT items.*
FROM line_items AS items
LEFT JOIN orders ON items.order_id = orders.id
LEFT JOIN users ON users.id = orders.user_id
WHERE users.name = 'Karisa';
Running this query takes a while to complete. If we use our friend, EXPLAIN we can get insights into why the query is slow:
EXPLAIN
SELECT items.*
FROM line_items AS items
LEFT JOIN orders ON items.order_id = orders.id
LEFT JOIN users ON users.id = orders.user_id
WHERE users.name = 'Karisa';
This may look scary, but there are a few key pieces we should look at:
Hash and Hash Join tell us that PostgreSQL is performing a JOIN, which aligns with our query using two LEFT JOINs. The cost of these operations can be deceiving because a node in the execution tree sums up all the costs of its child nodes
Gather, Workers Planned, and Parallel shows that PostgreSQL has decided to run operations in parallel to speed up our query
Seq Scan is the slowest operation in the query plan, mainly when looping through line_items. PostgreSQL needs to scan the table to find the row orders.id can join line_items.order_id against.
No doubt scanning through all the line_items is causing this query to run slowly (cost=0.00..931789.57!). But what can we do about it? Instead of looping through all the line_items and orders to join rows together, it would be much quicker if PostgreSQL could quickly find a row based on the foreign key. Fortunately, this exists through indexing. Add two indexes for each foreign key on line_items.order_id and orders.user_id respectively.
CREATE INDEX idx_line_item_orders ON line_items (order_id);
CREATE INDEX idx_user_orders on orders (user_id);
And rerun the query with EXPLAIN: EXPLAIN SELECT items.* FROM line_items AS items LEFT JOIN orders ON items.order_id = orders.id LEFT JOIN users ON users.id = orders.user_id WHERE users.name = 'Karisa';.
Much better! We can see that the cost estimates are significantly smaller, and EXPLAIN now tells us we're using Index Scan, confirming that the indexes we just created are being utilized. To get an even better grasp of how much adding indexes improved the speed of the query, we can rerun the query using EXPLAIN ANALYZE before and after adding the indexes, taking note of Execution Time.
Before:
After:
EXPLAIN is an essential tool for determining what PostgreSQL is doing under the hood and how to optimize queries. But this is just one piece of the puzzle. There are many ways to find bottlenecks and gain insight into the database. Timescale provides Insights, which allows you to deep dive into helpful statistics on query timing/latency, memory usage, and more, PostgreSQL has the pg_stat_statements extension (enabled by default in Timescale), plus many more documented on our Guide to PostgreSQL Performance.
For a deeper dive into using PostgreSQL's EXPLAIN, check out our video Explaining Explain!
To get started with Timescale, sign up for a free account today.