Postgres EXPLAIN ANALYZE: Reading Query Plans to Kill Slow Joins

Your query works fine on a small dataset, but in production with millions of rows it grinds to a halt. You add an index, nothing changes. You rewrite the JOIN order, still slow. The problem is you're guessing — and the fix starts with reading what Postgres is actually doing.

EXPLAIN ANALYZE is the tool that stops the guessing. It shows you the exact execution plan Postgres chose, the estimated versus actual row counts, and where time is really being spent. Once you can read it, slow joins become a diagnostic problem instead of a mystery.

What you'll learn

• How to run EXPLAIN ANALYZE and read the output structure
• What each join strategy (nested loop, hash join, merge join) means for performance
• How to spot miscalibrated row estimates and why they cause bad plans
• Which indexes help joins and how to verify they're being used
• A repeatable process for diagnosing and fixing slow joins

Prerequisites

You should have access to a PostgreSQL instance (version 12 or later is assumed, though most of this applies from version 10 onward). Basic familiarity with SQL JOIN syntax is required. You don't need to be a DBA, but you should be comfortable running queries in psql or a tool like pgAdmin or DBeaver.

Running EXPLAIN ANALYZE

There are two related commands you need to know. EXPLAIN shows the plan Postgres intends to use without executing the query. EXPLAIN ANALYZE actually runs the query and reports what happened versus what was planned.

EXPLAIN ANALYZE
SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01';

For heavy queries on production data, add BUFFERS to see cache hit rates, which is often the difference between a fast and a slow plan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01';

One caution: EXPLAIN ANALYZE runs the query for real. Wrap destructive statements in a transaction you roll back, or use a read replica for long-running SELECT investigations.

Reading the Plan Tree

The output is a tree of nodes, each indented further as you go deeper. Execution flows from the innermost (most indented) node outward. The top node is what Postgres returns to your client.

Hash Join  (cost=1420.00..3850.22 rows=18500 width=36)
          (actual time=45.231..198.442 rows=21034 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..1200.00 rows=40000 width=20)
                            (actual time=0.018..32.104 rows=41200 loops=1)
        Filter: (created_at >= '2024-01-01')
        Rows Removed by Filter: 58800
  ->  Hash  (cost=620.00..620.00 rows=20000 width=24)
            (actual time=12.005..12.006 rows=20000 loops=1)
        Buckets: 16384  Batches: 1  Memory Usage: 1234kB
        ->  Seq Scan on customers c  (cost=0.00..620.00 rows=20000 width=24)
                                     (actual time=0.011..6.204 rows=20000 loops=1)

Each node shows two lines: cost estimates from the planner (before execution) and actual times and rows from runtime. The cost format is startup_cost..total_cost. Startup cost is the time before the first row is returned; total cost is the time to return all rows. These are in planner units, not milliseconds — only the actual time values are in milliseconds.

The Three Join Strategies

Postgres picks one of three strategies for each JOIN. Knowing what each one means helps you interpret why a plan is slow and what you can do about it.

Nested Loop Join

For each row in the outer table, Postgres scans the inner table to find matching rows. This is fast when the outer set is small and an index exists on the inner table's join key. It becomes catastrophic when the outer set is large, because the inner scan runs once per outer row.

Nested Loop  (actual time=0.041..8420.334 rows=21034 loops=1)
  ->  Seq Scan on orders o  (actual time=0.020..45.210 rows=41200 loops=1)
  ->  Index Scan using customers_pkey on customers c
        (actual time=0.180..0.182 rows=1 loops=41200)

Notice loops=41200 on the inner index scan. The query is doing 41,200 index lookups. If each one takes 0.18ms, that's about 7.4 seconds just for the inner side. This plan only makes sense if the outer set is very small. Here it isn't, so a different join strategy would likely be faster.

Hash Join

Postgres builds an in-memory hash table from the smaller table, then probes it with each row from the larger table. This is generally efficient for large datasets where no sort order is available. The risk is when the hash table doesn't fit in work_mem and spills to disk (look for Batches: > 1 in the Hash node).

Hash  (actual time=210.005..210.006 rows=200000 loops=1)
  Buckets: 131072  Batches: 4  Memory Usage: 4096kB

Batches: 4 means the hash table spilled to disk in four passes. Increasing work_mem for the session often resolves this:

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Merge Join

Both tables are sorted on the join key, then scanned in parallel. Efficient when both sides are already sorted (e.g., from an index scan), but requires a sort step if they aren't, which can be expensive.

Merge Join  (actual time=1200.000..1950.000 rows=21034 loops=1)
  Merge Cond: (o.customer_id = c.id)
  ->  Sort  (actual time=1190.000..1210.000 rows=41200 loops=1)
        Sort Key: o.customer_id
        Sort Method: external merge  Disk: 4512kB

Sort Method: external merge Disk: 4512kB tells you the sort spilled to disk. Again, work_mem is the first lever to try. An index on o.customer_id may eliminate the sort entirely by letting Postgres use an index scan in sorted order.

Spotting Estimate vs. Actual Row Count Mismatches

The most important diagnostic in any plan is the gap between rows=X in the estimate and rows=Y in the actual. A large mismatch means the planner is working with stale or inaccurate statistics, and it will likely choose a suboptimal strategy as a result.

Seq Scan on orders o
  (cost=0.00..1200.00 rows=500 width=20)
  (actual time=0.018..32.104 rows=41200 loops=1)

Postgres estimated 500 rows and got 41,200. That's an 82x miss. The planner almost certainly chose the wrong join strategy based on this. The fix is usually running ANALYZE on the table to refresh statistics:

ANALYZE orders;

If the mismatch persists after ANALYZE, the column may have a non-uniform distribution that the default statistics target misses. You can increase the statistics target for that column:

ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 500;
ANALYZE orders;

The default is 100. Values between 100 and 500 improve accuracy for skewed distributions at the cost of slightly more planning time.

Using Indexes on Join Keys

An index on a join key is only useful to Postgres in certain situations. Understanding when indexes help versus when they're ignored saves you from adding indexes that do nothing.

Indexes on join keys help when:

• The join is a nested loop and the inner table is being probed by key
• The join is a merge join and the index provides pre-sorted access
• A filter on the join key is highly selective (few matching rows)

Indexes on join keys are ignored for hash joins — the hash table is built from the data directly. If Postgres is choosing a hash join on a large table and you have an index on the join column, that's expected behavior, not a bug.

To verify an index exists and check its usage:

-- Check indexes on a table
\d orders

-- Or query the catalog
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

To create a covering index that supports a specific join and filter pattern:

-- Index on the join key plus the filtered column
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

A covering index like this can turn a sequential scan with a filter into an index-only scan, which skips the heap entirely when all needed columns are in the index.

Common Pitfalls

Implicit type casting silently prevents index use. If customer_id is an integer in one table and stored as text in another, Postgres inserts a cast function into the join condition. Functions on indexed columns prevent index usage. Check that join keys have matching types with \d tablename.

Running EXPLAIN ANALYZE on a cold cache misleads you. The first run reads from disk; subsequent runs hit the OS page cache. For realistic diagnostics, run the query at least twice and look at the second run's timing. Use BUFFERS output to see the hit/miss ratio.

EXPLAIN without ANALYZE shows estimated rows only. If you forget the ANALYZE keyword, you're looking at guesses. Always include ANALYZE unless the query is destructive or takes too long to run.

Forcing a plan with SET enable_hashjoin = off is a band-aid. Disabling join strategies globally to force a different plan is fragile. It may help one query while slowing down dozens of others. Fix the root cause — usually stale statistics, a missing index, or inadequate work_mem.

work_mem is per-sort-operation, not per-query. A single query with three sort nodes can use up to 3x the work_mem value. Be careful setting it globally high on a busy server with many concurrent connections.

A Repeatable Diagnosis Process

Rather than poking randomly, use this sequence when you encounter a slow join:

1. Run EXPLAIN (ANALYZE, BUFFERS) and save the full output.
2. Find the highest actual time node — that's where time is actually being spent.
3. Check the estimate vs. actual row count for that node. If they differ by more than 10x, run ANALYZE on the involved tables and re-run.
4. Identify the join strategy being used (nested loop, hash, merge) and check whether it fits the data sizes involved.
5. For nested loops with large outer sets, look for a missing index on the inner table's join key.
6. For hash or merge joins with disk spills, try increasing work_mem in the session and re-run.
7. After any change, re-run EXPLAIN ANALYZE and confirm the plan and timing improved.

Wrapping Up

Reading query plans feels opaque at first, but it becomes mechanical once you know what to look for. The plan tree tells you exactly what Postgres did — your job is to find where the work piled up and give the planner what it needs to do better.

Here are the concrete next steps to take:

• Run EXPLAIN (ANALYZE, BUFFERS) on your slowest JOIN query today and look for large estimate vs. actual row mismatches.
• Check whether the tables involved were recently ANALYZEd using SELECT last_analyze FROM pg_stat_user_tables WHERE relname = 'your_table'.
• For any nested loop with a large loop count, verify an index exists on the inner table's join key and that types match between tables.
• If you see Batches: > 1 in a Hash node or external merge Disk in a Sort node, test with a higher work_mem in your session before committing to a server-wide change.
• Bookmark the explain.depesz.com tool — it color-codes slow nodes in pasted plan output and makes complex plans much easier to scan.