PostgreSQL EXPLAIN ANALYZE: How to Read Query Plans and Fix Slow Queries

I've spent years staring at PostgreSQL query plans. At some point you stop guessing why a query is slow and start reading the plan like a story. But getting to that point took way too long, mostly because the existing tools didn't help much.

explain.depesz.com (opens in new tab) has been around since 2009. It shows a colored table. No tree, no warnings, no explanation of what you're looking at. explain.dalibo.com (opens in new tab) added a tree view, but the UI is cluttered, it doesn't work on mobile, and it doesn't tell you what's actually wrong with your plan.

So I built a PostgreSQL EXPLAIN visualizer that parses your EXPLAIN ANALYZE output and does three things the others don't: renders an actual tree (top-down, like a CS textbook), flags common performance problems automatically, and lets you share the plan via URL without creating an account or sending data to a server.

This article walks through how to actually read query plans and fix what you find. Everything here applies whether you use the visualizer or just read raw text output in your terminal.

What PostgreSQL EXPLAIN ANALYZE Actually Shows You

Most engineers know that EXPLAIN shows you the query plan. Fewer understand the difference between what EXPLAIN shows and what EXPLAIN ANALYZE shows, and that difference matters.

EXPLAIN alone gives you the planner's estimate. What Postgres thinks will happen. EXPLAIN ANALYZE actually runs the query and records what did happen. The gap between those two is often where performance problems hide.

Here's 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

A few things to notice:

cost=0.00..4250.00 is the planner's estimate of how expensive this operation is. The first number is the startup cost (time before the first row is returned). The second is the total cost. These aren't milliseconds. They're arbitrary units the planner uses internally to compare plans.

rows=1 vs actual rows=1 shows the planner's row estimate vs reality. When these diverge by 10x or 100x, the planner picks the wrong strategy. More on that later.

Rows Removed by Filter: 99999 is the red flag here. Postgres scanned 100,000 rows to find 1. That's a sequential scan on a table that clearly needs an index on email.

For more detailed output, use EXPLAIN (ANALYZE, BUFFERS) to also see how many pages Postgres read from disk vs cache. That cache hit ratio tells you a lot about whether your working set fits in memory.

Reading the Plan as a Tree

A query plan is a tree. The planner breaks your SQL into operations, and those operations nest inside each other. Execution starts at the leaves (the innermost, most-indented nodes) and flows up to the root.

Here's 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

Reading the indentation: the deepest nodes (Seq Scan on users, Index Scan on orders) execute first. Their results feed into Hash Joins, which feed into a HashAggregate, then a Sort, then a Limit. Each level of indentation is a child feeding into its parent.

The text format works, but it gets hard to follow when plans have 20+ nodes with multiple levels of nesting. That's where a visual tree representation helps. It renders this as an actual top-down tree with color-coded nodes, so you can see at a glance where time is being spent.

One field that trips people up: loops. When you see actual time=0.005..0.070 rows=1 loops=150, that means this node ran 150 times. The actual time shown is per loop. Total time for this node is 0.070 * 150 = 10.5ms. Miss the loops multiplier and you'll underestimate the cost of inner nodes in nested loops by orders of magnitude.

Node Types You'll See in Every Plan

PostgreSQL has a few dozen node types, but you'll run into the same handful over and over. Knowing what each one does and when the planner picks it is the foundation for understanding any plan.

Scan Nodes

These are the leaf nodes, the ones that actually touch your tables.

Seq Scan reads every row in the table, in order. Postgres chooses this when there's no usable index, when the table is small enough that an index wouldn't help, or when the query needs most of the rows anyway. A Seq Scan on a 50-row lookup table is fine. A Seq Scan on a 10-million-row table filtering down to 3 results is a problem.

Index Scan uses a B-tree (or other) index to find matching rows, then fetches each row from the table. Good for selective queries that return a small fraction of the table.

Index Only Scan is the best case. If the index contains all the columns the query needs, Postgres doesn't have to visit the table at all. You'll see this when your covering index matches the query's SELECT and WHERE clauses. The "Heap Fetches" number tells you how many rows it did have to visit the table for, usually because the visibility map wasn't up to date.

Bitmap Index Scan + Bitmap Heap Scan is a two-phase approach. First, scan the index and build a bitmap of which pages contain matching rows. Then, scan those pages in physical order. Postgres uses this when too many rows match for a plain Index Scan (random I/O would be expensive) but too few for a Seq Scan to make sense. You'll often see this with OR conditions or low-selectivity index columns.

Join Nodes

Nested Loop takes each row from the outer (top) input and scans the inner (bottom) input for matches. Fast when the outer input is small. Terrible when both inputs are large, because the inner scan runs once per outer row.

Hash Join builds a hash table from the smaller input, then probes it with each row from the larger input. The go-to join for most medium-to-large joins. Requires enough work_mem to hold the hash table in memory.

Merge Join requires both inputs to be sorted on the join key. It walks through both sorted lists in lockstep. Efficient when both sides are already sorted (from an index or a preceding Sort node), but rare compared to Hash Join in practice.

Sort and Aggregate

Sort does what you'd expect. The important detail is the sort method. quicksort Memory: 25kB means it fit in memory. external merge Disk: 12345kB means it ran out of work_mem and spilled to disk, which is dramatically slower. If you see disk sorts, consider increasing work_mem for that session or adding an index that provides pre-sorted output.

HashAggregate and GroupAggregate handle GROUP BY. HashAggregate builds a hash table of groups (faster for many distinct groups), GroupAggregate walks pre-sorted input (can be better when data is already sorted by the group key).

The 5 Most Common Performance Problems

After optimizing hundreds of queries across different projects, these five patterns account for the vast majority of slow query plans I've seen.

1. Sequential Scan on a Large Table

The most common problem and the easiest to fix. You'll see it as:

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

Almost 1.25 million rows scanned to find 47. The fix is usually a composite index:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

After adding the index, the plan changes to:

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')

From 892ms to 0.12ms. Column order in the index matters. Put the most selective column first, or match the column order from your WHERE clause.

One caveat: not every Seq Scan is bad. If the table has 200 rows, an index adds overhead without meaningful benefit. Postgres is usually smart about this.

2. Stale Statistics Causing Wrong Join Strategies

This one is sneaky because the plan looks reasonable until you compare estimated rows to actual rows:

Hash Join  (cost=1234.56..5678.90 rows=50 width=120) (actual time=12.34..4567.89 rows=125000 loops=1)

The planner estimated 50 rows. Reality was 125,000. That's a 2,500x mismatch. When the planner thinks a join produces 50 rows, it might choose a Nested Loop (fine for 50, catastrophic for 125,000). The entire downstream plan is built on a wrong assumption.

The fix is almost always:

ANALYZE orders;

This updates the table's statistics so the planner makes better estimates. If your tables have data that changes significantly between autovacuum runs, you might need to increase default_statistics_target for specific columns or tune your autovacuum settings.

3. Nested Loop Where Hash Join Would Be Faster

When you see something like this:

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)

The inner Index Scan runs 25 times (once per department), fetching 3,400 rows each time. That's 85,000 index lookups total. A Hash Join would build a hash table of departments once and probe it while scanning employees once. Much faster at this scale.

This usually happens because of stale statistics (problem #2) or because the planner underestimates the inner side's row count. Fixing statistics often fixes the join choice. If it doesn't, you can test with:

SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;

This forces the planner to pick a different strategy. Don't leave this set in production. It's a diagnostic tool to confirm that a Hash Join would actually be faster before you investigate why the planner isn't choosing it.

4. Sort Spilling to Disk

Sort  (actual time=567.89..612.34 rows=250000 loops=1)
  Sort Key: created_at
  Sort Method: external merge  Disk: 45678kB

"external merge Disk" means the sort exceeded work_mem and wrote temporary data to disk. The default work_mem in PostgreSQL is 4MB, which is conservative.

Two options:

Increase work_mem for the session:

SET work_mem = '256MB';

Or per-transaction. Don't set this globally to a large value because it applies per-sort-operation, per-connection. 100 connections each doing a 256MB sort would consume 25GB of RAM.

Add an index that provides sorted output, eliminating the sort entirely:

CREATE INDEX idx_orders_created ON orders (created_at DESC);

If the query already filters on a column and sorts by another, a composite index covering both can eliminate the Seq Scan and the Sort in one shot.

5. 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

2.3 million rows scanned, 12 kept. The filter works, it just works on way too many rows. Options:

Composite index on the filter columns:

CREATE INDEX idx_events_type_sev_date ON events (type, severity, created_at);

Partial index if the filtered values are rare:

CREATE INDEX idx_events_critical ON events (created_at)
  WHERE type = 'error' AND severity = 'critical';

Partial indexes are smaller and faster because they only index the rows that match the WHERE clause. If you're always querying for type = 'error' AND severity = 'critical', the partial index is the surgical solution.

Beyond EXPLAIN: The Full Workflow

EXPLAIN ANALYZE is the middle of the workflow, not the beginning. Before you can optimize a query, you need to know which queries are slow in the first place.

Find slow queries with pg_stat_statements. This extension tracks execution statistics for all queries. Enable it, let it collect data for a day, then sort by total_exec_time or mean_exec_time to find the worst offenders. This is more reliable than guessing or waiting for someone to complain.

Always use EXPLAIN (ANALYZE, BUFFERS) in practice. The BUFFERS option shows how many pages were read from the OS cache (shared hit) vs disk (shared read). A query that reads 50,000 pages from cache is very different from one that reads 50,000 pages from disk, even if the plan looks identical.

Run ANALYZE regularly. The single cheapest performance fix in PostgreSQL. Stale statistics cause more bad query plans than anything else. If your autovacuum is running regularly, this should be handled automatically. If you're seeing row estimate mismatches (problem #2 above), check whether autovacuum is keeping up with your write volume.

Know when to tune server parameters. work_mem controls sort and hash memory per operation. effective_cache_size tells the planner how much data it can expect to find in the OS page cache, which affects its cost estimates for index scans vs sequential scans. 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 1.1-1.5 makes the planner more willing to use indexes.

For a deeper look at how PostgreSQL's concurrency model affects query performance, especially when dealing with lock contention showing up in your plans, that's covered in a separate article.

Reading EXPLAIN Is a Skill, Not Magic

Query plan analysis isn't something you learn once from a blog post. It's a skill you build by reading plans regularly and matching patterns to fixes.

But you don't need to memorize every node type or every edge case. The five problems covered here, missing indexes, stale statistics, wrong join strategies, disk sorts, and excessive filtering, cover the vast majority of real-world slow queries. If you can spot those, you can fix most performance problems you'll encounter.

The PostgreSQL EXPLAIN visualizer is there to make the reading part easier. Paste your plan, look at the tree, check the warnings. Everything runs in your browser and nothing leaves your machine.

Have a query plan that's confusing you? Paste it into the visualizer and share the URL. I'm always happy to take a look.