---
title: "Postgres Performance Basics: Reading EXPLAIN Output and Fixing Slow Queries"
author: "Anshu Pathak"
published: "2026-10-28T08:59:58.209Z"
updated: "2026-10-28T08:59:58.209Z"
url: "https://zyvop.com/postgres-performance-basics-reading-explain-output-and-fixing-slow-queries-8gs9b"
canonical: "https://zyvop.com/postgres-performance-basics-reading-explain-output-and-fixing-slow-queries-8gs9b"
tags: ["indexing","postgresql","SQL","databases","performance"]
---

# Postgres Performance Basics: Reading EXPLAIN Output and Fixing Slow Queries

Learn to read EXPLAIN ANALYZE output, spot what actually slows a query down, and fix it with better indexes, statistics, and cleaner SQL.

![Cover image](<https://pub-03c883138afa4f88bc40a259a9109c50.r2.dev/posts/covers/postgres-performance-basics-reading-explain-output-and-fixin-3ug4qyje.webp>)

A slow query is a guess until you see its plan. Postgres will tell you exactly how it intends to run a query and, with one extra keyword, how the run actually went. Learning to read that output is one of the highest-leverage Postgres skills you can pick up, because it turns "this feels slow" into "this step scanned five million rows to return twenty."

This post covers how to run `EXPLAIN` safely, how to read each part of its output, and a repeatable workflow for fixing the most common causes of slow queries.

## EXPLAIN vs EXPLAIN ANALYZE

-   `EXPLAIN` shows the plan the planner chose, with its _estimates_. It does not run the query.
    
-   `EXPLAIN ANALYZE` **runs the query** and adds _actual_ timings and row counts next to the estimates.
    

The command you'll use most:

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

`BUFFERS` reports how many pages (8 KB by default) each step touched. In Postgres 18 and later, `EXPLAIN ANALYZE` includes it by default. On older versions, add it yourself.

Because `ANALYZE` really executes the statement, wrap anything that writes data in a transaction and roll it back:

```sql
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'archived'
WHERE created_at < now() - interval '1 year';
ROLLBACK;
```

The rollback undoes the data changes, but the statement still runs for real: it takes locks, fires triggers, and can have effects outside the transaction, such as advancing a sequence.

## Anatomy of a plan

Here is the plan for the query above on a table of 5 million orders with no useful index. It is simplified (parallel workers are left out) and uses Postgres 18's format, which prints row counts with two decimals. Older versions print whole numbers.

```text
Limit  (cost=116011.80..116011.85 rows=20 width=64) (actual time=411.206..411.208 rows=20.00 loops=1)
  Buffers: shared hit=3200 read=50309
  ->  Sort  (cost=116011.80..116014.56 rows=1105 width=64) (actual time=411.205..411.206 rows=20.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 30kB
        Buffers: shared hit=3200 read=50309
        ->  Seq Scan on orders  (cost=0.00..116009.00 rows=1105 width=64) (actual time=0.021..410.812 rows=1180.00 loops=1)
              Filter: (customer_id = 42)
              Rows Removed by Filter: 4998820
              Buffers: shared hit=3200 read=50309
Planning Time: 0.214 ms
Execution Time: 411.434 ms
```

A plan is a tree. Read it from the most indented node outward: each child feeds rows to its parent.

```mermaid
flowchart BT
    S["Seq Scan on orders<br/>filter: customer_id = 42"] --> SO["Sort<br/>created_at DESC"]
    SO --> L["Limit 20"]
    L --> R["Rows returned to the client"]
```

Every node line carries the same handful of numbers:

-   `cost=0.00..116009.00` is the planner's estimate, in arbitrary units. The first number is the startup cost (before the first row), the second is the total. Costs are useful for comparing plans, not for predicting milliseconds.
    
-   `rows=1105 width=64` is the estimated row count and average row size in bytes.
    
-   `actual time=0.021..410.812` is the time in milliseconds to the first row and to the last row, _per loop_.
    
-   `rows=1180.00 loops=1` is the actual row count, also per loop, and how many times the node ran. The decimals are there because a per-loop average can be fractional. For a node with `loops` greater than 1, multiply by `loops` to get totals.
    
-   `Buffers: shared hit=3200 read=50309` counts pages found in Postgres's shared buffer cache (`hit`) versus fetched from outside it (`read`). A `read` may still be served by the operating system's cache.
    

One more rule: a node's time and buffer counts **include its children**, which is why the `Sort` and `Limit` nodes repeat the scan's `Buffers` line. To find where the time really goes, subtract the child's time from the parent's.

In this plan the story is clear. The sequential scan touched about 53,500 pages and threw away 4,998,820 rows to keep 1,180, and that scan accounts for almost the entire runtime.

## What to look for

| Signal in the plan | What it usually means | Typical fix |
| --- | --- | --- |
| Seq Scan with a large `Rows Removed by Filter` | Reading far more rows than the query needs | Add an index that matches the filter |
| Estimated rows far from actual rows | Stale or insufficient statistics | `ANALYZE`, extended statistics, a higher statistics target |
| `Sort Method: external merge Disk: ...` | The sort spilled to disk | Raise `work_mem` for this query, or sort via an index |
| A Hash node with `Batches` greater than 1 | The hash join spilled to disk | Same: `work_mem`, or filter earlier |
| Nested Loop with a large `loops` count on the inner side | The inner side runs once per outer row | Index the join key, and check the row estimates |
| High `shared read` | Lots of data fetched from outside shared buffers (or a cold cache) | Read fewer rows or columns, or add a covering index |

A sequential scan is not automatically bad. If the query needs most of the table, scanning it sequentially is the fastest option.

## Fixing the most common problems

### 1\. Add the right index

For the query above, an index on the filter column plus the sort column does the job:

```sql
CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at DESC);
```

`CONCURRENTLY` builds the index without blocking writes. It scans the table twice, so it is slower, and it can't run inside a transaction block. If the build fails, it leaves an `INVALID` index behind: drop it and try again. Put equality-filtered columns first and the sort or range column last, so Postgres can jump to one customer's rows and read them already in order.

```text
Limit  (cost=0.43..82.23 rows=20 width=64) (actual time=0.031..0.092 rows=20.00 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_created_idx on orders  (cost=0.43..4520.12 rows=1105 width=64) (actual time=0.030..0.088 rows=20.00 loops=1)
        Index Cond: (customer_id = 42)
        Index Searches: 1
        Buffers: shared hit=24
Planning Time: 0.187 ms
Execution Time: 0.118 ms
```

The runtime dropped from about 411 ms to 0.1 ms. The Sort node is gone because the index already returns rows in order, and `Limit` stops after 20 rows. The new `Index Searches` line, added in Postgres 18, counts how many times the index was descended.

The `DESC` in the index isn't required for this query. A plain `(customer_id, created_at)` index works too, because Postgres can scan an index backward, and the plan would read `Index Scan Backward`. Spell out directions when you mix them, such as `ORDER BY a ASC, b DESC`.

Two useful variations are a **partial index** (`... WHERE status = 'open'`) for queries that only touch a slice of the table, and a **covering index** (`INCLUDE (...)`) that can enable index-only scans, which also depend on the table being vacuumed regularly. Indexes cost disk space and slow down writes, so add them for queries that matter, not by reflex.

### 2\. Make predicates index-friendly

Wrapping an indexed column in a function usually prevents a plain index from being used:

```sql
-- Can't use a plain index on created_at
WHERE date(created_at) = '2026-10-01'

-- Can
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'
```

If you genuinely need to search on an expression, index the expression itself, for example `CREATE INDEX ON users (lower(email));`. A leading wildcard such as `LIKE '%term'` can't use a B-tree index either. Look at the `pg_trgm` extension for that case.

### 3\. Fix bad estimates

When estimated and actual rows disagree wildly, the planner is choosing plans from bad information. Start with fresh statistics. Autovacuum normally re-analyzes tables for you, but after a bulk load or a large data change, run `ANALYZE` yourself:

```sql
ANALYZE orders;
```

If the columns are correlated (city and zip code, for example), the planner assumes they are independent and underestimates. Extended statistics fix that:

```sql
CREATE STATISTICS customers_city_zip (dependencies)
    ON city, zip FROM customers;
ANALYZE customers;
```

For a single skewed column, raise its statistics target with `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500;` and re-run `ANALYZE`.

### 4\. Stop sorts and hashes from spilling to disk

If you see `external merge Disk` or multiple hash batches, give that query more memory without changing the server default:

```sql
BEGIN;
SET LOCAL work_mem = '64MB';
-- run the query
COMMIT;
```

Be careful with a global increase. `work_mem` applies per sort or hash operation, per query, so a large value multiplied across many connections can exhaust memory.

### 5\. Replace deep OFFSET pagination

`OFFSET` makes Postgres read and discard every skipped row. Keyset pagination remembers where the last page ended instead:

```sql
-- Slow for deep pages: reads and discards 100,000 rows
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- Keyset: continues from the last row of the previous page
SELECT * FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```

Back it with an index on `(created_at, id)`. The trade-off is that you can move to the next or previous page, but you can't jump straight to page 500.

### 6\. Fetch less

`SELECT *` on a wide table drags every column through the plan. Select only what you need. The `Buffers` line shows the difference directly.

## A repeatable workflow

First find the queries worth your time. The `pg_stat_statements` extension tracks every statement's cost. It must be added to `shared_preload_libraries` (which needs a server restart) and then enabled per database with `CREATE EXTENSION pg_stat_statements;`. The query below uses the Postgres 13+ column names. Older versions call them `total_time` and `mean_time`.

```sql
SELECT query,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
```

To capture the plans of slow queries as they happen in production, use the `auto_explain` module. Preload it with `session_preload_libraries` or `shared_preload_libraries`, then set `auto_explain.log_min_duration`, because it does nothing until you do. Turning on `auto_explain.log_analyze` gives actual timings but adds overhead, so use a high threshold or `auto_explain.sample_rate`. The separate `log_min_duration_statement` setting logs slow statements without their plans. Then triage each query the same way:

```mermaid
flowchart TD
    A["Slow query"] --> B["Run EXPLAIN (ANALYZE, BUFFERS)"]
    B --> C{"Estimated rows close to actual?"}
    C -->|No| D["Run ANALYZE, add extended statistics, raise the statistics target"]
    D --> B
    C -->|Yes| E{"Where is the time spent?"}
    E -->|"Seq Scan, many rows removed"| F["Add or fix an index"]
    E -->|"Sort or Hash spills to disk"| G["Raise work_mem for this query, or reduce rows earlier"]
    E -->|"Nested Loop with many loops"| H["Index the join key or rewrite the join"]
    E -->|"Too many rows or columns fetched"| I["Select less, filter earlier, paginate"]
```

## Pitfalls to avoid

-   **Test with realistic data.** On a table with a thousand rows, the planner correctly prefers a sequential scan. A plan from a tiny dev database tells you little about production.
    
-   **Run the query twice.** The first run may hit a cold cache. Compare `shared read` against `shared hit` across runs.
    
-   **Don't force plans in production.** Setting `enable_seqscan = off` is a handy diagnostic to see whether an index plan is possible at all, but it is not a fix.
    
-   **Mind the overhead.** `ANALYZE` measures time per node, which adds overhead on very fast, many-node plans. `EXPLAIN (ANALYZE, TIMING OFF)` reduces it.
    
-   **Use a visualizer for big plans.** Tools like explain.dalibo.com and explain.depesz.com turn a wall of text into a readable tree and highlight the slow nodes. Plans can reveal table names, column names, and query text, so don't paste sensitive ones into public tools.
    

## Wrapping up

Most slow Postgres queries come down to a small set of causes: a missing or unusable index, bad row estimates, a spill to disk, or simply fetching more than the query needs. `EXPLAIN (ANALYZE, BUFFERS)` shows you which one you have. Read the plan from the inside out, compare estimates against actuals, find the node where the time goes, and fix that first.