Skip to main content
PostgreSQL · lesson 1 of 7

Intermediate

6

Advanced

1

Also part of PostgreSQL

7
Open the PostgreSQL page →
PostgreSQL intermediate Lesson 1 of 7

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows what the planner guesses a query will cost; EXPLAIN ANALYZE actually runs it and shows what really happened. Confusing the two means trusting an estimate that may be wrong.

By SkillByExample · Published

postgresql explain-analyze query-planning performance

A query plan is a guess until it’s actually measured — EXPLAIN and EXPLAIN ANALYZE are two different levels of trust in that guess.

The dataset

Every lesson in this track uses the same seed data: 200,000 books and 50,000 loan records, loaded into a Postgres 17 container with no indexes beyond the primary keys.

SELECT count(*) FROM books;
SELECT count(*) FROM loans;
 count
--------
 200000
(1 row)

 count
-------
 50000
(1 row)

EXPLAIN: the plan, without running anything

EXPLAIN SELECT * FROM books WHERE id = 42;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using books_pkey on books  (cost=0.42..8.44 rows=1 width=118)
   Index Cond: (id = 42)
(2 rows)

cost=0.42..8.44 and rows=1 are the planner’s estimate, based on table statistics — the query was never actually executed. Useful for a quick sanity check, but it’s still a guess.

EXPLAIN ANALYZE: the plan, actually measured

EXPLAIN ANALYZE SELECT * FROM books WHERE id = 42;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using books_pkey on books  (cost=0.42..8.44 rows=1 width=118) (actual time=0.019..0.019 rows=1 loops=1)
   Index Cond: (id = 42)
 Planning Time: 0.342 ms
 Execution Time: 0.063 ms
(4 rows)

Same estimate as before, plus actual time=0.019..0.019 rows=1 — the query really ran, took 0.019ms to find the first row and 0.019ms total, and really returned exactly 1 row. The estimate (rows=1) and reality (rows=1) agree here, which is exactly why this query is fast.

When there’s no index, the estimate and reality both get worse

EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'mystery';
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..6338.00 rows=40380 width=118) (actual time=0.006..13.769 rows=40000 loops=1)
   Filter: (genre = 'mystery'::text)
   Rows Removed by Filter: 160000
 Planning Time: 0.409 ms
 Execution Time: 14.749 ms
(5 rows)

Seq Scan means Postgres read every one of the 200,000 rows, checked each one against the filter, and threw away 160,000 of them (Rows Removed by Filter: 160000) to find the 40,000 that matched. genre has no index — there’s no faster way to answer this query yet. The next lesson fixes exactly this.

Practice

1. Run EXPLAIN ANALYZE on a query filtering by published_year and read the Seq Scan numbers. 
EXPLAIN ANALYZE SELECT * FROM books WHERE published_year = 2000;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..6126.37 rows=2467 width=118) (actual time=0.203..18.169 rows=2667 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on books  (cost=0.00..4879.67 rows=1028 width=118) (actual time=0.023..10.219 rows=889 loops=3)
         Filter: (published_year = 2000)
         Rows Removed by Filter: 65778
 Planning Time: 3.789 ms
 Execution Time: 20.159 ms
(8 rows)

No index on published_year either, so it’s still a full scan — but this time the planner decided the table was big enough to split the work across 2 parallel workers (Gather + Parallel Seq Scan, each worker handling roughly a third of the 197,333 non-matching rows). Same underlying problem as the genre query, just handled with more machinery because Postgres judged it worthwhile here.

2. Roll back a real EXPLAIN ANALYZE on a DELETE so it doesn't actually delete anything. 
BEGIN;
EXPLAIN ANALYZE DELETE FROM loans WHERE borrower = 'Borrower 1';
ROLLBACK;
BEGIN
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Delete on loans  (cost=0.00..1033.00 rows=0 width=0) (actual time=3.319..3.319 rows=0 loops=1)
   ->  Seq Scan on loans  (cost=0.00..1033.00 rows=50 width=6) (actual time=0.009..3.261 rows=50 loops=1)
         Filter: (borrower = 'Borrower 1'::text)
         Rows Removed by Filter: 49950
 Planning Time: 0.431 ms
 Execution Time: 4.140 ms
(6 rows)
ROLLBACK

The delete really ran inside the transaction — ROLLBACK is what makes it safe to measure without losing data.

3. Compare the cost estimate against a query that returns nothing at all. 
EXPLAIN ANALYZE SELECT * FROM books WHERE id = -1;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using books_pkey on books  (cost=0.42..8.44 rows=1 width=118) (actual time=0.051..0.051 rows=0 loops=1)
   Index Cond: (id = '-1'::integer)
 Planning Time: 0.375 ms
 Execution Time: 0.097 ms
(4 rows)

The estimate still says rows=1 (the planner assumes a primary key lookup returns one row), but actual ... rows=0 shows reality: no row has id = -1. The estimate and reality can diverge even on an indexed lookup — this is normal, not a sign of a broken index.

Next: indexing strategy — turning that Seq Scan on genre into an Index Scan, and the cases where an index doesn’t actually help.

Frequently Asked Questions

Why do EXPLAIN and EXPLAIN ANALYZE show different numbers for the same query?
EXPLAIN alone never runs the query — it prints the planner's cost estimate, based on table statistics, and nothing else. EXPLAIN ANALYZE actually executes the query and reports real elapsed time and real row counts alongside the same estimate, so the two can be compared directly.
Is it safe to run EXPLAIN ANALYZE on a write query like UPDATE or DELETE?
It actually performs the write — EXPLAIN ANALYZE executes the query, full stop. Running it on a DELETE really deletes the rows. Wrap it in a transaction you roll back (BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;) to see the real plan without the real effect.
What do the cost numbers like cost=0.00..6338.00 actually mean?
They're arbitrary planner units, not milliseconds — the first number is the estimated cost to return the first row, the second is the estimated cost to return all rows. They're only useful for comparing plans relative to each other, never as an absolute time prediction; actual time in EXPLAIN ANALYZE's output is the real measurement in milliseconds.
What does 'Rows Removed by Filter' tell you?
It's the number of rows the scan read and then discarded because they didn't match the WHERE clause — a large number here on a Seq Scan is a direct signal that the query is reading far more data than it needs, which is exactly the kind of query an index can fix.
Why did the primary key lookup use an Index Scan while the genre filter used a Seq Scan?
id has a unique index automatically created by PRIMARY KEY; genre has no index at all on this table yet, so the only way to find matching rows is to read every row and check. The planner isn't being inconsistent — it's using the only access path that actually exists for each column.