How to Use the EXPLAIN Command in PostgreSQL

If you only ever learn one diagnostic command in PostgreSQL, make it EXPLAIN. It’s the window into how the query planner actually intends to execute your SQL, and understanding its output is the single most valuable skill for figuring out why a query is slow and what to do about it.

This article walks through what EXPLAIN does, its syntax and options, how to actually read its output, practical examples across different query types, and common troubleshooting scenarios.

What Is the EXPLAIN Command?

EXPLAIN shows the execution plan that PostgreSQL’s query planner has chosen for a given SQL statement, without actually running it (by default). It reveals things like: whether a sequential scan or an index scan will be used, what join algorithm will be applied, in what order tables will be joined, and how many rows the planner estimates each step will produce.

This matters because SQL is declarative — you describe what you want, not how to get it — and the planner decides the “how” based on statistics, available indexes, and configured cost parameters. EXPLAIN lets you see that decision-making process instead of treating it as an unknowable black box.

Basic Syntax

EXPLAIN [ ( option [, ...] ) ] statement;

The simplest usage:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

This returns something like:

Seq Scan on orders  (cost=0.00..2334.00 rows=12 width=72)
  Filter: (customer_id = 42)

Key Options

ANALYZE

This is the option you’ll use constantly. Adding ANALYZE actually executes the query (be careful with this on write operations — see the note below) and reports the real, measured execution time and row counts alongside the planner’s original estimates.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Output now includes both estimated and actual figures:

Seq Scan on orders  (cost=0.00..2334.00 rows=12 width=72) (actual time=0.015..8.223 rows=14 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 49986
Planning Time: 0.112 ms
Execution Time: 8.245 ms

Important caveat: EXPLAIN ANALYZE on an INSERT, UPDATE, or DELETE statement will actually perform that write. If you want to see the plan for a modifying statement without committing the change, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'archived' WHERE order_date < '2020-01-01';
ROLLBACK;

BUFFERS

Shows information about actual disk block usage — how many blocks were found in shared memory cache (“hit”) versus read from disk (“read”). This is enormously useful for understanding I/O-related performance issues, not just CPU/algorithmic ones.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

FORMAT

Controls output format — TEXT (default), JSON, XML, or YAML. The structured formats are especially useful when feeding plan output into visualization tools or automated analysis scripts.

EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE customer_id = 42;

COSTS

Controls whether estimated startup and total cost, as well as estimated row counts and width, are shown. Defaults to on; you might turn it off (COSTS OFF) when you want a cleaner plan shape for comparison purposes without the numbers changing between runs.

EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE customer_id = 42;

VERBOSE

Adds extra detail, including fully-qualified column names and the output columns of each plan node.

EXPLAIN (VERBOSE) SELECT * FROM orders WHERE customer_id = 42;

SETTINGS

Shows which non-default configuration settings were in effect when the plan was generated — useful when comparing plans across environments that might have different tuning applied.

EXPLAIN (ANALYZE, SETTINGS) SELECT * FROM orders WHERE customer_id = 42;

WAL

When combined with ANALYZE, reports write-ahead log activity generated by the statement — relevant mainly for write-heavy statements.

EXPLAIN (ANALYZE, WAL) UPDATE orders SET status = 'archived' WHERE id = 1;

How to Read EXPLAIN Output

Plans are structured as a tree of nodes, and they’re read from the innermost (most indented) nodes outward, since inner nodes execute first and feed their results up to outer nodes. Each node shows:

The operation type — Seq Scan, Index Scan, Bitmap Heap Scan, Nested Loop, Hash Join, Sort, etc.
cost=startup..total — the planner’s estimated cost in arbitrary units, where startup cost is the cost before the first row can be returned, and total cost is the cost to return all rows.
rows — the estimated number of rows this node will produce.
width — the estimated average width in bytes of each row.
With ANALYZE: actual time=startup..total, actual rows, and loops — the real measured values.

A Worked Example

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

Hash Join  (cost=15.50..245.30 rows=120 width=40) (actual time=0.452..3.221 rows=118 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..220.00 rows=120 width=12) (actual time=0.020..2.100 rows=118 loops=1)
        Filter: (order_date > '2026-01-01'::date)
        Rows Removed by Filter: 4882
  ->  Hash  (cost=10.00..10.00 rows=500 width=36) (actual time=0.400..0.401 rows=500 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..10.00 rows=500 width=36) (actual time=0.008..0.180 rows=500 loops=1)
Planning Time: 0.180 ms
Execution Time: 3.290 ms

Reading this from the inside out: PostgreSQL scans customers fully, builds a hash table from it, then scans orders (filtering on order_date), and probes the hash table to perform the join. The estimated and actual row counts line up closely here (120 estimated vs. 118 actual), which is a good sign that statistics are accurate for this query.

Practical Examples

Example 1: Diagnosing a Slow Query Missing an Index

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

If this shows a Seq Scan with a large “Rows Removed by Filter” value and a high actual execution time, it’s a strong signal that an index on customer_id would help:

CREATE INDEX orders_customer_id_idx ON orders(customer_id);

Re-running EXPLAIN ANALYZE afterward should show an Index Scan (or Bitmap Heap Scan for less selective conditions) with a much lower execution time.

Example 2: Spotting a Bad Row Estimate

EXPLAIN ANALYZE SELECT * FROM events WHERE event_type = 'rare_event';

If the planner estimates 5,000 rows but the actual count is 12, that mismatch is worth investigating — likely resolved with a fresh ANALYZE or a higher statistics target on that column, as covered in the article on the ANALYZE command.

Example 3: Checking Buffer Usage for I/O-Bound Queries

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

A high number of “read” blocks (versus “hit” blocks from cache) suggests the query is pulling a lot of data from disk rather than memory — worth knowing before assuming a purely CPU/algorithmic optimization would help.

Example 4: Comparing Two Query Formulations

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'EU');

EXPLAIN (ANALYZE, COSTS OFF)
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.region = 'EU';

Running both versions through EXPLAIN ANALYZE lets you directly compare plan shapes and actual execution times, rather than guessing which formulation the planner handles better.

Example 5: Using JSON Output for Programmatic Analysis

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE customer_id = 42;

This is the format most third-party plan visualization tools (like online plan analyzers) expect as input, since it’s much easier to parse programmatically than the text format.

Common Use Cases

Diagnosing why a specific query is slow, as the first and most important debugging step.
Validating that a new index is actually being used by the planner, rather than assuming it based on its existence.
Comparing alternative ways of writing the same query to see which one the planner handles more efficiently.
Understanding join order and algorithm decisions in complex multi-table queries.
Capacity planning, by examining how execution time and buffer usage scale as data volume grows.
Validating the impact of configuration changes, like adjusting work_mem or planner cost parameters, by comparing plans before and after.

Troubleshooting Common Issues

The Planner Isn’t Using an Index I Expect It To Use

A few common causes: statistics are stale (try ANALYZE), the table is small enough that a sequential scan is genuinely cheaper (the planner isn’t wrong here — for tiny tables, a seq scan often is faster), or the query has a function or type mismatch on the indexed column that prevents index usage (e.g., comparing a TEXT column to an INTEGER without an explicit cast, or wrapping the column in a function without a matching functional index).

Test the theory directly:

SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
SET enable_seqscan = ON;

If forcing sequential scans off dramatically changes the plan and it becomes faster, that confirms an index usage problem worth digging into further.

Estimated and Actual Rows Are Wildly Different

This almost always points back to statistics — either stale (needs ANALYZE) or insufficiently detailed for a skewed column (needs a higher statistics target). See the ANALYZE command guide for more on this.

EXPLAIN ANALYZE Is Slower Than Just Running the Query

This is expected to some degree, since ANALYZE adds instrumentation overhead to measure actual timings. For very fast queries this overhead can be proportionally significant. If you need to minimize this, the BUFFERS option without ANALYZE (or careful use of EXPLAIN alone) can sometimes give enough insight without the full instrumentation cost, though for real timing data you do need ANALYZE.

Accidentally Running a Write With EXPLAIN ANALYZE

As mentioned earlier, EXPLAIN ANALYZE on INSERT/UPDATE/DELETE actually executes the statement. If you didn’t mean to commit that change, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'test';
ROLLBACK;

Plan Looks Fine But Query Is Still Slow in the Application

Check whether the slowness is actually happening at the database level at all — network latency, application-side processing of a large result set, or connection pool exhaustion can all masquerade as “slow queries” when the database itself is executing quickly. Compare EXPLAIN ANALYZE‘s reported execution time against what the application actually measures end-to-end.

Best Practices

Always use ANALYZE (and usually BUFFERS) when diagnosing real performance problems — plain EXPLAIN without ANALYZE only shows estimates, which can be misleading if statistics are off.
Compare estimated vs. actual rows on every node, not just the final result — a mismatch deep in the plan tree can still be the root cause of a bad overall plan.
Wrap EXPLAIN ANALYZE on write statements in a transaction with ROLLBACK unless you actually intend to commit the change.
Use FORMAT JSON when working with visualization tools, since text output, while human-readable, is harder to parse programmatically or feed into plan-diagramming tools.
Don’t just look at total cost — look at where time is actually spent. A plan with low total estimated cost can still be slow in practice if the actual data or I/O patterns don’t match the planner’s assumptions.
Re-run EXPLAIN ANALYZE after making a change — adding an index, running ANALYZE, or adjusting a configuration setting — to confirm the change actually had the intended effect, rather than assuming it worked.
Build a habit of reading plans from the inside out. It’s easy to get overwhelmed by a large, deeply nested plan; anchor yourself by finding the innermost nodes first and working outward.

Common Plan Node Types and What They Mean

Getting comfortable reading EXPLAIN output means recognizing the common node types and what they imply about performance:

Seq Scan — reads every row in the table sequentially, applying any filter conditions as it goes. Fine for small tables or when a large fraction of the table matches the filter; often a performance concern on large tables with selective filters lacking a matching index.

Index Scan — uses an index to find matching rows directly, then fetches each matching row from the table’s heap. Efficient for highly selective conditions.

Index Only Scan — like an index scan, but able to satisfy the query entirely from the index itself without visiting the table heap at all, because every column the query needs is present in the index. This is usually the fastest scan type when applicable, though it depends on the visibility map being sufficiently up to date (which routine vacuuming helps maintain).

Bitmap Heap Scan / Bitmap Index Scan — a two-step approach where PostgreSQL first builds a bitmap of matching row locations from the index, then fetches those rows from the heap in physical order, reducing random I/O compared to a plain index scan. Common for moderately selective conditions, or when combining multiple index conditions with AND/OR.

Nested Loop — for each row in the outer input, scans the inner input looking for matches. Efficient when the outer side is small, but can become slow if both sides are large and there’s no good index to speed up the inner lookup.

Hash Join — builds an in-memory hash table from one side of the join (usually the smaller one) and probes it while scanning the other side. Generally efficient for larger joins, provided the hash table fits comfortably within work_mem.

Merge Join — requires both inputs to be sorted on the join key, then merges them together in a single pass. Efficient when the inputs are already sorted (for example, via an index), avoiding an explicit sort step.

Sort — explicitly sorts rows, often appearing before a Merge Join or to satisfy an ORDER BY clause. Watch for “Sort Method: external merge” in EXPLAIN ANALYZE output specifically — that indicates the sort spilled to disk because it didn’t fit in work_mem, which is usually worth addressing.

Aggregate / HashAggregate / GroupAggregate — implement GROUP BY and aggregate functions, either via a hash table (HashAggregate, generally faster when it fits in memory) or by processing pre-sorted input (GroupAggregate).

Spotting Disk Spills in EXPLAIN ANALYZE Output

One of the most actionable things to look for in EXPLAIN (ANALYZE, BUFFERS) output is evidence that an operation spilled to disk because it didn’t fit in the memory budget allotted by work_mem. This typically shows up as text like:

Sort Method: external merge  Disk: 24576kB

or, for hash-based operations:

Batches: 5  Memory Usage: 4096kB

A Sort Method of external merge (versus quicksort, which stays entirely in memory) or a hash join reporting multiple Batches (versus a single batch) both indicate the operation needed more memory than it had available and had to spill to disk, which is considerably slower than an equivalent in-memory operation. This is one of the most direct, actionable signals that increasing work_mem — either globally or just for that session via SET — might meaningfully speed up the specific query you’re analyzing.

Using auto_explain for Production Query Logging

Manually running EXPLAIN ANALYZE is great for investigating a query you already know is slow, but sometimes you need visibility into slow queries you don’t even know are happening in production. The auto_explain module (bundled with PostgreSQL, but not enabled by default) automatically logs the execution plan for any query exceeding a configured duration threshold:

# in postgresql.conf, after adding to shared_preload_libraries
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = true
auto_explain.log_buffers = true

After a restart (since shared_preload_libraries requires one), any query taking longer than one second gets its full EXPLAIN ANALYZE-equivalent plan written to the PostgreSQL log automatically, without you needing to have predicted which query would be slow ahead of time. This is genuinely one of the most useful tools for catching real-world slow queries that only show up under production load or data volume, which can be very different from what you’d see testing against a smaller development dataset.

Wrapping Up

EXPLAIN is the most direct way to understand what PostgreSQL is actually doing when it runs your query, rather than guessing based on symptoms. Learning to read its output — recognizing scan types, understanding the difference between estimated and actual figures, and knowing which options (ANALYZE, BUFFERS, VERBOSE) to reach for in different situations — is one of the highest-leverage skills you can build as a PostgreSQL user, whether you’re a backend developer occasionally chasing a slow endpoint or a full-time database administrator.

Make it a habit to reach for EXPLAIN ANALYZE before assuming you know why a query is slow. More often than not, the plan tells you exactly what’s happening — you just need to know how to read it.