How PostgreSQL Executes a Query: Reading EXPLAIN ANALYZE

The Query Is Simple, but Production Takes 840 ms#

An endpoint returns the 50 most recent pending orders for one tenant:

SELECT
  o.id,
  o.status,
  o.total_minor,
  o.created_at,
  c.name AS customer_name
FROM orders o
JOIN customers c
  ON c.id = o.customer_id
WHERE o.tenant_id = $1
  AND o.status = 'pending'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;

It looks harmless. It returns only 50 rows and joins customers through a primary key. On a development database it finishes in 4 ms. In production it takes around 840 ms for a busy tenant.

The SQL text tells us what result we requested. It does not tell us how PostgreSQL found it.

Did PostgreSQL scan two million orders? Did it choose the wrong join? Did a sort spill to disk? Did it expect 20 rows and receive 50,000? Was the data already cached during the fast test?

EXPLAIN ANALYZE answers those questions with evidence.

EXPLAIN Predicts; EXPLAIN ANALYZE Executes#

Start with the safest form:

EXPLAIN
SELECT ...

EXPLAIN asks the planner to choose a plan but does not execute the statement. It reports estimated cost and rows.

To compare the estimates with reality:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

ANALYZE here is an EXPLAIN option. It executes the statement and records actual timing, rows, and loops. BUFFERS reports the database pages touched.

That distinction matters:

• EXPLAIN SELECT plans the read.
• EXPLAIN ANALYZE SELECT runs the read.
• EXPLAIN ANALYZE UPDATE performs the update.
• EXPLAIN ANALYZE DELETE performs the delete.

Wrapping a write in a transaction and rolling it back can protect table changes:

BEGIN;
 
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders
SET status = 'expired'
WHERE expires_at < now()
  AND status = 'pending';
 
ROLLBACK;

But this is not harmless. The statement still consumes CPU and I/O, acquires locks, fires triggers, and can generate WAL. Sequence increments are not rolled back, and functions or triggers may produce external side effects.

Prefer a controlled environment or safe replica with production-like data. On production, begin with plain EXPLAIN, existing query telemetry, and a carefully reviewed plan for collecting actual execution data.

Read the Plan From the Inside Out#

Here is an illustrative slow plan for our Order query:

Limit  (cost=39842.21..39842.34 rows=50 width=49)
       (actual time=838.912..838.929 rows=50 loops=1)
  Buffers: shared hit=12410 read=6732
  ->  Sort  (cost=39842.21..39892.12 rows=19964 width=49)
            (actual time=838.910..838.918 rows=50 loops=1)
        Sort Key: o.created_at DESC, o.id DESC
        Sort Method: top-N heapsort  Memory: 39kB
        Buffers: shared hit=12410 read=6732
        ->  Hash Join  (cost=1812.00..39179.08 rows=19964 width=49)
                       (actual time=22.417..819.312 rows=48720 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=12407 read=6732
              ->  Seq Scan on orders o
                    (cost=0.00..37239.00 rows=19964 width=41)
                    (actual time=0.042..748.221 rows=48720 loops=1)
                    Filter: (
                      (tenant_id = 'tenant_42'::uuid)
                      AND (status = 'pending'::order_status)
                    )
                    Rows Removed by Filter: 1951280
                    Buffers: shared hit=11201 read=6732
              ->  Hash  (cost=1437.00..1437.00 rows=30000 width=24)
                        (actual time=21.891..21.892 rows=30000 loops=1)
                    Buckets: 32768  Batches: 1  Memory Usage: 1978kB
                    Buffers: shared hit=1206
                    ->  Seq Scan on customers c
                          (cost=0.00..1437.00 rows=30000 width=24)
                          (actual time=0.010..10.842 rows=30000 loops=1)
                          Buffers: shared hit=1206
Planning:
  Buffers: shared hit=182
Planning Time: 1.214 ms
Execution Time: 839.104 ms

Each indented child produces rows for its parent:

orders scan ─┐
             ├→ hash join → top-N sort → limit → response
customers ───┘

Read the deepest nodes first:

1. PostgreSQL scans orders.
2. It filters almost two million rows and keeps 48,720.
3. It scans and hashes customers.
4. It joins the matching orders to customers.
5. It finds the newest 50 rows with a top-N sort.
6. The Limit returns those 50 rows.

The root says the query returned 50 rows in about 839 ms. The cause is lower in the tree: PostgreSQL reads every order because it has no useful access path for this tenant, status, and ordering.

Understand One Plan Node#

Consider the order scan:

Seq Scan on orders o
  (cost=0.00..37239.00 rows=19964 width=41)
  (actual time=0.042..748.221 rows=48720 loops=1)

Startup and total cost#

cost=0.00..37239.00 contains planner cost units, not milliseconds.

• 0.00 is the estimated work before the node can produce its first row.
• 37239.00 is the estimated work to produce all rows from the node.

Costs help PostgreSQL compare possible plans under its configured cost model. Do not read 37239.00 as 37 seconds or compare it directly with application latency.

Estimated rows and width#

rows=19964 is the planner's estimate of rows produced by this node per execution. width=41 is the estimated average bytes carried by each output row.

These values influence join order, join algorithm, sort cost, hash-table size, and parallelism.

Actual time#

actual time=0.042..748.221 reports the average time per loop:

• About 0.042 ms before the first row was available.
• About 748.221 ms until the node completed.

Node time includes work performed by its children. Do not add every node's total time: that double-counts child work.

Actual rows and loops#

rows=48720 loops=1 means the node returned 48,720 rows per loop and ran once.

When a node runs many times, the displayed rows and timing are averages per loop. Multiplying by loops helps estimate repeated work, but it remains an approximation because instrumentation, caching, and inclusive child time affect the result.

Rows removed by filter#

Rows Removed by Filter: 1951280

PostgreSQL inspected roughly two million order rows to return 48,720 from this scan. Returning only 50 rows at the root did not mean the database processed only 50.

Like actual rows, rows removed is averaged per loop when a node repeats.

Estimates Decide the Plan#

The planner must choose before it knows the actual result. It relies on table statistics and assumptions about data distribution.

In our plan:

estimated rows: 19,964
actual rows:    48,720

This mismatch is meaningful but not catastrophic. Larger errors can cause much worse choices:

estimated rows: 10
actual rows:    498,214

PostgreSQL may choose a nested loop because it expects ten cheap index lookups. If the outer node produces nearly 500,000 rows, the inner lookup may execute nearly 500,000 times.

Common causes include:

• Stale statistics after substantial data changes
• Skewed values, such as one tenant owning most pending orders
• Correlated columns treated as independent
• Expressions or casts that statistics cannot describe well
• Parameter values that differ from those used during testing
• Generic prepared-statement plans that ignore a specific value's selectivity

Refresh statistics when appropriate:

ANALYZE orders;

For important columns with complicated distributions, a targeted statistics value may help:

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

Higher targets improve detail but increase statistics size, planning work, and analyze time. Do not increase them globally without evidence.

If tenant_id and status are correlated, extended statistics can describe that relationship:

CREATE STATISTICS orders_tenant_status_stats
  (dependencies, mcv)
ON tenant_id, status
FROM orders;
 
ANALYZE orders;

Statistics improve estimates. They do not replace an index when the query still needs an efficient way to reach a small ordered subset.

Scan Types Answer Different Questions#

Sequential scan#

A sequential scan reads table pages and checks rows. It is often correct when:

• The table is small.
• The query needs a large fraction of rows.
• No useful index exists.
• Random heap access would cost more than reading the table once.

The word Seq Scan is not itself a problem. In our query, it is expensive because the endpoint needs 50 rows while the scan examines roughly two million.

Index scan#

An index scan navigates the index to find matching row locations, then visits the table heap for needed values.

Look for the difference between:

Index Cond: (tenant_id = 'tenant_42')
Filter: (status = 'pending')

Index Cond limits what the index visits. A Filter is applied after PostgreSQL fetches candidate rows. A plan can use an index yet still discard enormous numbers of rows.

Index-only scan#

An index-only scan can return columns from the index without always reading the heap. It still may perform heap fetches when PostgreSQL's visibility map cannot prove that tuples are visible to the current snapshot.

Index Only Scan using ...
Heap Fetches: 18420

A large heap-fetch count explains why an “index-only” plan still performs substantial table I/O.

Bitmap index and heap scans#

A bitmap scan gathers matching row locations and visits heap pages in batches. It is useful when many rows match and ordinary index access would jump through the heap inefficiently.

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'pending')
  -> Bitmap Index Scan on orders_status_idx

When the bitmap becomes lossy, PostgreSQL tracks pages rather than every tuple and must recheck conditions. Inspect Rows Removed by Index Recheck and memory pressure.

The PostgreSQL index guide covers B-tree order, selectivity, covering indexes, and write costs in more depth.

Join Algorithms Reflect Row Counts and Access Paths#

Nested loop#

Nested Loop
  -> outer rows
  -> inner index lookup  loops=100000

A nested loop is excellent when the outer side is small and the inner lookup is cheap. If the inner lookup averages 0.08 ms and runs 100,000 times, repeated work alone is roughly eight seconds.

The algorithm is not inherently bad. A wrong row estimate or missing inner index makes it bad for a particular workload.

Hash join#

A hash join builds an in-memory hash table from one input and probes it with the other. It is effective for equality joins over larger inputs.

Hash
  Buckets: 32768  Batches: 1  Memory Usage: 1978kB

Batches: 1 means the hash table stayed in memory. Multiple batches indicate partitioning and temporary I/O because the operation exceeded available memory.

Merge join#

A merge join walks two inputs ordered by the join key. It can be efficient when indexes already provide that order. If PostgreSQL must explicitly sort both inputs first, those sorts may dominate the query.

Do not force a join algorithm because another one sounds faster. Fix inaccurate estimates and access paths, then measure the plan PostgreSQL chooses.

Sorts, Hashes, and Aggregates Can Spill#

Our slow plan uses:

Sort Method: top-N heapsort  Memory: 39kB

Because the query has LIMIT 50, PostgreSQL keeps only the best candidates instead of fully sorting 48,720 rows. The sort is not the main bottleneck.

A disk spill looks different:

Sort Method: external merge  Disk: 184320kB
Buffers: temp read=23040 written=23112

Hash joins and hash aggregates can also use multiple batches or report disk usage.

work_mem applies to each eligible sort or hash operation, not once per database connection. One query can have several nodes, and many queries can execute concurrently:

100 connections
× 3 memory-consuming nodes
× 64 MB work_mem
= potential memory pressure far beyond 64 MB

Tune it for a measured workload or transaction. Raising it globally to remove one spill can create an outage under concurrency.

Buffers Explain Pages, Not Complete Storage Latency#

Our order scan reports:

Buffers: shared hit=11201 read=6732

• shared hit: the page was already in PostgreSQL shared buffers.
• shared read: PostgreSQL loaded the page into shared buffers.
• dirtied: the operation changed a page in memory.
• written: PostgreSQL wrote a dirty page during the operation.
• temp read/written: a sort, hash, or materialization used temporary files.

A shared read does not prove a physical disk read. The operating-system page cache may satisfy it. Buffer counts are still valuable because they show how much data the query touches independent of a single timing result.

When track_io_timing is enabled, plans can include I/O timing. That adds more evidence, but it also has overhead that depends on the platform and configuration.

Cold, warm, and production-cache conditions can produce different timings for the same plan:

cold run  → pages loaded into cache
warm run  → many shared-buffer hits
failover  → cache begins cold again

Compare both time and pages touched. A query that is fast only because millions of relevant pages happen to be cached is still fragile.

Loops Reveal Repeated Work#

Consider a customer lookup inside a nested loop:

Index Scan using customers_pkey on customers
  (actual time=0.012..0.013 rows=1 loops=48720)

One lookup is cheap. Repeating it 48,720 times may not be.

Ask:

• Why does the outer node return this many rows?
• Can the query apply LIMIT before repeated work?
• Is the inner lookup indexed?
• Are repeated keys being looked up unnecessarily?
• Did a row-estimate error cause the nested loop choice?

PostgreSQL may introduce a Memoize node for repeated parameterized lookups. Inspect its cache hits, misses, evictions, and memory use rather than assuming it eliminates all repeated work.

Parallel Plans Need Their Own Reading#

Large scans may use:

Gather
  Workers Planned: 4
  Workers Launched: 2
  -> Parallel Seq Scan on orders

The leader combines rows from workers. Gather Merge also preserves an order supplied by workers.

Check:

• Workers planned versus launched
• Row counts and loops across workers
• Whether startup and coordination cost outweigh the gain
• Whether another workload prevented workers from launching
• Whether the query still reads far more pages than necessary

Parallelism can make a large scan faster. It does not make scanning unnecessary data free.

Fix the First Expensive Divergence#

Our query needs rows matching:

tenant_id = ?
status = 'pending'
ORDER BY created_at DESC, id DESC
LIMIT 50

A candidate index is:

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (
  tenant_id,
  status,
  created_at DESC,
  id DESC
)
INCLUDE (customer_id, total_minor);

This is not automatically the correct production index. Verify existing indexes, status selectivity, write amplification, index size, maintenance cost, and whether all selected columns belong in INCLUDE.

After creating the candidate safely and refreshing statistics, an illustrative plan might become:

Limit  (cost=0.72..94.18 rows=50 width=49)
       (actual time=0.086..6.921 rows=50 loops=1)
  Buffers: shared hit=214 read=18
  ->  Nested Loop  (cost=0.72..37315.84 rows=19964 width=49)
                    (actual time=0.085..6.912 rows=50 loops=1)
        Buffers: shared hit=214 read=18
        ->  Index Scan using orders_tenant_status_created_idx on orders o
              (cost=0.43..14218.60 rows=19964 width=41)
              (actual time=0.052..1.284 rows=50 loops=1)
              Index Cond: (
                (tenant_id = 'tenant_42'::uuid)
                AND (status = 'pending'::order_status)
              )
              Buffers: shared hit=64 read=18
        ->  Index Scan using customers_pkey on customers c
              (cost=0.29..1.15 rows=1 width=24)
              (actual time=0.109..0.109 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
              Buffers: shared hit=150
Planning Time: 1.482 ms
Execution Time: 7.014 ms

The improvement is structural:

The new plan uses a nested loop, which is now appropriate: the ordered index allows LIMIT to stop after 50 orders, so only 50 customer lookups run.

Validate more than one tenant. A plan that works for a typical tenant may behave differently for the largest tenant or a rare status.

Planning Time and Prepared Statements Matter#

The bottom of the plan separates:

Planning Time: 1.482 ms
Execution Time: 7.014 ms

Planning is normally small, but complex generated SQL with many joins or partitions can spend meaningful time planning. At high request rates, even small per-execution costs accumulate.

Prepared statements introduce another question: should PostgreSQL use a custom plan for the current parameters or reuse a generic plan?

For skewed tenants:

tenant_small → 200 orders
tenant_large → 20,000,000 orders

One generic plan may not suit both. Capture the actual parameter distribution and determine whether the application uses prepared statements, a connection pool, or an ORM that changes planning behavior. The production slow-query guide explores plan and environment differences in more depth.

Useful EXPLAIN Variants#

For most read investigations:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

To reduce per-node timing overhead while keeping actual rows and total execution time:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT ...;

For writes or queries where WAL generation matters:

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE ...;

To expose non-default settings that may influence the plan:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;

For tools and automated comparison:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

VERBOSE can show output columns and fully qualified details, but it also makes plans larger. Use only the options that answer the current question.

A Production Investigation Workflow#

1. Start from user impact#

Identify the endpoint, job, or transaction that is slow. Record latency percentiles, error rate, time window, deployment changes, and affected tenants.

2. Find the actual database statement#

Use application traces, structured logs, and pg_stat_statements to find normalized queries with high total time, mean time, calls, rows, and block activity.

A 20 ms query executed 20,000 times per second may deserve attention before a five-second report that runs once per day.

3. Capture representative parameters#

Do not diagnose only with a convenient tenant or date. Compare typical, worst-case, and recently slow parameters. Preserve privacy when sharing plans because literals and schema names can expose sensitive data.

4. Reproduce safely#

Use production-like row counts, value distributions, statistics, indexes, PostgreSQL settings, and cache conditions. Prefer a controlled replica or staging copy where EXPLAIN ANALYZE cannot harm production traffic.

5. Read the plan inside out#

Find the first important divergence:

• Estimated rows versus actual rows
• Rows removed by filter or index recheck
• A node repeated many times
• Excessive shared or temporary buffers
• Disk spills or hash batches
• Workers not launched
• A scan or sort that prevents early LIMIT

6. Change one thing#

Change one query, index, statistic, or configuration setting. Several simultaneous changes make it difficult to know what helped and harder to roll back safely.

7. Compare evidence#

Compare the complete before-and-after plan, not only execution time:

• Rows processed
• Loops
• Buffer activity
• Spill behavior
• Estimate accuracy
• Planning and execution time

8. Verify the workload#

Measure the endpoint and database under realistic concurrency. Confirm that write latency, CPU, memory, storage, replication lag, and connection pressure did not regress.

Common Plan-Reading Mistakes#

Treating every sequential scan as bad#

Reading most of a table sequentially can be cheaper than random index and heap access.

Reading cost as milliseconds#

Planner cost is a relative model, not elapsed time.

Looking only at the root node#

The root reports the final result. The expensive divergence is often deep in a child.

Ignoring loops#

A cheap inner node can dominate when it runs hundreds of thousands of times.

Adding node times together#

Parent timing includes child work. Summing all nodes double-counts execution.

Assuming shared reads are physical reads#

The operating system may satisfy them from its cache.

Testing only on a warm laptop#

Small data, uniform values, warm caches, and no concurrency hide production behavior.

Increasing work_mem globally#

It applies to multiple operations across concurrent queries and can create severe memory pressure.

Adding an index without measuring its cost#

Indexes consume storage and make inserts, updates, vacuuming, and replication more expensive.

Running EXPLAIN ANALYZE carelessly#

It executes the statement and can amplify the exact production incident being investigated.

Optimizing one execution but ignoring frequency#

Use total workload cost and user impact, not only the slowest isolated duration.

Production Checklist#

• Capture the exact query shape and representative parameters.
• Confirm whether the application uses prepared statements.
• Start with plain EXPLAIN when execution is unsafe.
• Run EXPLAIN ANALYZE only in a controlled context.
• Include BUFFERS when investigating data access.
• Read the plan from deepest children toward the root.
• Distinguish planner cost from milliseconds.
• Compare estimated and actual rows at every important node.
• Multiply per-loop rows carefully and avoid summing inclusive times.
• Inspect rows removed by filters and index rechecks.
• Check sort methods, hash batches, and temporary I/O.
• Review shared hits, reads, dirtied pages, and writes.
• Check workers planned versus launched in parallel plans.
• Validate statistics freshness and data skew.
• Use extended statistics only where relationships justify them.
• Test typical and worst-case parameter values.
• Change one query, index, statistic, or setting at a time.
• Compare before-and-after plans and application latency.
• Include index write cost and storage in the decision.
• Re-test under representative concurrency.

Conclusion#

PostgreSQL performance becomes understandable when you inspect the work the database actually performed.

For our Order query, the result contained only 50 rows, but the original plan scanned roughly two million orders, filtered most of them, joined 48,720, and then sorted the surviving rows. A suitable ordered access path let PostgreSQL stop after 50 matches and changed a nested loop from a potential risk into the right plan.

Read plans inside out. Distinguish estimates from actuals, planner cost from time, and cache hits from reads. Pay attention to loops, discarded rows, buffers, spills, and parameter distribution. Then change one thing and verify the entire workload instead of guessing from the SQL text.

References#

• EXPLAIN — PostgreSQL
• Using EXPLAIN — PostgreSQL
• Planner statistics — PostgreSQL