vM.

Reading EXPLAIN ANALYZE When You Are New to PostgreSQL

Author
Vishal Maurya
Published on
Reading time
5 min read

Overview

You run EXPLAIN ANALYZE, and PostgreSQL prints thirty lines of indented nodes, costs and timings. It is easy to stare at it without knowing where to start. You do not need to understand every field. A few checks, done in order, find the cause of most slow queries.

1. Run It With the Useful Options

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days';

ANALYZE actually executes the query and reports real timings. That matters for INSERT, UPDATE and DELETE: they will really change data, so wrap them in a transaction and roll back.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'x' WHERE id < 100;
ROLLBACK;

Plain EXPLAIN without ANALYZE only shows the plan and estimates.

2. Learn the Shape of the Output

Plans are trees. The most indented nodes run first and feed rows to their parents. Each node looks roughly like this:

Index Scan using idx_orders_status on orders o  (cost=0.43..8.45 rows=1 width=16) (actual time=0.020..0.021 rows=1 loops=1)

The first parenthesis holds the planner estimates (cost, rows, width). The second holds what really happened (actual time, rows, loops). The final line gives Planning Time and Execution Time.

Two details trip people up. Actual time is per loop, so multiply by loops for the node total. And cost units are arbitrary planner units, not milliseconds, so compare them only with other costs from the same planner.

3. Find the Node That Takes the Time

Scan the actual time values and find the node where the time jumps. A parent includes the time of its children, so look for the node whose own time, after subtracting its children, is large. That is where to focus. Ignore the rest at first.

4. Compare Estimated Rows With Actual Rows

This is the single most useful check. For each node compare rows= in the estimate with rows= in the actual part.

Seq Scan on orders  (cost=0.00..1850.00 rows=12 width=16) (actual time=0.1..45.2 rows=48000 loops=1)

The planner expected 12 rows and got 48,000. When estimates are off by orders of magnitude, the planner may pick a poor join strategy or scan type because it believes the data is small. Typical causes are stale statistics, correlated columns, or expressions the planner cannot estimate. A first step is to run ANALYZE orders;. For correlated columns, look at extended statistics (CREATE STATISTICS) in the docs.

5. Sequential Scan vs Index Scan

A Seq Scan reads the whole table. That is not automatically bad: for small tables, or queries that return a large share of rows, it is often the fastest choice. It is a problem when it reads a big table to return a few rows.

Seq Scan on orders  (actual time=0.02..310.5 rows=3 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 2400000

Rows Removed by Filter being huge compared with rows returned is the clue that an index on customer_id may help. Other scan types you will see:

  • Index Scan fetches matching rows through an index.
  • Index Only Scan can answer from the index alone, subject to visibility checks.
  • Bitmap Heap Scan collects many matches from an index and reads table pages in order.

6. Read the Buffers Line

With BUFFERS you get lines such as Buffers: shared hit=120 read=8400. A hit came from PostgreSQL shared memory; a read had to be fetched from the operating system or disk. Large read counts on a node mean it is I/O heavy and likely slow on a cold cache. Reducing the buffers touched, by reading fewer rows or using an index, usually reduces time as well. Running the query twice shows how much of the cost is cold cache.

7. Watch for Loops and Sorts

A node with loops=50000 inside a nested loop multiplies its cost. If an inner node is a sequential scan repeated for each outer row, that is a strong sign of a missing index. Also look for Sort Method: external merge Disk: ..., which means the sort spilled to disk because it exceeded work_mem. Raise work_mem carefully, since it applies per sort or hash operation, and consider it per session first.

A Short Checklist

  1. Which node has the largest real time?
  2. Are estimated rows far from actual rows there?
  3. Is a large table scanned to return few rows?
  4. Is something executing thousands of loops?
  5. Did a sort or hash spill to disk?

Conclusion

Reading plans is a skill built from a few repeated checks. Find the slow node, compare estimates with reality, and decide whether an index, fresh statistics or a rewritten query fits.

If you are stuck on a plan that does not make sense, I can help interpret it and propose a fix. Contact me.

Additional Resources

  • PostgreSQL using EXPLAIN
  • PostgreSQL planner statistics
  • PostgreSQL EXPLAIN command reference