PostgreSQL EXPLAIN Query Plans

A query tells PostgreSQL what you want. The planner decides how to get it. EXPLAIN reveals that plan before or after the run. Think of a navigation app. You enter a destination, and the app shows the route it picked, the roads it uses, and the estimated time.

  SQL query --> Planner picks best route --> Executor follows route
                       |
                    EXPLAIN shows this route

Reading plans lets you find slow queries and fix them with evidence rather than guesses.

Reading EXPLAIN Output

Two commands show a plan. One estimates, and the other measures.

Running EXPLAIN

EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

A sample output looks like this.

Seq Scan on orders  (cost=0.00..1834.00 rows=12 width=48)
  Filter: (customer_id = 42)

The plan lists steps called nodes. Each node shows its type and three numbers.

Part	Meaning
cost=0.00..1834.00	Start-up cost and total cost in abstract units
rows=12	Estimated number of rows returned
width=48	Estimated average row size in bytes

Cost units have no direct link to seconds. Lower numbers mean cheaper plans.

EXPLAIN ANALYZE

Plain EXPLAIN only estimates. EXPLAIN ANALYZE runs the query and reports real numbers.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=12 width=48)
                    (actual time=0.020..14.500 rows=15 loops=1)
Planning Time: 0.080 ms
Execution Time: 14.600 ms

Compare estimated rows with actual rows. A large gap signals out-of-date statistics. Run ANALYZE orders; to refresh them.

EXPLAIN ANALYZE really executes the statement. Wrap INSERT, UPDATE, and DELETE tests in a transaction and finish with ROLLBACK.

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE order_date < '2020-01-01';
ROLLBACK;

Common Scan Types

A scan node describes how PostgreSQL reads a table. Four types appear most often in plans.

Sequential Scan

PostgreSQL reads every row from the first to the last. It resembles reading a whole book to find one name. Small tables and queries that return most rows use this scan happily.

Index Scan

PostgreSQL jumps through an index to the matching rows. It resembles using the index at the back of a book.

Index Only Scan

The index alone holds every needed column, so the table is never visited. This scan runs fastest.

Bitmap Heap Scan

PostgreSQL collects matching row locations from an index and visits the table pages in order. It suits queries that return a moderate number of rows.

Tuning with Plans

Plans point to the slow parts of a query and show whether a change helps.

Fixing a Slow Query

Create an index on the filter column and check the plan again.

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer on orders
  (cost=0.29..8.45 rows=15 width=48)
  (actual time=0.015..0.030 rows=15 loops=1)
Execution Time: 0.050 ms

The run time drops from 14.6 ms to 0.05 ms. The plan proves the index helps.

Join Methods

Method	How It Works	Best For
Nested Loop	For each row on one side, look up matches on the other	Small inputs or indexed lookups
Hash Join	Builds a lookup table in memory from one side	Larger inputs without sorting
Merge Join	Walks two sorted lists together	Large sorted inputs

Reading Plans Well

A few options and habits turn raw plan text into clear answers.

Useful Options

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;

BUFFERS shows how many memory and disk pages the query touched. FORMAT JSON produces output that visual plan tools can read.

Reading a Plan

Start at the innermost, most indented line. Execution flows from the deepest nodes upward to the top node.

Sort
  -> Hash Join
       -> Seq Scan on orders
       -> Hash
            -> Seq Scan on customers

Here PostgreSQL scans customers, builds a hash, scans orders, joins them, and sorts the result.

Warning Signs

A sequential scan on a huge table that returns few rows.
Estimated rows far from actual rows.
A nested loop with thousands of loops.
A sort that spills to disk.

Previous lesson

Back to course

Next lesson