I have spent many, many years staring at PostgreSQL query plans.
Eventually, you kind of stop guessing why a query is slow. You start reading the plan like a story.
But I'll be honest. Getting to that point took me way too long. This is mostly because the existing tools weren't much help.
For instance, a tool like explain.depesz.com (opens in new tab) has been around since 2009, but it only shows a color-coded table. There is no tree structure, no warnings, and no real explanation of what you're looking at.
Another tool, explain.dalibo.com (opens in new tab), added the tree view, but the interface here is too cluttered. Plus, it doesn't work well on mobile devices, and it still fails to tell you the most important thing.
What is ACTUALLY wrong with my plan?
All this pent-up frustration led me to build a PostgreSQL EXPLAIN visualizer.
This tool parses your EXPLAIN ANALYZE output. It also does three things the other tools don't do together.
First, it renders an actual top-down tree, just like a computer science textbook.
Second, it automatically flags common performance problems.
Finally, it lets you share the plan using a URL without creating an account or sending data to a server.
You're welcome!
In this article, I'll show you how to actually read query plans and fix the issues you find. Everything covered here applies whether you use my visualizer or prefer reading the raw text output right in your terminal.
What is the Difference Between EXPLAIN vs EXPLAIN ANALYZE?
EXPLAIN shows you what the database planner estimates will happen, while EXPLAIN ANALYZE actually runs the query and shows you what did happen.
Most of my colleagues (engineers) know that EXPLAIN reveals the execution plan Postgres picked for your SQL. However, only a small percentage understand the crucial difference between the two commands. The gap between the planner's estimate and the actual execution is often exactly where those pesky performance problems hide.
Here is a simple example:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';Seq Scan on users (cost=0.00..4250.00 rows=1 width=72) (actual time=23.456..45.123 rows=1 loops=1)
Filter: (email = '[email protected]'::text)
Rows Removed by Filter: 99999
Planning Time: 0.089 ms
Execution Time: 45.167 ms
Here are a few key details you need to pay attention to:
cost=0.00..4250.00
This is the planner's estimate of how expensive this operation will be. The first number is the startup cost, which represents the time before the first row is returned. The second number is the total cost.
These are not milliseconds, by the way. They are arbitrary units the planner uses internally to compare different plans.
rows=1 vs actual rows=1
This shows the planner's row estimate compared to reality. When these numbers diverge by a factor of 10 or 100, the planner usually picks the wrong strategy. I will cover this in more detail later.
Rows Removed by Filter: 99999
We have a massive red flag here. Postgres scanned 100,000 rows just to find only one. That's a sequential scan on a table that clearly needs an index on the email column.
If you need even more detailed output, you can use EXPLAIN (ANALYZE, BUFFERS). This will show you how many pages Postgres found in its own cache and how many it had to read in.
I'll explore this in a dedicated section below.
Does EXPLAIN ANALYZE Actually Execute your Query?
Yes, it executes the statement completely, meaning if you run it on an UPDATE or a DELETE, the data will actually change.
Because the command runs the statement, all side effects still happen. You will only see the query plan in your output, but the rows in your database will be modified.
When you need to see the plan for a query that modifies data, you should wrap it in a transaction and roll it back.
Check it out.
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2023-01-01';
ROLLBACK;By doing this, you get the real timings and real row counts, but the table will stay exactly as it was. Keep in mind that a rollback doesn't undo sequence values. An INSERT run under EXPLAIN ANALYZE will still consume IDs, so you should expect gaps in your sequence.
How Do You Read a Query Plan as a Tree?
You read it from the inside out, where execution starts at the most indented leaf nodes and flows upwards to the root.
The planner breaks your SQL down into operations, and these operations nest inside each other.
I can explain this much better if we look at a more complex example.
EXPLAIN ANALYZE
SELECT u.name, count(o.id), sum(oi.price)
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY sum(oi.price) DESC
LIMIT 20;Limit (actual time=234.56..234.58 rows=20 loops=1)
-> Sort (actual time=234.55..234.56 rows=20 loops=1)
Sort Key: (sum(oi.price)) DESC
Sort Method: top-N heapsort Memory: 27kB
-> HashAggregate (actual time=220.10..228.45 rows=4523 loops=1)
Group Key: u.name
-> Hash Join (actual time=5.67..195.23 rows=89234 loops=1)
Hash Cond: (oi.order_id = o.id)
-> Seq Scan on order_items oi (actual time=0.01..45.67 rows=524000 loops=1)
-> Hash (actual time=5.12..5.12 rows=15234 loops=1)
-> Hash Join (actual time=0.89..4.56 rows=15234 loops=1)
Hash Cond: (o.user_id = u.id)
-> Index Scan using orders_created_idx on orders o (actual time=0.02..2.34 rows=15234 loops=1)
Index Cond: (created_at > '2024-01-01')
-> Hash (actual time=0.45..0.45 rows=4523 loops=1)
-> Seq Scan on users u (actual time=0.01..0.30 rows=4523 loops=1)
Planning Time: 1.234 ms
Execution Time: 234.89 ms
If I want to read the indentation properly, I look for the deepest nodes first.
Operations like the Seq Scan on users and Index Scan on orders execute first. Their results feed into the Hash Joins directly above them. Those results then feed into a HashAggregate, followed by a Sort, and finally a Limit. Every level of indentation represents a child operation feeding data into its parent.
The text format works well enough, but it becomes very hard to follow when plans have over 20 nodes with multiple levels of nesting.
This is exactly where a visual tree representation helps. It renders the output as an actual top-down tree with color-coded nodes, allowing you to see at a glance where your execution time is being spent.
There is one specific field that constantly trips people up, and that is loops.
Take a look at this.
actual time=0.005..0.070 rows=1 loops=150
This means this specific node ran 150 times. The actual time shown is per loop. The total time for this node is actually 0.070 multiplied by 150, which equals 10.5ms. If you miss this "loops multiplier," you will severely underestimate the cost of inner nodes in nested loops by several orders of magnitude.
What Node Types Will You See in Every Query Plan?
You will encounter a predictable handful of scan, join, sort, and aggregate nodes over and over again.
PostgreSQL has roughly a few dozen node types, yet you will run into one and the same handful repeatedly. Knowing what each one of them does and when the planner picks it is the absolute foundation for understanding any plan.
Scan Nodes
Scan nodes are the leaf nodes that actually touch your tables.
Seq Scan reads every row in the table, and it does it in order. Postgres chooses Seq Scan when there is no usable index, when the table is so small that no index would help, or when the query needs most of the rows anyway. For example, a Seq Scan on a 50-row lookup table is perfectly fine. However, a Seq Scan on a 10-million-row table filtering down to 3 results is a major problem.
Index Scan uses a B-tree (or other) index to find matching rows. Then, it fetches each row from the table. This is good for selective queries that return only a small fraction of the table.
Index Only Scan is the best-case scenario. If the index contains all the columns the query really needs, Postgres doesn't even have to visit the table. You will see this if your covering index matches the query's SELECT and WHERE clauses. Check the "Heap Fetches" number. It tells you how many rows it really had to visit the table for, which usually happens because the visibility map wasn't up to date.
Bitmap Index Scan + Bitmap Heap Scan is a two-phase approach where it scans the index and builds a bitmap of which pages contain matching rows first. Then, it scans those pages in physical order. Postgres uses this when too many rows match for a plain Index Scan because random I/O would be too expensive, but too few match for a Seq Scan to make sense. You will often see this with OR conditions or low-selectivity index columns.
Join Nodes
Nested Loop takes each row from the outer (top) input. It then scans the inner (bottom) input for matches. This is fast when the outer input is small, but it's terrible when both inputs are large, because the inner scan runs once for every outer row.
Hash Join builds a hash table from the smaller input. Then, it probes it with each row from the larger input. This is the planner's go-to join strategy for most medium-to-large joins. It requires enough work_mem to hold the hash table in memory, though.
And Merge Join requires both inputs to be sorted on the join key. It walks through both sorted lists in lockstep, and it's very efficient when both sides are already sorted either from an index or a preceding Sort node, but it is rare compared to a Hash Join in practice.
Sort and Aggregate
Sort does exactly what anyone would expect, but the key detail here is the sort method. The text quicksort Memory: 25kB means the operation fits in memory. On the other hand, external merge Disk: 12345kB means it ran out of work_mem and spilled to disk, which is way slower. If you see disk sorts, consider increasing work_mem for that session. You could also try adding an index that provides pre-sorted output.
HashAggregate and GroupAggregate handle all GROUP BY operations. HashAggregate builds a hash table of groups, which is faster for many distinct groups. GroupAggregate walks pre-sorted input, which can perform better when the data is already sorted by the group key.
What are the 5 Most Common Performance Problems?
Missing indexes, stale statistics, wrong join strategies, disk sorts, and excessive filtering account for the vast majority of slow queries.
After optimizing hundreds of queries across different projects, I've found that these five patterns account for the vast majority of slow query plans.
Sequential Scan on a Large Table
This is the most common problem, but it's the easiest one to fix. You will see it looking like this:
Seq Scan on orders (actual time=0.01..892.34 rows=47 loops=1)
Filter: (customer_id = 12345 AND status = 'active')
Rows Removed by Filter: 1249953
You see? Almost 1.25 million rows were scanned just to find 47. The fix is usually a composite index that looks a little like this:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);After you add the index, the plan changes to this:
Index Scan using idx_orders_customer_status on orders (actual time=0.03..0.12 rows=47 loops=1)
Index Cond: (customer_id = 12345 AND status = 'active')
As you can see, the execution time dropped from 892ms to 0.12ms.
The takeaway here is that column order in the index matters quite a bit. Put the most selective column first, or closely match the column order from your WHERE clause.
One caveat to remember here is that not every Seq Scan is bad.
For example, let's say the table only has 200 rows. An index here would add overhead without providing any meaningful benefit. Postgres is usually smart about making this distinction.
Stale Statistics Causing Wrong Join Strategies
This one is a bit sneaky because the plan really looks perfectly reasonable until you compare the estimated rows to the actual rows.
Take a look:
Hash Join (cost=1234.56..5678.90 rows=50 width=120) (actual time=12.34..4567.89 rows=125000 loops=1)
So, the planner estimated 50 rows, but reality was 125,000. That is a whopping 2,500-times mismatch.
When the planner thinks a join produces only 50 rows, it might choose a Nested Loop, which is fine for 50 but outright catastrophic for 125,000.
As a result, the entire downstream plan gets built on a wrong assumption.
But I have a fix, and it's almost always running:
ANALYZE orders;This will update your table's statistics so the planner can make better estimates. And, in case your tables have data that changes significantly between autovacuum runs, then you might need to increase default_statistics_target for specific columns or tune your autovacuum settings altogether.
Nested Loop Where Hash Join Would Be Faster
Take a look at the output below:
Nested Loop (actual time=0.05..3456.78 rows=85000 loops=1)
-> Seq Scan on departments (actual time=0.01..0.15 rows=25 loops=1)
-> Index Scan using emp_dept_idx on employees (actual time=0.02..125.45 rows=3400 loops=25)
Here, the inner Index Scan runs 25 times, which is once per department, fetching 3,400 rows each time. That results in 85,000 index lookups in total.
A Hash Join would build a hash table of departments once and probe it while scanning employees once. This is much faster at this scale.
This problem usually happens because of stale statistics, as seen in the previous problem, or because the planner underestimates the inner side's row count.
If you fix the statistics, this often fixes the join choice automatically. If it doesn't, I've got your back.
You can test with this:
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;This command forces the planner to pick a different strategy. But whatever you do, do NOT leave this set in production. It is strictly a diagnostic tool that you use to confirm that a Hash Join would actually be faster before you investigate why the planner is not naturally choosing it.
Sort Spilling to Disk
Take a look at this:
Sort (actual time=567.89..612.34 rows=250000 loops=1)
Sort Key: created_at
Sort Method: external merge Disk: 45678kB
The phrase external merge Disk here means the sort exceeded work_mem and wrote temporary data to disk. In PostgreSQL, the default work_mem is 4MB, which is pretty conservative to say the least.
You have two options to fix this.
First, you can increase work_mem for the session:
SET work_mem = '256MB';You can also do this per-transaction.
But remember, don't set this globally to a large value because it applies per-sort-operation and per-connection. Say you have 100 connections, each doing a 256MB sort. Well, that would consume 25GB of RAM.
Second, you can add an index that provides sorted output, which eliminates the sort entirely:
CREATE INDEX idx_orders_created ON orders (created_at DESC);Suppose the query already filters on one column and sorts by another. Well, in that case, a composite index covering both can eliminate the Seq Scan AND the Sort in a single shot. Two birds with one stone, as they say.
Filter Removing Most Scanned Rows
Seq Scan on events (actual time=0.02..345.67 rows=12 loops=1)
Filter: (type = 'error' AND severity = 'critical' AND created_at > '2024-06-01')
Rows Removed by Filter: 2347988
In this example, 2.3 million rows were scanned, but only 12 were kept. So, the filter works, but it processes way too many rows.
You have a few options to resolve this.
You could use a composite index on the filter columns:
CREATE INDEX idx_events_type_sev_date ON events (type, severity, created_at);Alternatively, you could use a partial index if the filtered values are rare:
CREATE INDEX idx_events_critical ON events (created_at)
WHERE type = 'error' AND severity = 'critical';My take here is that Partial indexes are smaller and faster because they only index those rows that match the WHERE clause. If you are always querying for type = 'error' AND severity = 'critical', the partial index would be the perfect surgical solution.
What Does EXPLAIN (ANALYZE, BUFFERS) Tell You About Memory and Disk Usage?
It shows exactly how many 8 kB pages Postgres touched and whether they were already in Postgres's cache or had to be read in.
Timings tell you that a node is slow, but BUFFERS helps you understand why. It basically adds a line to each node showing how many 8 kB pages Postgres touched, along with where they came from:
Seq Scan on orders (actual time=0.01..892.34 rows=47 loops=1)
Filter: (customer_id = 12345 AND status = 'active')
Rows Removed by Filter: 1249953
Buffers: shared hit=2051 read=12893
The shared hit metric represents pages that were already in the shared buffer cache of Postgres. This is the cheap case.
The shared read metric represents pages Postgres had to request from the operating system. These pages can still come from the OS page cache. They can also come from the disk, but the plan cannot tell you which one. But you can be sure that, if you have a high read count on a query that is only slow the first time it runs, that's a clear, classic sign of a cold cache.
Now, two runs of the same query can actually produce an identical plan with very different timings. BUFFERS is usually where that difference will show up. You could also see dirtied and written. These count the pages the query modified or had to write out.
An important thing to remember is that, on PostgreSQL 18 and newer, EXPLAIN ANALYZE includes this output automatically. If you're running version 17 or older, you need to explicitly ask for it using EXPLAIN (ANALYZE, BUFFERS).
What Is the Full Workflow Beyond Just Using EXPLAIN?
You must find slow queries first using pg_stat_statements, look at buffers, run ANALYZE regularly, and tune your server parameters.
EXPLAIN ANALYZE is at the very middle of the workflow, not at the beginning.
Before you can start optimizing a query, you first need to know which queries are slow. Here's how you do that.
The first step is to find slow queries with pg_stat_statements. This extension tracks execution statistics for all queries. Your task is to enable it, then let it collect data for a day, and then sort by total_exec_time or mean_exec_time to find the main culprits. This is way more reliable than just guessing or waiting for a customer to issue a complaint.
Then, make sure to always look at BUFFERS. If you're using PostgreSQL 18, no worries, it's there by default. But on older versions, make EXPLAIN (ANALYZE, BUFFERS) your habit instead of the plain EXPLAIN ANALYZE. If your query is reading 50,000 pages from the cache, this is much different than one that reads 50,000 pages from disk, and this is true even if the plan looks completely identical.
Make a habit of running ANALYZE regularly. I'd say that this is the single cheapest performance fix in PostgreSQL. Stale statistics will cause more bad query plans than anything else you encounter. Of course, if your autovacuum is running regularly, then this should be handled automatically, but if you're seeing row estimate mismatches, check whether autovacuum is actually keeping up with your write volume.
Understand when to tune server parameters. The work_mem parameter controls sort and hash memory per operation. The effective_cache_size tells the planner how much data it can expect to find in the OS page cache, and this affects its cost estimates for index scans compared to sequential scans. The random_page_cost adjusts how expensive the planner considers random I/O relative to sequential I/O. On SSDs, lowering this from the default 4.0 to a range of 1.1 to 1.5 makes the planner more willing to use indexes.
If you want an even deeper dive, check out my article on how the concurrency model of PostgreSQL affects query performance. This will be of great help to you, especially when you're dealing with lock contention showing up in your plans. I covered that in a separate article.
So, Is Reading EXPLAIN Plans a Difficult Skill to Learn?
It is a practical skill built by reading plans regularly and matching patterns to fixes, but it's definitely not magic.
Analyzing Query plans is not something you can learn quickly from a single blog post. It's a real skill that you build by reading plans regularly. It's pattern recognition. Eventually, you'll start matching patterns to fixes.
The point of this article was to show you that you don't need to memorize every single node type or every edge case. I covered the five most important problems. They help with the vast majority of real-world slow queries.
So, if you can spot missing indexes, stale statistics, wrong join strategies, disk sorts, and excessive filtering, you'll be able to fix most performance problems you come across.
And remember, my PostgreSQL EXPLAIN visualizer is there for you. It'll undoubtedly make the reading part easier. All you have to do is paste your plan, look at the tree, and check the warnings. It runs right in your browser, and nothing leaves your machine.
Do you have a query plan that is confusing you? Paste it into the visualizer and share the URL with me. I'm always here, and I'm happy to take a look.