Skip to content
Tutorials Center
My learning

Topics

Lesson 61 of 90 ยท Performance Tuning

Reading Slow Plans

Learn to find the node that makes a PostgreSQL plan slow: row misestimates, nested loop vs hash vs merge joins, sorts that spill to disk, work_mem and JIT.

EXPLAIN and EXPLAIN ANALYZE taught you to read a plan from the inside out. This lesson is about the next skill: looking at a plan that is slow and finding the one node that explains why. Most slow plans have a single culprit, and it nearly always falls into a short list: a bad row estimate, the wrong join method, a sort or hash that ran out of memory, or work that was repeated far too many times.

Everything about PostgreSQL's own plan output is shown in read-only blocks here, because our in-browser engine is SQLite. The live examples use EXPLAIN QUERY PLAN on SQLite to demonstrate the same ideas (join order, index use, temporary sort structures) on small data you can change and re-run.

Start with estimates versus reality

EXPLAIN ANALYZE prints two row counts for every node: the planner's guess (rows=, inside the cost parentheses) and what really happened (actual ... rows=). A large gap between them is the single most useful signal in plan reading, because every later decision (scan type, join method, memory request) was made from the guess.

TEXT
Nested Loop  (cost=0.85..412.30 rows=3 width=48)
             (actual time=0.04..9810.22 rows=240118 loops=1)
  ->  Index Scan using orders_status_idx on orders  (cost=0.43..8.45 rows=1 width=16)
                                                    (actual time=0.02..61.5 rows=240118 loops=1)
        Index Cond: (status = 'refunded'::text)
  ->  Index Scan using customers_pkey on customers  (cost=0.42..8.44 rows=1 width=40)
                                                    (actual time=0.03..0.04 rows=1 loops=240118)

The planner expected one refunded order and found 240,118. With one expected row, a nested loop looked cheap. With 240,118 rows it runs the inner index scan that many times. The fix is not to fiddle with this query; it is to give the planner better information (see Statistics and the Query Planner).

Remember two reading rules:

  • Per-loop numbers. actual time and rows on a node are averages per loop. A node with actual time=0.04 and loops=240118 really cost about 9.6 seconds in total.
  • Estimates compound. An error of 10x at a scan can become 1,000x after two joins. Find the first node, working from the leaves up, where estimate and actual diverge. That is usually where the root cause lives.

Where slow plans hide

Bad estimates Rows guessed far from rows found.
Too many loops A cheap node run a million times.
Spills Sorts and hashes that left memory.
Wasted reads Lots of rows read, few kept.

Find the first node where reality leaves the estimate.

1 / 2

Three ways to join

PostgreSQL has three join algorithms, and the planner picks one per join from its row estimates. Knowing what each is good at tells you when a choice is wrong.

Method How it works Good when Bad when
Nested Loop For each row of the outer side, probe the inner side (ideally an index lookup) The outer side is small and the inner side has an index The outer side is large: the inner side runs once per outer row
Hash Join Build an in-memory hash table from one side, stream the other side through it Larger inputs, equality conditions, no useful index The hash table does not fit in memory and splits into batches
Merge Join Walk two inputs sorted on the join key in step Both sides already sorted (for example by index), or very large sorted inputs Sorting is needed first and is expensive

Nested loops are the most common source of "it was fast in testing" surprises. They are wonderful for 10 outer rows and a disaster for 10 million. If you see a nested loop with a huge loops= count on the inner node, the planner underestimated the outer side.

Hash joins show their cost in the Hash node:

TEXT
Hash  (cost=1250.00..1250.00 rows=100000 width=12) (actual time=95.1..95.1 rows=100000 loops=1)
  Buckets: 131072  Batches: 8  Memory Usage: 4096kB

Batches: 1 means the whole hash table fit in memory. Batches: 8 means it did not, so PostgreSQL wrote parts of both inputs to temporary files and processed them in eight rounds. That is a spill: correct, but much slower.

You can see the shape of a nested loop in SQLite too. With no index on the join column, SQLite builds a throwaway index (its way of avoiding a quadratic scan). With a real index, each outer row becomes a cheap lookup.

SQL Join order and index lookups

Press Run (or Ctrl + Enter) to see the result here.

Without the index, SQLite scans all 20,000 orders (SCAN o) and looks each one up in customers by primary key, so the big table drives the loop. After CREATE INDEX, the plan flips: SCAN c reads the customers (applying the country filter) and SEARCH o USING INDEX orders_customer_idx (customer_id=?) fetches just their orders. The small filtered side drives lookups into the big table, which is exactly the situation where a PostgreSQL nested loop shines.

Sorts and work_mem

Every sort (ORDER BY, DISTINCT, merge joins, window functions, GROUP BY via sorting) and every hash (hash joins, hash aggregates) is limited by work_mem, which defaults to 4MB per node, per query. If the data fits, you see an in-memory method. If not, PostgreSQL spills to temporary files:

TEXT
Sort  (actual time=1840.2..2310.7 rows=2000000 loops=1)
  Sort Key: created_at
  Sort Method: external merge  Disk: 79120kB

Compare with a healthy sort:

TEXT
Sort  (actual time=21.3..22.0 rows=11000 loops=1)
  Sort Key: created_at
  Sort Method: quicksort  Memory: 1250kB

Three standard remedies, in order of preference:

  1. Avoid the sort. An index that already delivers rows in the needed order removes the Sort node entirely, and also helps LIMIT queries stop early.
  2. Sort less. Filter earlier, select fewer columns (narrower rows mean smaller sorts), or aggregate before sorting.
  3. Raise work_mem for this query. Use SET LOCAL work_mem = '256MB' inside a transaction for the one heavy report rather than raising it for the whole server. Raising it globally multiplies by every node of every connection (see Configuration Tuning).

Since PostgreSQL 15, hash-based nodes may use work_mem * hash_mem_multiplier (default 2.0), so hashes get a little more room than sorts before they spill.

EXPLAIN (ANALYZE, BUFFERS) also reports temp read and temp written buffers on nodes that used temporary files, which is a quick way to spot spills even in a big plan. From PostgreSQL 18 on, EXPLAIN ANALYZE includes buffer counts by default.

SQLite shows the same idea in a different spelling. When no index gives the requested order, it announces USE TEMP B-TREE FOR ORDER BY:

SQL When a sort can be avoided

Press Run (or Ctrl + Enter) to see the result here.

The first plan ends with USE TEMP B-TREE FOR ORDER BY; the second is a single SEARCH ... USING COVERING INDEX events_user_time_idx (user_id=?) with no sort, because (user_id, created_at) already stores that user's rows in date order. In PostgreSQL the same index would replace a Sort node with a plain Index Scan.

JIT compilation

Since PostgreSQL 12, queries whose estimated cost exceeds jit_above_cost (default 100000) can be compiled to machine code at run time. The end of an EXPLAIN ANALYZE shows what it cost:

TEXT
JIT:
  Functions: 14
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 2.1 ms, Inlining 0.0 ms, Optimization 1.4 ms, Emission 14.8 ms, Total 18.3 ms
Execution Time: 412.7 ms

JIT helps long analytical queries and hurts short ones that were estimated to be expensive. If a query that really takes 40 ms shows 300 ms of JIT time, the estimate was wrong (go back to the first section), and you can turn JIT off for that session with SET jit = off while you fix the estimate. On OLTP systems many teams disable JIT globally; on reporting systems it is usually worth keeping.

Wasted work: rows removed by filters

Two lines inside scan nodes reveal a different kind of slowness, where the plan reads far more than it keeps:

TEXT
Seq Scan on events  (actual time=0.01..310.5 rows=12 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 3999988

Four million rows were read to return twelve. That is an indexing problem (the predicate needs an index, or the existing index is not usable; see How Indexes Work in PostgreSQL). Similarly, Rows Removed by Index Recheck on a bitmap scan means the bitmap became lossy because work_mem was too small, and Heap Fetches on an index-only scan means the visibility map is stale, so vacuum has some catching up to do.

Tools that draw the plan

Reading a 200-line plan as text is hard. Plan visualizers parse EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output and highlight the expensive nodes:

  • pgAdmin shows a graphical plan with arrow thickness proportional to rows.
  • explain.dalibo.com (the open-source PEV2 viewer) and explain.depesz.com color nodes by exclusive time and flag misestimates.
  • auto_explain logs slow plans automatically so you can examine them after the fact (see Finding Slow Queries with pg_stat_statements).

Be careful with pasting plans into public web tools: plans can include table names, column names and literal values from your query.

A plan shows only what the engine did. It never tells you why the planner chose it. When you cannot explain a choice, the answer is nearly always in the statistics the planner used, which is the subject of the next lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A node shows actual time=0.05..0.06 rows=1 loops=200000. Roughly how much time did it use in total?

    Actual time is per loop, so multiply by the loop count: 0.06 ms times 200,000 is about 12,000 ms, or 12 seconds.
  2. 2. Which plan symptom most clearly means a hash join did not fit in memory?

    More than one batch means PostgreSQL wrote part of the data to temporary files and processed it in rounds. Batches: 1 is the in-memory case.
  3. 3. The planner estimated 1 row but the node returned 240,000 rows, and a nested loop was chosen. What is the best first step?

    The join method was a reasonable response to a wrong estimate. Correct the information (ANALYZE, higher statistics target, extended statistics) and the planner will usually choose better by itself.
  4. 4. How can you give one heavy report more sort memory without affecting other sessions?

    SET LOCAL changes the setting for the current transaction only, so the large allowance cannot multiply across every connection.

Summary

  • Compare estimated and actual rows on every node; the first big gap, found from the leaves up, usually holds the root cause.
  • Actual time and rows are per loop: multiply by loops to get the real cost.
  • Nested loops suit small outer sides with an indexed inner side, hash joins suit large unsorted equality joins, and merge joins suit pre-sorted inputs.
  • Sort Method: external merge Disk and Batches above 1 are memory spills; avoid the sort with an index, sort less, or raise work_mem locally.
  • JIT pays off only for genuinely long queries; a wrong estimate can trigger it needlessly.
  • Rows Removed by Filter shows wasted reads, a hint that an index is missing or unusable.
  • Plan visualizers make long plans readable; mind what the plan text reveals before sharing it.

Next: Statistics and the Query Planner.