A Deep Dive into EXPLAIN ANALYZE

I once handled a ticket reporting that the admin dashboard's user order list took 12 seconds to open. The backend engineer had investigated for ages and said, "We added indexes, and the query is simple, but we still don't know why it's slow." I asked him to send me the output from EXPLAIN ANALYZE. The problem was obvious at a glance: an outer Nested Loop had an inner Seq Scan on orders that ran more than 8,000 times.

Of those 12 seconds, 11.8 were spent in that inner Seq Scan. This was not merely a slow query; it was a query running in the most inefficient way possible. One composite index brought the runtime down from 12 seconds to 80 milliseconds.

After years of tuning PG, one impression has become especially strong: EXPLAIN ANALYZE is the only X-ray machine for database tuning. Other tools, such as slow-query logs, monitoring dashboards, and pg_stat_statements, tell you which query is slow. Only EXPLAIN ANALYZE tells you why it is slow and which step is responsible. Being unable to read an execution plan is like being a doctor who cannot read a CT scan and can only prescribe treatment by intuition.

This article provides a thorough guide to EXPLAIN ANALYZE: the difference between its two modes, how to recognize node types, how to read four key metrics, which signals are red flags, and a practical closed-loop process from slow query to verified optimization.

1. EXPLAIN vs EXPLAIN ANALYZE: Estimates and Reality

Many people use these two commands interchangeably, but they do completely different things.

EXPLAIN: it only plans the query and does not execute it. The optimizer uses statistics to estimate an execution plan, so every cost and rows value you see is a guess. The advantage is that it has no execution cost. Even if a query would run for hours, EXPLAIN usually returns in seconds. The drawback is that its guess may be wrong, sometimes wildly so.

EXPLAIN ANALYZE: it actually executes the query. Be careful: INSERT, UPDATE, and DELETE statements really will change data. It records the actual runtime and row count for every step. The actual time, actual rows, and loops values are what really happened. These are the real basis for tuning.

A comparison makes the difference obvious:

-- EXPLAIN only (estimated)
Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212)

-- EXPLAIN ANALYZE (actually executed)
Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212)
                    (actual time=1.2..6234.8 rows=2 loops=1)

The first line says, "I estimate that this query will return one row." The second says, "It actually scanned for 6.2 seconds and returned two rows." The estimated cost is 485,000, which looks expensive, but the real time is 6,234 milliseconds. That is the pain your users actually experience.

Always use actual time as the authority when tuning. cost is only a dimensionless relative unit used internally by the optimizer to choose among possible plans; it does not represent real seconds. I have seen people look at cost=100 and conclude that a query was cheap, only for it to take 30 seconds in practice because stale statistics had caused the optimizer to draw the entire picture incorrectly.

2. Execution Node Types: From Scans to Joins

An execution plan is a tree. Read it from the inside out and from the bottom up. Each node performs one operation: scanning, joining, sorting, or aggregating. Once you understand the node types, you understand what the query is doing.

1. Seq Scan (sequential full-table scan)

This reads the entire table from the first page to the last. That is fine for small tables. PG pages are 8 KB, so scanning a table with a few thousand rows takes only microseconds. On a large table, a Seq Scan usually means that the right index is missing or not being used. This is the most common root cause of slow queries.

2. Index Scan / Index Only Scan / Bitmap Index Scan (index scans)

These are three ways to use an index, each suited to a different scenario:

• Index Scan: follows the index to find row pointers, then reads the corresponding rows from the table. It is suitable when only a small number of rows will be returned.
• Index Only Scan: the index contains every column the query needs, so the table itself does not need to be read. This is the ideal case. To achieve it, create a covering index that includes all the columns referenced by the SELECT.
• Bitmap Index Scan + Bitmap Heap Scan: first collects matching row locations from the index into a bitmap, then reads the table in batches. This is suitable for returning a moderate number of rows, from several thousand to tens of thousands. It is more efficient than a plain Index Scan and reads less than a Seq Scan.

3. Join nodes: Nested Loop / Hash Join / Merge Join

The optimizer automatically chooses among three JOIN strategies based on data volume and available indexes:

• Nested Loop: scans the inner side once for every row on the outer side. With 100 outer rows and 1,000 inner rows, that produces 100,000 inner scans. This is reasonable only when the inner side has an index or is very small. Otherwise, it becomes an N+1-style disaster.
• Hash Join: builds a hash table from the entire inner input, then scans the outer input and probes the hash. It is well suited to equality joins between large tables without indexes. This is PG's main workhorse for analytical queries.
• Merge Join: sorts both sides by the join key and merges them. Its inputs must be ordered, either through indexes or an earlier Sort. It works well for range and equality joins.

4. Sort and Aggregate

ORDER BY triggers a Sort, while GROUP BY, COUNT, and SUM trigger an Aggregate. For Sort nodes, pay close attention to Sort Method, which is covered in the fourth section. For Aggregate nodes, check whether PG uses Hash Aggregate, which hashes groups in memory, or Group Aggregate, which requires sorted input.

3. Key Metrics: cost, actual time, rows, and loops

A node line looks roughly like this:

Hash Join  (cost=1234.56..7890.12 rows=1000 width=64)
           (actual time=45.2..612.8 rows=982 loops=1)

There are four metrics to understand.

1. cost: estimated cost, for reference only

The format is cost=startup cost..total cost. Startup cost is the cost of producing the first row, which matters for LIMIT and EXISTS. Total cost is the cost of completing the entire operation. The key point is that this is only an estimate, derived from statistics, and it may be wrong. Do not treat cost as real seconds.

2. actual time: real execution time, so use it as the authority

The format is actual time=startup milliseconds..total milliseconds, and the unit is milliseconds. It appears only with EXPLAIN ANALYZE. This is the metric I watch most closely when reading a plan. Find the node with the largest actual time, and you have usually found the bottleneck.

3. rows: estimate vs actual; a large discrepancy means stale statistics

The rows=1000 on the cost line is the estimated row count, while the rows=982 on the actual line is the actual count. If they are close, the statistics are accurate. If they differ by several orders of magnitude, the statistics are stale and the optimizer is guessing blindly. When the discrepancy is large, running ANALYZE table_name can often transform the plan completely.

4. loops: execution count; multiply actual values by it

loops=N means that the node was executed N times. Both actual time and actual rows are per-loop averages. To calculate total time, use time × loops. Many people miss this trap: actual time=0.5 looks fast, but with loops=200000, the total is 100 seconds. That is the real culprit.

4. Warning Signs: Four Typical Pathological Plans

When reading EXPLAIN output, I mainly look for the following red flags.

Signal 1: Seq Scan on a large table (missing index)

Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212)
                    (actual time=1.2..6234.8 rows=2 loops=1)
  Filter: (user_id = 12345)
  Rows Removed by Filter: 19999998

The query scans a table with 20 million rows, rejects 19,999,998 of them, and keeps only two. An index is clearly missing. Add CREATE INDEX ON orders(user_id), and the Seq Scan becomes an Index Scan, reducing six seconds to milliseconds.

Signal 2: rows estimates 1, but the actual count is 100000 (stale statistics)

Nested Loop  (cost=0.58..12.34 rows=1 width=100)
             (actual time=0.5..8234.1 rows=102456 loops=1)

The optimizer expected only one row, so it selected a Nested Loop because it appeared cheap. The query actually returned 100,000 rows, causing the inner side to be scanned 100,000 times. The root cause is stale statistics: the optimizer chose the wrong strategy based on incorrect data. Run ANALYZE to update the statistics, and the next execution plan will automatically switch to a Hash Join.

Signal 3: Sort Method: external merge (sorting spilled to disk)

Sort  (cost=45678.00..48234.00 rows=100000 width=80)
      (actual time=234.5..1876.3 rows=100000 loops=1)
  Sort Key: created_at
  Sort Method: external merge  Disk: 18432kB
  Buffers: shared hit=1234 read=5678, temp read=2345 written=2345

external merge plus Disk means the sort lacked enough memory and spilled to disk. Disk sorting is one or two orders of magnitude slower than in-memory sorting. The temp read/written values show how much temporary data was paged. The solution is to increase work_mem, either globally or for the current session with SET work_mem = '64MB', until the method becomes Sort Method: quicksort Memory. This change alone often saves hundreds of milliseconds or several seconds.

Signal 4: Nested Loop with an inner Seq Scan (an N+1-style disaster)

Nested Loop  (actual time=0.3..11234.5 rows=8234 loops=1)
  -> Index Scan on users  (actual ... rows=8234 loops=1)
  -> Seq Scan on orders  (actual time=0.2..1.3 rows=1500 loops=8234)
        Filter: (user_id = users.id)

The outer side returns 8,234 rows, while the inner Seq Scan reads the entire table every time, for a total of 8,234 full scans. This is a classic N+1 problem. Add an index on orders.user_id so the inner node becomes an Index Scan, or rewrite the query to use a Hash Join, and the problem disappears immediately. When you see this plan, it is usually a sign that you will leave work on time today.

5. Practical Workflow: A Closed Loop from Slow Query to Optimization

I use a fixed process for slow queries and never rely on tweaking things by feel:

1. Get the exact slow query. Retrieve it from pg_stat_statements or the slow-query log. Do not retype it from memory because its parameters and real execution scenario may differ substantially.
2. Run EXPLAIN (ANALYZE, BUFFERS) once. Be careful with ANALYZE on write statements in production because it really changes data. SELECT is safe; UPDATE and DELETE can be wrapped in a transaction and followed by ROLLBACK.
3. Read the plan from the bottom up and find the node with the largest actual time. Bottlenecks are usually concentrated in one or two nodes, not spread across the whole plan. Do not focus only on the root node's total time; find the child node consuming most of it.
4. Form a hypothesis: is an index missing? Are the statistics stale? Is work_mem too small? Did the optimizer choose the wrong JOIN strategy?
5. Apply the targeted fix: add an index, run ANALYZE, adjust work_mem, or rewrite the query.
6. Test again: rerun EXPLAIN ANALYZE and compare the actual time. No improvement? Try a different hypothesis. Improved? Record the result for future review.

Every step follows measurement -> hypothesis -> validation. That is engineering discipline, not mysticism.

6. BUFFERS: See the Truth About I/O

The third option in EXPLAIN (ANALYZE, BUFFERS), BUFFERS, is a severely underrated tool. It tells you how much each node read from cache and disk:

Index Scan on orders  (actual time=0.5..12.3 rows=42 loops=1)
  Buffers: shared hit=8 read=4

• shared hit: the page was found in shared_buffers, so no disk I/O was required.
• shared read: the page was not cached, so it was read from disk.
• temp read/written: work_mem was insufficient, so data spilled to temporary files in Sort or Hash nodes.

The hit ratio, calculated as hit / (hit+read), is the key metric. Below 90% generally means the cache does not cover the working set. Either the data volume far exceeds memory, shared_buffers is too small, or the query scans too broad a range. When a query is slow, BUFFERS often points in the right direction immediately by making it obvious whether the problem is I/O or CPU.

7. Do Not Use EXPLAIN Without ANALYZE, and Other Pitfalls

Finally, several hard-earned lessons deserve emphasis:

Pitfall 1: Running EXPLAIN without ANALYZE. As noted earlier, plain EXPLAIN contains estimates that may be wrong. I have seen an optimizer estimate rows=1 when the actual result was 10 million rows. It chose a Nested Loop based on that mistake, and production performance became painfully slow. Tuning requires actual values, not estimated ones.

Pitfall 2: Stale statistics make the entire plan wrong. PG stores statistics in pg_statistic, and autovacuum updates them periodically. However, statistics often lag on large tables, tables that have just received a bulk import, and tables whose schema has just changed. Run ANALYZE before tuning; many mysterious problems will disappear automatically.

Pitfall 3: EXPLAIN ANALYZE really executes the statement. SELECT has no side effects, but INSERT, UPDATE, and DELETE really do modify data. When debugging a write statement in production, always wrap it in BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; so the changes are rolled back immediately.

Pitfall 4: Forgetting multiplication when loops is high. actual time is the per-loop average. With 100,000 loops, it accumulates 100,000 times. Always calculate the full total when reading a plan.

Pitfall 5: Cost parameters lack context. A cost value is meaningful only when comparing alternative plans for the same query. Comparing cost across queries is meaningless. The optimizer uses it to select plans; you should not use it as the basis for tuning.

Summary

• EXPLAIN is an estimate; EXPLAIN ANALYZE reveals reality. Always tune using actual time and actual rows. cost is only a relative value used internally by the optimizer to select a plan and may diverge severely from reality.
• Node types reveal problems at a glance: a Seq Scan on a large table means a missing index; Index Only Scan is the ideal case; Nested Loop with an inner Seq Scan is an N+1 problem; Hash Join is a healthy choice for large-table joins; and Sort Method: external merge means work_mem is insufficient.
• Learn to read all four metrics: cost is an estimate for reference only, actual time is real execution time and the authority, estimated vs actual rows reveals when ANALYZE is needed, and loops must be multiplied into time.
• Watch for four red flags: Seq Scan on a large table, row estimates that differ from reality by several orders of magnitude, sorting that spills to disk, and a Nested Loop containing a Seq Scan. Any one of them will usually reveal the bottleneck.
• Use BUFFERS to see the truth about I/O: a high shared hit count is healthy, many shared read operations mean cache misses, and high temp usage means work_mem is insufficient. A hit ratio below 90% leaves room for optimization.

One final sentence: EXPLAIN ANALYZE is not just a report; it is a CT scan for a query. Understand every node and every line of actual time, and you will know more than the optimizer about what this SQL actually does on your machine.