How to Read PostgreSQL EXPLAIN ANALYZE Output for Query Optimization

Apr 17, 2026

1. The Problem: My Query Was Slow

I had a PostgreSQL query that was taking forever. I ran EXPLAIN on it, and the plan looked reasonable - low cost estimates, nothing scary. But when I actually executed the query, it ran for minutes.

The issue? EXPLAIN only shows estimates. I needed EXPLAIN ANALYZE to see what was really happening.

2. Environment

PostgreSQL 14+
Any Linux/MacOS environment
A slow query to diagnose

3. The Solution

3.1 Run EXPLAIN ANALYZE with BUFFERS

I started with the essential diagnostic command:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;

The BUFFERS option is critical - it shows disk I/O activity so you can see where time is actually spent. Without it, you’re missing half the picture.

3.2 What I Found: Three Key Indicators

When I examined the output, I looked for these specific patterns:

Indicator 1: Row Count Mismatch

Index Scan using orders_customer_id_idx on orders  (cost=0.43..8.45 rows=2 width=100) (actual time=0.025..45.123 rows=200 loops=1)

The planner estimated 2 rows but actually got 200. This “butterfly effect” is the most common cause of bad performance - PostgreSQL picks nested loop join thinking it’s cheap, but with 200 iterations it becomes expensive.

Indicator 2: External Sort

Sort Method: external merge  Disk: 2048kB

This means work_mem was too low, forcing PostgreSQL to spill to disk during sorting. Disk I/O is orders of magnitude slower than memory operations.

Indicator 3: High Heap Fetches in Index-Only Scan

Index Only Scan using orders_customer_id_idx on orders  (actual rows=2000 loops=1)
  Heap Fetches: 2000

Index-only scans should have Heap Fetches: 0 or very low numbers. When it equals the row count, the visibility map is stale and PostgreSQL can’t skip checking the heap for each row.

3.3 How I Fixed Each Problem

Fix for Bad Estimates:

-- Update statistics for the specific table
ANALYZE orders;

-- For correlated columns the planner doesn't understand
CREATE STATISTICS s1 (dependencies) ON customer_id, order_date FROM orders;
ANALYZE orders;

After running this, my row estimates matched reality and PostgreSQL switched from nested loop to hash join - execution time dropped from 45 seconds to 2 seconds.

Fix for External Sorts:

-- Check current setting
SHOW work_mem;

-- Increase for this session only
SET work_mem = '256MB';

-- Or set permanently in postgresql.conf
-- work_mem = '256MB'

With adequate work_mem, the sort stayed in memory:

Sort Method: quicksort  Memory: 2048kB

Fix for High Heap Fetches:

VACUUM orders;

-- Check autovacuum is running
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';

After vacuum, the index-only scan became truly index-only:

Index Only Scan using orders_customer_id_idx on orders  (actual rows=2000 loops=1)
  Heap Fetches: 0

3.4 Lossy Bitmap Scans (Another work_mem Issue)

I also encountered a less obvious work_mem problem:

SET work_mem = '64kB';
EXPLAIN (ANALYZE) SELECT * FROM large_table WHERE status = 'active';

The output showed:

Bitmap Heap Scan on large_table  (actual rows=50000 loops=1)
  Rows Removed by Index Recheck: 150000
  Heap Blocks: exact=752 lossy=4654

The word “lossy” is the warning sign - PostgreSQL resorted to a lossy bitmap because memory was insufficient. Each “recheck” row means extra work.

I reset work_mem to a reasonable value:

RESET work_mem;

Now the output showed only “exact” blocks, and execution time dropped significantly.

3.5 Common Mistakes I Made

Mistake 1: Using EXPLAIN without ANALYZE

I initially ran just EXPLAIN and trusted the cost estimates. But a plan that looks “cheap” (low cost numbers) might actually run for minutes. ANALYZE runs the query and shows real execution times.

Mistake 2: Running on Production During Peak Load

EXPLAIN ANALYZE actually executes the query. For slow queries, this can impact production. I learned to:

Test on a copy of the database
Run during low-traffic periods
Wrap in a transaction and rollback for write queries

Mistake 3: Ignoring “Rows Removed” Metrics

I initially ignored the Rows Removed by Index Recheck metric. But this shows work_mem problems clearly - each removed row means unnecessary work.

4. The Optimization Workflow

I developed a systematic approach:

1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query
2. Scan output for three key indicators:
   - Row count mismatch (estimated vs actual)
   - External sort or "lossy" in output
   - High Heap Fetches in Index-Only Scan
3. Apply the matching fix:
   - Mismatch -> ANALYZE table / CREATE STATISTICS
   - External sort -> increase work_mem
   - High Heap Fetches -> VACUUM table
4. Re-run EXPLAIN ANALYZE to verify improvement

5. Why EXPLAIN ANALYZE Is Essential

Without EXPLAIN ANALYZE, I was guessing at optimization targets. With it, I get:

Verified diagnosis - Actual execution time, not just estimates
Precise targeting - Know exactly which operation is slow
Before/after comparison - Measure optimization impact

The most common performance problems in PostgreSQL stem from just three causes: bad statistics, insufficient work_mem, or stale visibility maps. EXPLAIN ANALYZE reveals which one is your culprit.

6. Summary

I learned that PostgreSQL query optimization is systematic once you know what to look for. Run EXPLAIN (ANALYZE, BUFFERS), scan for row mismatches (fix with ANALYZE), external sorts (fix with work_mem), and high heap fetches (fix with VACUUM). These three indicators cover most PostgreSQL query performance problems.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!