How to Read PostgreSQL EXPLAIN Output to Find Slow Query Causes

Apr 17, 2026

My query was taking 47 seconds to run. I stared at the terminal, wondering where to even start debugging. The table had millions of rows, but I had an index on the column I was filtering. So why was it so slow?

That’s when I remembered PostgreSQL has a built-in diagnostic tool: EXPLAIN. But looking at the output felt like reading hieroglyphics—costs, rows, scans, joins. What did any of it mean?

The Problem: Deciphering EXPLAIN Output

PostgreSQL’s EXPLAIN command is your primary tool for understanding query performance, but its output can be overwhelming. You see a tree of nodes with numbers like cost=0.00..12345.67, but how do these translate to actual performance issues?

Understanding EXPLAIN Output Structure

Every EXPLAIN output is a hierarchical tree. Each node represents an operation the database performs:

Result
└── Nested Loop (cost=0.00..12500.00 rows=500 width=100)
    ├── Index Scan using idx_users on users (cost=0.00..8.27 rows=1 width=50)
    │   Index Cond: (id = 12345)
    └── Seq Scan on orders (cost=0.00..12400.00 rows=500 width=50)
        Filter: (user_id = 12345)

Each node shows:

cost=startup..total — Relative effort estimate (not actual time!)
rows — Estimated rows this node will produce
width — Average bytes per row

EXPLAIN vs EXPLAIN ANALYZE

The key difference:

-- Shows the plan WITHOUT executing
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- EXECUTES the query and shows real times
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Warning: EXPLAIN ANALYZE actually runs your query. Be careful with UPDATE and DELETE statements—use a transaction with rollback if needed.

The Key Metrics to Watch

1. Row Count Discrepancies

When estimated rows differ wildly from actual rows, the planner made bad decisions:

Seq Scan on large_table  (cost=0.00..150000.00 rows=1000 loops=1)
                         (actual time=0.015..5000.123 rows=500000 loops=1)
  Filter: (status = 'active')
  Rows Removed by Filter: 50000

The planner thought 1,000 rows would match, but 500,000 did. This probably caused it to choose a sequential scan when an index scan would be faster.

Fix: Run ANALYZE your_table; to update statistics.

2. Sequential Scans on Large Tables

Seq Scan on orders  (cost=0.00..250000.00 rows=10000000 width=100)

Sequential scans read every row. On large tables, this is slow. Ask yourself:

Should there be an index here?
Is the WHERE clause selective enough?
Are my statistics up to date?

3. Join Method Selection

PostgreSQL has three main join strategies:

-- Nested Loop: Good for small result sets
Nested Loop  (cost=0.00..15.00 rows=10)
  -> Index Scan on small_table
  -> Index Scan on large_table

-- Hash Join: Good for large unsorted joins
Hash Join  (cost=10000.00..50000.00 rows=100000)
  -> Seq Scan on table_a
  -> Hash
    -> Seq Scan on table_b

-- Merge Join: Good for pre-sorted data
Merge Join  (cost=0.00..30000.00 rows=50000)
  -> Index Scan on table_a
  -> Index Scan on table_b

4. High-Cost Nodes

Cost isn’t time, but it’s proportional. The node with the highest total cost is usually your bottleneck. Focus optimization efforts there.

A Real-World Debugging Session

I had this slow query:

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01'
AND o.status = 'pending'
ORDER BY o.total DESC
LIMIT 100;

Running EXPLAIN ANALYZE:

Limit  (cost=500000.00..500025.00 rows=100) (actual time=45000..45002 rows=100)
  -> Sort  (cost=500000.00..500100.00 rows=400000) (actual time=45000..45001 rows=100)
        Sort Key: o.total DESC
        Sort Method: top-N heapsort  Memory: 35kB
        -> Hash Join  (cost=10000.00..480000.00 rows=400000) (actual time=500..42000 rows=400000)
              Hash Cond: (o.customer_id = c.id)
              -> Seq Scan on orders  (cost=0.00..350000.00 rows=400000) (actual time=0.01..20000 rows=400000)
                    Filter: ((created_at > '2025-01-01'::date) AND (status = 'pending'::text))
                    Rows Removed by Filter: 9600000
              -> Hash  (cost=5000.00..5000.00 rows=100000) (actual time=50..50 rows=100000)
                    -> Seq Scan on customers  (cost=0.00..5000.00 rows=100000)

The problem: sorting 400,000 rows and doing a sequential scan on orders. I added a composite index:

CREATE INDEX idx_orders_status_created_total ON orders(status, created_at, total DESC);

After the index:

Limit  (cost=0.43..150.00 rows=100) (actual time=0.05..2.00 rows=100)
  -> Nested Loop  (cost=0.43..150.00 rows=100) (actual time=0.05..2.00 rows=100)
        -> Index Scan using idx_orders_status_created_total on orders
            (cost=0.43..100.00 rows=100) (actual time=0.03..0.50 rows=100)
              Index Cond: ((status = 'pending'::text) AND (created_at > '2025-01-01'::date))
        -> Index Scan using customers_pkey on customers
            (cost=0.00..0.50 rows=1) (actual time=0.01..0.01 rows=1)
              Index Cond: (id = o.customer_id)

From 45 seconds to 2 milliseconds. The index let PostgreSQL read only what it needed.

Common Pitfalls

Assuming Cost Equals Time

Cost is unitless—it’s a relative estimate the planner uses to compare plans. A plan with cost=1000 isn’t necessarily faster than cost=2000—you need EXPLAIN ANALYZE for actual times.

Ignoring Statistics

If you’ve loaded lots of new data, the planner’s statistics are stale:

-- Update statistics for a specific table
ANALYZE your_table;

-- Or update entire database
ANALYZE;

Adding Indexes Blindly

An index that isn’t used is just write overhead. Always verify with EXPLAIN that your index is actually being used.

Not Understanding Planner Limits

The planner estimates based on statistics. If your data has correlations it doesn’t know about, or if you’re using functions it can’t estimate (like custom functions), expect suboptimal plans.

When to Suspect What

Symptom	Likely Cause
Seq Scan on indexed column	Stale stats, non-selective filter, or function on column
Rows estimate way off	Need to run ANALYZE
Nested Loop with millions of iterations	Missing index on join column
Sort spilling to disk	work_mem too low
Parallel query not used	max_parallel_workers too low

Quick Diagnostic Checklist

Run EXPLAIN ANALYZE on the slow query
Find the highest-cost node
Check for Seq Scans on large tables
Compare estimated rows vs actual rows
Look for nested loops with high loop counts
Consider if indexes match your query patterns

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!