EXPLAIN & Query Plans

EXPLAIN shows you how PostgreSQL plans to execute a query — before it runs. EXPLAIN ANALYZE actually runs the query and shows real performance data. Understanding query plans is essential for finding and fixing slow queries.

Basic EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using orders_customer_id_idx on orders  (cost=0.43..12.50 rows=5 width=64)
   Index Cond: (customer_id = 42)

Reading the Plan Tree

Query plans are trees — PostgreSQL reads them from the innermost/bottom node up to the top:

EXPLAIN
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 80000;

QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=1.07..2.47 rows=3 width=64)
   Hash Cond: (e.dept_id = d.id)
   ->  Seq Scan on employees e  (cost=0.00..1.30 rows=3 width=40)
         Filter: (salary > 80000)
   ->  Hash  (cost=1.03..1.03 rows=3 width=36)
         ->  Seq Scan on departments d  (cost=0.00..1.03 rows=3 width=36)

Reading this plan:

1. Seq Scan on employees — scan all employees, filter salary > 80000
2. Seq Scan on departments — scan all departments, build a hash table from the results
3. Hash Join — for each filtered employee, look up their department in the hash table

The Cost Numbers

(cost=0.43..12.50 rows=5 width=64) means:

Costs are relative, not absolute. Lower is better. Costs are estimates based on table statistics.

EXPLAIN ANALYZE — Real Performance

EXPLAIN ANALYZE actually executes the query and shows actual vs. estimated times:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using orders_customer_id_idx on orders
   (cost=0.43..12.50 rows=5 width=64)
   (actual time=0.082..0.091 rows=3 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.356 ms
 Execution Time: 0.125 ms

The actual time=X..Y rows=Z loops=N shows:

• actual time: real startup..total time in milliseconds
• rows: real row count returned
• loops: how many times this node ran (useful for nested loops)

Full Options

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM large_table WHERE status = 'pending';

QUERY PLAN
-------------------------------------------------------------------------------------
 Seq Scan on large_table  (cost=0.00..55432.00 rows=1234 width=128)
                           (actual time=0.012..423.521 rows=1189 loops=1)
   Filter: ((status)::text = 'pending'::text)
   Rows Removed by Filter: 2099811
   Buffers: shared hit=12456 read=30576
 Planning Time: 0.212 ms
 Execution Time: 423.601 ms

-- JSON format for programmatic analysis or pgAdmin / EXPLAIN.depesz.com
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Scan Types

Sequential Scan (Seq Scan)

Reads every row in the table. PostgreSQL chooses this when:

• There's no suitable index
• The query would return a large portion of the table (> ~5-10% for small tables)

Seq Scan on orders  (cost=0.00..55432.00 rows=2100000 width=80)
  Filter: (status = 'active')
  Rows Removed by Filter: 100000

A Seq Scan is not always bad — for very small tables or queries returning most of the table, it can be the fastest option.

Index Scan

Uses a B-tree index to find rows, then fetches from the heap (table data):

Index Scan using orders_customer_idx on orders  (cost=0.43..12.5 rows=5 width=80)
  Index Cond: (customer_id = 42)

Index Only Scan

Like Index Scan, but the index contains all needed columns — no heap access:

Index Only Scan using orders_cov_idx on orders  (cost=0.43..8.5 rows=5 width=20)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0   ← never touched the table!

For this to work, use a covering index with INCLUDE (col1, col2).

Bitmap Index Scan / Bitmap Heap Scan

Used when a query would return many rows via an index. PostgreSQL collects all matching index entries into a bitmap, then fetches heap pages in order (more efficient I/O than random access):

Bitmap Heap Scan on orders  (cost=234.5..1234.0 rows=5000 width=80)
  Recheck Cond: (status = 'pending')
  ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..233.3 rows=5000 width=0)
        Index Cond: (status = 'pending')

Join Types

Nested Loop

For each row in the outer table, scan the inner table. Fast when the inner table has an index and the outer result is small:

Nested Loop  (cost=0.43..50.00 rows=5 width=64)
  ->  Seq Scan on departments d  (rows=3)
  ->  Index Scan on employees e using emp_dept_idx
        Index Cond: (dept_id = d.id)

Hash Join

Build a hash table from the smaller table, then probe it with each row from the larger table. Good for medium-to-large tables without useful indexes:

Hash Join  (cost=1.07..25.00 rows=100 width=64)
  Hash Cond: (e.dept_id = d.id)
  ->  Seq Scan on employees e  (rows=1000)
  ->  Hash  (cost=1.03..1.03 rows=3)
        ->  Seq Scan on departments d  (rows=3)

Merge Join

Both inputs must be sorted on the join key. Fast when both sides are already sorted (e.g., via index):

Merge Join  (cost=0.56..15.00 rows=50 width=64)
  Merge Cond: (e.dept_id = d.id)
  ->  Index Scan using emp_dept_idx on employees e
  ->  Index Scan using departments_pkey on departments d

Interpreting Buffer Usage

With BUFFERS option:

Buffers: shared hit=1250 read=350

High read vs hit ratio means your shared_buffers setting may be too small, or the working set doesn't fit in cache.

Common Query Plan Problems

Problem 1: Expected index, got Seq Scan

-- This might not use an index if stats are stale
SELECT * FROM orders WHERE status = 'pending';

Fix: Update statistics

ANALYZE orders;  -- update stats on this table
-- Then EXPLAIN again

Problem 2: Massive Rows Removed by Filter

Seq Scan on orders (rows=2100000)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 2099900

Fix: Add an index on the filter column:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Problem 3: Planner Underestimates Rows

Seq Scan: estimated rows=5, actual rows=50000

Fix: Increase statistics target on the column:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Problem 4: Slow Nested Loop With Many Outer Rows

Nested Loop (loops=100000, total time=5000ms)

Fix: Consider increasing join_collapse_limit, adding an index, or temporarily setting enable_nestloop = off to see if hash join is faster:

-- Test with nested loop disabled
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_nestloop;

EXPLAIN.depesz.com and pgMustard

Paste your EXPLAIN output into these online tools for color-coded, annotated plan analysis:

• explain.depesz.com — classic, widely used
• pgmustard.com — modern, gives specific recommendations
• explain.dalibo.com — graphical plan visualization