PostgreSQL EXPLAIN ANALYZE: Read Query Plans and Tune Costs

When a PostgreSQL query is slow, guessing is expensive. You can add indexes that never get used, rewrite queries that weren’t the problem, or throw hardware at something a single configuration change would fix. EXPLAIN and EXPLAIN ANALYZE replace guessing with evidence: they show exactly how PostgreSQL plans to run a query, and how it actually ran.

This guide explains how to read that output, how the planner’s cost numbers are calculated, and how to use them to fix slow queries. All the plans below are real output from PostgreSQL 16, on a test database with 100,000 users and 1,000,000 orders, so you can reproduce them.

Set up a test database

If you want to follow along, this script creates the same tables and random data:

CREATE TABLE users (
  id serial PRIMARY KEY, name text, created_at date, active boolean
);
CREATE TABLE orders (
  id serial PRIMARY KEY, user_id int REFERENCES users(id),
  total numeric, created_at date
);

INSERT INTO users (name, created_at, active)
SELECT 'user' || g, date '2022-01-01' + (random()*1000)::int, random() < 0.3
FROM generate_series(1, 100000) g;

INSERT INTO orders (user_id, total, created_at)
SELECT 1 + (random()*99999)::int, round((random()*200)::numeric, 2),
       date '2022-01-01' + (random()*1000)::int
FROM generate_series(1, 1000000) g;

ANALYZE;

The final ANALYZE matters. It collects the statistics the planner uses to estimate row counts. Your numbers will differ slightly because the data is random.

EXPLAIN versus EXPLAIN ANALYZE

EXPLAIN shows the plan the planner chose and its estimates. It doesn’t run the query.
EXPLAIN ANALYZE runs the query and adds actual times and row counts. Adding BUFFERS also shows how many data pages were read from memory or disk.

Because EXPLAIN ANALYZE really executes the statement, be careful with INSERT, UPDATE or DELETE. Wrap them in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2022-02-01';
ROLLBACK;

Reading a plan

Let’s find the orders for one user, before adding any index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 4242;

Gather  (cost=1000.00..12579.43 rows=11 width=18) (actual time=8.143..38.563 rows=13 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=6370
  ->  Parallel Seq Scan on orders  (cost=0.00..11578.33 rows=5 width=18) (actual time=5.813..29.423 rows=4 loops=3)
        Filter: (user_id = 4242)
        Rows Removed by Filter: 333329
        Buffers: shared hit=6370
Planning Time: 0.272 ms
Execution Time: 38.622 ms

Read plans from the most indented line outwards, because inner nodes feed their parents. Here’s what each part means:

Parallel Seq Scan on orders: PostgreSQL read the whole table, split across worker processes.
cost=0.00..11578.33: the planner’s estimated cost to return the first row, then all rows. These are arbitrary units, not milliseconds.
rows=5 versus actual ... rows=4 loops=3: the estimate was 5 rows per process, and each of the 3 processes (two workers plus the leader) actually found about 4. Multiply actual rows by loops to get the total, here 13.
Rows Removed by Filter: 333329: per process, it read about a third of a million rows to keep a handful. That’s the clearest sign of a missing index.
Buffers: shared hit=6370: 6,370 pages were read, all from PostgreSQL’s cache.

Adding the right index

CREATE INDEX idx_orders_user_id ON orders (user_id);

Running the same EXPLAIN (ANALYZE, BUFFERS) again:

Bitmap Heap Scan on orders  (cost=4.51..47.28 rows=11 width=18) (actual time=0.034..0.050 rows=13 loops=1)
  Recheck Cond: (user_id = 4242)
  Heap Blocks: exact=13
  Buffers: shared hit=13 read=3
  ->  Bitmap Index Scan on idx_orders_user_id  (cost=0.00..4.51 rows=11 width=0) (actual time=0.028..0.029 rows=13 loops=1)
        Index Cond: (user_id = 4242)
        Buffers: shared read=3
Planning Time: 0.363 ms
Execution Time: 0.090 ms

Execution time fell from about 38.6 ms to 0.09 ms, and pages touched from 6,370 to 16. The planner chose a bitmap scan: it uses the index to find which pages hold matching rows, then reads just those pages. Note that PostgreSQL doesn’t automatically index foreign key columns like orders.user_id. Indexing them is one of the most common quick wins.

How the cost numbers are calculated

The planner compares plans using cost estimates built from a few configuration parameters. For a simple sequential scan with a filter, the PostgreSQL documentation gives the calculation as:

cost = (pages × seq_page_cost)
     + (rows × cpu_tuple_cost)
     + (rows × cpu_operator_cost)   -- once per filter operator

You can check it yourself. Our users table has this many pages and rows:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'users';
-- relpages | reltuples
--      637 |    100000

And the plan for a filtered scan:

EXPLAIN SELECT * FROM users WHERE created_at > '2024-06-01';
-- Seq Scan on users  (cost=0.00..1887.00 rows=12217 width=18)
--   Filter: (created_at > '2024-06-01'::date)

With the defaults seq_page_cost = 1.0, cpu_tuple_cost = 0.01 and cpu_operator_cost = 0.0025:

(637 × 1.0) + (100000 × 0.01) + (100000 × 0.0025)
= 637 + 1000 + 250
= 1887

That matches the plan exactly. The rows=12217 estimate comes from the column statistics that ANALYZE collected.

Tuning cost parameters

The defaults were chosen with spinning disks in mind. The most important one to review is random_page_cost, which defaults to 4.0: the planner assumes a random page read costs four times a sequential one. On SSDs or when most data is cached, that’s too pessimistic, and it can make the planner avoid indexes it should use. Many people lower it to around 1.1 to 2.0 on SSD storage.

Test changes in a session before changing them globally:

SET random_page_cost = 1.1;
EXPLAIN ANALYZE SELECT ...;   -- compare against the previous plan
RESET random_page_cost;

If it helps across your workload, set it in postgresql.conf or with ALTER SYSTEM SET random_page_cost = 1.1; followed by SELECT pg_reload_conf();. Also worth reviewing:

effective_cache_size: tells the planner how much memory is available for caching (shared buffers plus the operating system cache). Setting it realistically makes index scans look appropriately cheap.
work_mem: memory per sort or hash operation. If EXPLAIN ANALYZE shows Sort Method: external merge Disk: ..., the sort spilled to disk and more work_mem might help. Raise it carefully, because every sort or hash in every connection can use this much.

Warning signs to look for in plans

Estimated rows far from actual rows, by 10 times or more. The planner is working with bad information. Run ANALYZE, and for skewed columns consider raising the statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.
Large “Rows Removed by Filter” on a big table. You’re probably missing an index on that filter.
Nested Loop with a large outer row count and a sequential scan inside. The inner side runs once per outer row, so check the loops value.
Sorts spilling to disk. Check work_mem, or add an index that provides rows already in order.

Other useful techniques

Composite indexes for queries that filter on several columns, with the most selective equality columns first, e.g. CREATE INDEX ON orders (user_id, created_at);.
Partial indexes when you usually query a subset: CREATE INDEX ON users (created_at) WHERE active;.
Keyset pagination instead of large OFFSET values, which force PostgreSQL to read and discard every skipped row: WHERE id > $last_seen_id ORDER BY id LIMIT 50.
GIN indexes for JSONB containment queries using @>: CREATE INDEX ON events USING GIN (data);.

Find the queries worth fixing first

Don’t optimise at random. The pg_stat_statements extension records how often each query runs and how long it takes in total:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- also requires shared_preload_libraries = 'pg_stat_statements' and a restart

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

A query that takes 5 ms but runs a million times a day often matters more than a 2-second report that runs once. Fix the top of this list, run EXPLAIN ANALYZE before and after each change, and keep the change only if the numbers prove it helped.