Using PostgreSQL EXPLAIN ANALYZE to Find Expensive Queries

A PostgreSQL query can return the correct results and still perform poorly as a database grows. A query that works well with a few thousand rows may become slow when a table contains millions of records. Adding more application servers will not necessarily solve the problem if the database is spending most of its time scanning rows, sorting large result sets, or executing an inefficient join.

PostgreSQL provides EXPLAIN and EXPLAIN ANALYZE to help developers understand how a query is executed. These commands reveal the execution plan, including scan methods, join strategies, estimated row counts, actual row counts, and execution time.

Understanding this information helps developers identify expensive operations and decide whether an index, query rewrite, or database configuration change is justified.

This article explains how to read PostgreSQL execution plans, identify common performance problems, compare estimated and actual results, and investigate queries safely in development and production environments.

What Is PostgreSQL EXPLAIN?

EXPLAIN asks PostgreSQL to display the execution plan it intends to use for a query. The plan describes how PostgreSQL expects to retrieve and process the requested data.

For example, consider an orders table:

CREATE TABLE orders
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(30) NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Suppose an application frequently retrieves recent orders for a particular customer.

SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

To inspect the planned execution strategy, run:

EXPLAIN
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

PostgreSQL may choose a sequential scan, an index scan, or another plan depending on the available indexes, table statistics, data distribution, and estimated query cost.

The estimated cost is a planner metric, not an execution time expressed in milliseconds. It is useful for comparing plan operations within PostgreSQL's cost model, but it should not be interpreted as a direct measurement of elapsed time.

EXPLAIN vs. EXPLAIN ANALYZE

The two commands serve different purposes.

Feature	EXPLAIN	EXPLAIN ANALYZE
Shows the planned execution strategy	Yes	Yes
Executes the query	No	Yes
Reports actual row counts	No	Yes
Reports actual execution timing	No	Yes
Useful for inspecting plans without running the query	Yes	No
Can trigger data changes for modifying statements	No	Yes

EXPLAIN is appropriate when you want to inspect the planner's intended strategy without executing the statement.

EXPLAIN ANALYZE executes the query and compares actual execution behavior with the estimates. This makes it much more useful when investigating a real performance problem.

However, remember that EXPLAIN ANALYZE adds measurement overhead, and the execution environment may differ from normal application traffic. A query's observed duration can vary with cache state, concurrent activity, and the amount of data processed.

Read an Execution Plan Step by Step

Run the following command to inspect the actual execution plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

The BUFFERS option reports buffer activity associated with plan operations. It helps distinguish work involving shared buffers from blocks that must be read from storage.

A simplified plan might look like this:

Limit
  -> Sort
       Sort Key: created_at DESC
       -> Seq Scan on orders
            Filter: (customer_id = 42)
            Rows Removed by Filter: 95000

This example illustrates a possible problem: PostgreSQL scans a large number of rows and then sorts the matching results before returning a small number of records.

The actual plan may differ. PostgreSQL can choose different operations based on the database's statistics and configuration.

1. Identify the scan method

Common scan methods include:

Seq Scan: Reads table pages sequentially and checks rows against the query conditions.
Index Scan: Uses an index to locate matching rows and then retrieves the required table data.
Index Only Scan: Retrieves required values from an index when the visibility information and index contents allow it.

A sequential scan is not automatically bad. It can be the most efficient strategy when a query needs a large portion of a table or when an index would require many additional page accesses.

The important question is whether the scan method makes sense for the query and the amount of data returned.

2. Compare estimated and actual rows

An execution plan may show values such as:

rows=100
actual rows=12000

The estimated row count is the planner's prediction. The actual row count represents what the operation produced during execution.

A large difference can indicate stale statistics, uneven data distribution, correlated columns, or a query pattern that is difficult for the planner to estimate.

When estimates are substantially wrong, investigate table statistics and the selectivity of the query conditions before making major indexing changes.

3. Inspect execution time and loops

EXPLAIN ANALYZE reports actual timing and loop information for plan nodes.

A node executed repeatedly may contribute significantly to total work even when one execution is inexpensive. For nodes executed multiple times, the reported per-loop timing and row counts must be interpreted alongside the loop count.

Do not simply add every displayed node duration together. Parent nodes include work performed by their children, so adding all timings can double-count execution time.

Focus on the overall execution time and the plan operations responsible for substantial work.

Use BUFFERS to Understand Data Access

Execution time alone does not always explain why a query is slow. PostgreSQL's buffer statistics provide additional information.

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'Pending';

The plan may report:

shared hit: A requested block was found in shared buffers.
shared read: A block had to be read into shared buffers.
temp read and temp written: Temporary blocks were read or written during execution.

A high number of buffer hits can still represent substantial database work. It means the blocks were found in shared buffers, not that processing them was free.

Temporary block activity can help identify operations that spill intermediate results to temporary storage, such as sorts or hashes that exceed their available memory.

Use these details to determine whether a query is scanning excessive data, repeatedly accessing the same structures, or performing expensive intermediate operations.

Improve a Query with a Suitable Index

Return to the customer order query:

SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

If the query is frequent and the table is large, a composite index may help PostgreSQL locate a customer's orders in the required order.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

The leading customer_id column supports the equality filter, while created_at DESC can help retrieve recent orders without an additional full sort.

After creating the index, inspect the plan again:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Check whether PostgreSQL chooses the new index and whether the amount of scanning or sorting has decreased.

An index is not guaranteed to improve every execution. Its value depends on the query workload, data distribution, table size, and the cost of retrieving the selected columns. Indexes also consume storage and add overhead to inserts, updates, and deletes.

Common Expensive Operations

Plan operation	Possible issue	What to investigate
Large sequential scan	Many rows are examined	Filter selectivity, table size, and index usefulness
Large sort	Many rows must be ordered	Index ordering, sort volume, and memory usage
Nested loop with many loops	Repeated inner operations	Join conditions, indexes, and row estimates
Hash join with temporary I/O	Hash processing may spill to disk	Row estimates and memory requirements
Large row-estimate mismatch	Planner estimates differ from reality	Statistics, data distribution, and correlated conditions

These operations are not inherently defective. A hash join may be the right choice for a large join, and a nested loop may be ideal when one side contains very few rows.

The goal is to identify unnecessary work rather than eliminate a particular plan node.

Troubleshooting Slow Queries

When a query is slow, use a consistent diagnostic sequence.

Reproduce the query with representative parameters and data.
Run EXPLAIN (ANALYZE, BUFFERS) in a suitable environment.
Identify expensive scans, sorts, joins, and repeated operations.
Compare estimated rows with actual rows.
Review indexes and table statistics.
Make one targeted change at a time.
Run the plan again and compare the results.

If the planner's row estimates appear inaccurate, consider refreshing statistics:

ANALYZE orders;

ANALYZE collects statistics used by the query planner. PostgreSQL normally maintains these statistics through autovacuum, but manually refreshing them can help when investigating recently changed data distributions.

If a query remains slow despite a reasonable execution plan, investigate locking, concurrent workloads, storage performance, connection pressure, and application-level latency as well.

Important Safety Considerations

EXPLAIN ANALYZE executes the statement being analyzed. For a modifying statement, this means data can be changed.

For example, this command performs the update while collecting execution statistics:

EXPLAIN ANALYZE
UPDATE orders
SET status = 'Archived'
WHERE created_at < NOW() - INTERVAL '2 years';

Do not run such statements casually against production data.

When you need to investigate a modifying statement, use an appropriate test environment or a carefully controlled transaction that you can roll back. A rollback does not undo every possible side effect, such as external actions performed by triggers or extensions.

For read-only queries, still consider the workload. An expensive analytical query can consume significant database resources even if it does not modify data.

Best Practices

Use representative parameters and data volumes when investigating a slow query.
Prefer EXPLAIN (ANALYZE, BUFFERS) when actual execution behavior is needed.
Do not treat every sequential scan as a performance defect.
Review row estimates before creating additional indexes.
Measure the effect of each change instead of relying on assumptions.
Account for index storage and write overhead.
Avoid running expensive diagnostic queries during periods of high production load without understanding their impact.
Record the PostgreSQL version, query parameters, and relevant environmental conditions when comparing plans.

Advantages and Disadvantages

Advantages	Disadvantages
Shows how PostgreSQL plans to execute a query.	Actual execution adds measurement overhead.
Helps identify unnecessary scanning and sorting.	The plan can vary with data and database conditions.
Reveals estimated-versus-actual row differences.	Interpreting complex plans requires practice.
Buffer statistics help explain data access.	A single plan does not represent every workload.
Supports evidence-based indexing decisions.	Indexes can increase storage and write costs.

Summary

PostgreSQL EXPLAIN ANALYZE is an important tool for diagnosing expensive database queries. It reveals actual execution behavior, while BUFFERS provides additional insight into how database pages are accessed.

Start by examining scan methods, row estimates, execution loops, sorts, and buffer activity. Use those findings to decide whether statistics, indexing, or query structure needs attention.

The most effective optimization process is measurement-driven: capture a representative plan, make one targeted change, and compare the results. This helps developers improve query performance without adding unnecessary indexes or making assumptions based only on how SQL looks.