Mastering PostgreSQL EXPLAIN ANALYZE: How to Read Execution Plans and Fix Slow Queries

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Quick start (5 min)

Your API endpoint latency spikes from 45ms to 3.2s during peak traffic. Nine times out of ten, the culprit is a database query choking under load. While tools like MySQL or MongoDB offer query inspection, PostgreSQL gives you exceptional visibility into its planner decisions. You just need to know how to read EXPLAIN ANALYZE.

Prefix any SQL statement with EXPLAIN ANALYZE in psql or your database GUI to see how Postgres actually runs your query:

EXPLAIN ANALYZE
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'completed'
  AND created_at >= '2026-01-01';

The database executes the query and returns a detailed execution tree:

Seq Scan on orders  (cost=0.00..1845.00 rows=1420 width=36) (actual time=0.045..14.821 rows=1530 loops=1)
  Filter: ((created_at >= '2026-01-01'::date) AND ((status)::text = 'completed'::text))
  Rows Removed by Filter: 85470
Planning Time: 0.182 ms
Execution Time: 15.012 ms

Start at the bottom. Planning Time (0.182 ms) represents the time the optimizer spent calculating the cheapest execution path. Execution Time (15.012 ms) measures real disk I/O, row retrieval, and filter evaluation. Plain EXPLAIN only displays theoretical cost models. Adding ANALYZE runs the query, returning ground-truth runtimes alongside planner estimates.

Deep dive

Execution plans read from the inside out and bottom to top. Child nodes run first, passing their output rows upward to parent nodes.

Deconstructing the Node Metrics

Each plan node presents two distinct metric sets: estimated values followed by actual execution tallies.

  • cost=0.00..1845.00: Arbitrary work units based on disk page fetches and CPU cycles. 0.00 is startup cost (time until the node yields row one). 1845.00 is the estimated cost to process every matching row.
  • rows=1420: The optimizer’s row estimate, derived from statistical distributions stored in pg_statistic.
  • width=36: Expected row footprint in bytes.
  • actual time=0.045..14.821: Wall-clock duration in milliseconds. The first row materialized at 0.045 ms; all 1,530 rows completed at 14.821 ms.
  • rows=1530: The actual count of rows returned. Compare this against estimated rows (1,420). Close numbers mean healthy statistics.
  • loops=1: Iteration count. If an inner loop inside a nested join runs 50 times, multiply reported times and row counts by 50 to calculate total resource consumption.

Scan Strategies: Sequential vs Index

Postgres selects from four primary scan methods depending on table volume, index presence, and column cardinality:

  • Seq Scan (Sequential Scan): Streams the full table from disk page by page. Notice Rows Removed by Filter: 85470 in our earlier plan. The engine inspected 87,000 physical rows just to retain 1,530—wasting disk bandwidth on an 98.2% discard rate.
  • Index Scan: Navigates a B-Tree index to locate specific tuple pointers (TIDs), then fetches individual table rows directly from heap pages.
  • Bitmap Index Scan / Bitmap Heap Scan: Bridges sequential and index scans. The index scan first constructs a memory bitmap marking matching physical pages. Next, the heap scan reads those pages in physical storage order, preventing random seek thrashing.
  • Index Only Scan: The fastest scan path. The index contains every requested column, so Postgres skips visiting the heap entirely whenever the table visibility map confirms pages are clean.

Advanced usage

Reading node costs gets you halfway there. Pinpointing production bottlenecks requires drilling into memory spills, buffer hits, and planner edge cases.

Tracking Memory and Disk I/O with BUFFERS

Always include the BUFFERS option when troubleshooting slow queries. It proves whether data lived warm in RAM or required physical storage reads:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(o.id) AS total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country_code = 'US'
GROUP BY u.email
ORDER BY total_orders DESC
LIMIT 10;

The resulting plan reveals buffer metrics:

Buffers: shared hit=412 read=1895 dirtied=0 written=0
  • shared hit (412): Pages pulled directly from PostgreSQL shared_buffers in RAM. Fast and near-zero latency.
  • shared read (1895): Pages fetched from the OS cache or disk. At the default 8 kB per page, 1,895 reads translate to ~14.8 MB of disk or cache throughput. High read counts indicate cold cache issues or absent indexes.

Hunting Down Hidden Sequential Scans

You created an index on created_at, yet the planner still insists on a full table scan. Three common issues cause this behavior:

1. Type Mismatches and Implicit Casting

When an application driver passes parameters with mismatched types—such as passing a string literal to a bigint column or comparing differing text collations—Postgres applies a type conversion function around the column. B-Tree indexes cannot index expressions unless explicitly built as expression indexes.

-- Bad: Function wrapper prevents B-Tree index usage
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Good: Exact match leverages standard index on email
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Alternative: Create an expression index to support case-insensitive lookups
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

2. Column Transformations in the WHERE Clause

Wrapping timestamp columns in date functions strips away index eligibility. Avoid expressions like WHERE DATE(created_at) = '2026-03-01'. Use sargable boundary ranges instead:

-- Uses index range scan cleanly
EXPLAIN ANALYZE 
SELECT id, total_amount 
FROM orders 
WHERE created_at >= '2026-03-01 00:00:00'
  AND created_at <  '2026-03-02 00:00:00';

3. Low Selectivity Thresholds

The query planner calculates whether random I/O overhead beats raw sequential throughput. If your predicate matches more than 10% to 20% of the entire table, sequential scanning is often genuinely faster than hopping back and forth across an index. On fast NVMe drives where random_page_cost is close to seq_page_cost (e.g., 1.1), this trade-off shifts, but sequential scans on large result sets remain expected behavior.

Detecting Memory Spills in Sorts and Hashes

Check the sort node details in your output:

Sort Method: external merge  Disk: 18432kB

PostgreSQL sets work_mem to 4 MB by default. When sorts or hash tables outgrow this allocation, the engine writes temporary chunks to disk, cratering throughput. Raising work_mem for the active session resolves the spill without requiring a global database restart:

SET work_mem = '64MB';
-- Re-running the query now yields:
-- Sort Method: quicksort  Memory: 4120kB

Practical tips

Safe production debugging requires sensible operational guardrails:

  • Protect Live Data on Mutations: EXPLAIN ANALYZE executes statements. If you pass a DELETE or UPDATE, rows are modified immediately. Wrap mutating queries in a transaction block and roll back:
BEGIN;
EXPLAIN ANALYZE
DELETE FROM sessions WHERE expired_at < NOW();
ROLLBACK;
  • Refresh Stale Statistics: When estimated rows (10) diverge wildly from actual rows (50,000), the optimizer selects inefficient nested loop joins over hash joins. Rebuild column statistics using ANALYZE orders; to restore accurate plan estimates.
  • Automate Slow Query Logging: Skip manual query capture. Load the auto_explain module in postgresql.conf to automatically dump execution plans for any query crossing your performance SLA:
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
  • Visualize Giant Execution Trees: Complex queries with 15+ joins generate plans hundreds of lines long. Export the plan with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) and load it into visual diagnostic tools like PEV2 or Dalibo’s Explain. These highlight the costliest bottleneck nodes in seconds.
Share: