cost and rows estimates in PostgreSQL EXPLAIN

What it does

Every step in a plan, with or without ANALYZE, has the planner’s estimates in the first brackets:

Seq Scan on orders  (cost=0.00..23094.00 rows=9867 width=34)

• cost=A..B: A is the start-up cost, the work before the step can return its first row; B is the total cost to return all its rows. Both include the steps below it.
• rows: how many rows the planner expects the step to return (per loop, if it runs more than once).
• width: the average size of those rows, in bytes.

The planner works out a cost for every plan it considers and runs the cheapest. Costs are not milliseconds. They’re in arbitrary units, set by these settings (the defaults):

So by convention one cost unit is one sequential page read. Many people lower random_page_cost to about 1.1 on SSDs, which makes index scans look cheaper.

When you see it

On every step of every EXPLAIN. Plain EXPLAIN shows only these estimates and doesn’t run the query. EXPLAIN ANALYZE runs it and adds the actual numbers beside them, which is how you check the estimates. (EXPLAIN ANALYZE on an UPDATE or DELETE really changes rows: wrap it in BEGIN … ROLLBACK.) EXPLAIN (COSTS OFF) hides them.

Reading its numbers

Where cost comes from. A sequential scan costs one seq_page_cost per page plus one cpu_tuple_cost per row, plus one cpu_operator_cost per row for each condition it checks. For our orders table:

SELECT relpages, reltuples FROM pg_class WHERE oid = 'orders'::regclass;
--  relpages | reltuples
-- ----------+-----------
--     10594 |     1e+06

• 10594 × 1.0 + 1,000,000 × 0.01 = 20594, and EXPLAIN SELECT * FROM orders printed cost=0.00..20594.00.
• Add WHERE status = 'cancelled': one more 1,000,000 × 0.0025 = 23094, exactly what the plan above shows.

Start-up cost matters for LIMIT and EXISTS. A scan can return its first row straight away (start-up 0.00). A sort has to read all its input first, so its start-up cost is close to its total:

Sort  (cost=57341.36..58137.19 rows=318334 width=49)

Rows come from statistics. ANALYZE (and autovacuum) samples each table and stores per-column statistics: the share of NULLs, the most common values and their frequencies, a histogram of the rest, and the number of distinct values. The planner multiplies the table’s row count by the estimated fraction that matches each condition. With several conditions, it assumes they’re independent and multiplies their fractions.

Compare estimated and actual rows per loop. In EXPLAIN ANALYZE output, put rows from the first bracket beside rows from the second. A factor of 2 doesn’t matter much; a factor of 10 or more, low in the plan, usually does, because every step above it inherits the error.

When it’s a problem

When a wrong estimate leads to a wrong plan: a nested loop chosen for “a few rows” that runs 50,000 times, a hash table sized for 200 rows that gets 50,000, an index scan where a sequential scan would read less. The common causes:

1. Stale statistics. Rows were added since the last ANALYZE, especially new values beyond the histogram’s range, such as today’s timestamps. Run ANALYZE <table>, or make autovacuum analyse the table more often (ALTER TABLE <table> SET (autovacuum_analyze_scale_factor = 0.02)).
2. Correlated columns. country = 'JP' AND city = 'Tokyo 4': the planner multiplies the two fractions as if they were unrelated and underestimates. CREATE STATISTICS tells it about the dependency.
3. Too few statistics for a skewed column. Raise the sample for that column: ALTER TABLE <table> ALTER COLUMN <column> SET STATISTICS 1000; ANALYZE <table>;
4. Conditions it can’t estimate. Expressions such as lower(email) = … or created_at::date = … get a default guess. An expression index gives the planner statistics for the expression after the next ANALYZE.

Don’t tune the cost settings to force a plan for one query. Fix the estimate, and the costs follow.

Example

PostgreSQL 18.6, the customers and orders tables from loops and actual time. Every customer whose city is Tokyo 4 has country JP, but the planner doesn’t know that:

SET search_path = seo_terms;
SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, o.id, o.total
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'JP' AND c.city = 'Tokyo 4';

Hash Join  (cost=453.50..23673.05 rows=9876 width=28) (actual time=17.053..131.758 rows=50000.00 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=5390 read=5355
  ->  Seq Scan on orders o  (cost=0.00..20594.00 rows=1000000 width=18) (actual time=15.954..65.814 rows=1000000.00 loops=1)
        Buffers: shared hit=5239 read=5355
  ->  Hash  (cost=451.00..451.00 rows=200 width=18) (actual time=1.088..1.089 rows=1000.00 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 59kB
        Buffers: shared hit=151
        ->  Seq Scan on customers c  (cost=0.00..451.00 rows=200 width=18) (actual time=0.013..0.996 rows=1000.00 loops=1)
              Filter: ((country = 'JP'::text) AND (city = 'Tokyo 4'::text))
              Rows Removed by Filter: 19000
              Buffers: shared hit=151
Planning:
  Buffers: shared hit=120
Planning Time: 0.247 ms
Execution Time: 133.258 ms

The customers scan expected 200 rows (20,000 × 1/5 for the country × 1/20 for the city) and got 1,000. The join inherited the error: 9,876 expected, 50,000 actual. Here the plan survived, but the same 5× error decides between plans in bigger queries. Tell the planner the columns are related:

CREATE STATISTICS customers_country_city (dependencies) ON country, city FROM customers;
ANALYZE customers;

Hash Join  (cost=463.50..23683.05 rows=49378 width=28) (actual time=8.274..121.769 rows=50000.00 loops=1)
  …
        ->  Seq Scan on customers c  (cost=0.00..451.00 rows=1000 width=18) (actual time=0.012..1.046 rows=1000.00 loops=1)
              Filter: ((country = 'JP'::text) AND (city = 'Tokyo 4'::text))

Both estimates are now right. The cost of the customers scan didn’t change (451.00, the same pages and rows read); only the expected output did.

Stale statistics look like this. On the readings table from Heap Fetches (autovacuum off), we added 50,000 rows dated 2027 and queried them before analysing:

INSERT INTO readings
SELECT 1000000 + i, i % 500, timestamptz '2027-01-01' + i * interval '1 second', 0
FROM generate_series(1, 50000) AS i;

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM readings WHERE taken_at >= '2027-01-01';

Index Scan using readings_taken_at_idx on readings  (cost=0.42..8.54 rows=7 width=28) (actual time=0.007..5.420 rows=50000.00 loops=1)
  Index Cond: (taken_at >= '2027-01-01 00:00:00+00'::timestamp with time zone)
  Index Searches: 1
  Buffers: shared hit=1029

7 rows expected, 50,000 returned. After ANALYZE readings:

Index Scan using readings_taken_at_idx on readings  (cost=0.42..1934.46 rows=47501 width=28) (actual time=0.015..5.111 rows=50000.00 loops=1)
  Index Cond: (taken_at >= '2027-01-01 00:00:00+00'::timestamp with time zone)
  Index Searches: 1
  Buffers: shared hit=1029

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights badly misestimated row counts as well as the slowest step, so a wrong estimate low in the plan stands out. You can also paste a plan into the free plan visualizer.