EXPLAIN ANALYZE: A Tour of PostgreSQL's Best Diagnostic Tool

What you actually need to know

• EXPLAIN ANALYZE runs the query and shows the actual plan.
• The slowest node in the tree is usually where the fix is.
• Sequential scans on large tables are usually the problem.
• Nested loop joins on large unindexed columns are problems.
• BUFFERS shows cache versus disk reads.

Plan node

What it means

When to worry

Seq Scan

Reading every row

On large tables

Index Scan

Using an index

Almost always fine

Index Only Scan

Reading only index

Better than index scan

Nested Loop

Per row inner lookup

Large outer, unindexed inner

Hash Join

Hash table join

Memory pressure

Merge Join

Sorted merge

Both sides already sorted

Sort

Ordering rows

Large unsorted result

Bitmap Scan

Combining indexes

OR conditions usually

The core argument

EXPLAIN ANALYZE is the most useful Postgres diagnostic tool that most engineers never bother to learn. The output looks intimidating. The patterns are actually simple. The engineers who learn to read it fix performance problems in minutes that take other engineers days.

The mental model is small. The output is a tree. Each node represents an operation. The slowest node is usually where the fix is. The patterns of what the slowest node means are limited. Sequential scan on a large table means you need an index. Nested loop with high cost means you need an index on the join column. Sort with high cost means you have a missing index or a large result that should be smaller.

The investment to learn is hours. Read the documentation. Run EXPLAIN ANALYZE on a few slow queries. Identify the slowest node. Try a fix. Run again. The pattern recognition builds quickly. After a week of practice the tool becomes natural.

A team with even one engineer fluent in EXPLAIN ANALYZE solves performance problems quickly, because the diagnosis stops being a guessing game. A team without that fluency tends to throw hardware at problems that a single index would have fixed. The investment in learning is small. The savings on infrastructure and engineering time is large.

The patterns to recognize

Pattern

Likely fix

Seq Scan on large table with WHERE clause

Add index on the WHERE columns

Nested Loop with high row estimate

Add index on join columns or rewrite

Sort with high cost

Index on the ORDER BY columns

Filter after index scan

Multi column index that covers the filter

Bitmap Heap Scan with many rows

Consider a more selective index

High estimated vs actual rows

Statistics out of date. ANALYZE the table.

Disk reads dominate buffers

Working set too large for memory

Hash Join with high memory

Larger work_mem or query rewrite

How much does this cost

The cost of learning EXPLAIN ANALYZE is hours. The savings is the performance work that goes faster. A team where one engineer is fluent in EXPLAIN ANALYZE handles most query performance work efficiently. A team where nobody is fluent struggles with the same problems repeatedly.

Features the EXPLAIN ANALYZE workflow must have

• A practice of running EXPLAIN ANALYZE on suspect queries.
• pg_stat_statements enabled to find candidates.
• A way to capture and share plan output.
• A learning practice for new engineers.
• A way to compare plans before and after a change.
• BUFFERS analysis for cache pressure questions.
• A documented set of patterns and fixes.

Expert opinion

EXPLAIN ANALYZE is the engineering equivalent of reading lab results. Once you can read it, performance problems become diagnosable rather than mysterious. The patterns are limited. The fixes are mechanical. The teams that invest in learning the tool solve performance work in hours that other teams cannot solve in weeks. The investment is hours. The return compounds across every performance investigation.

Yashveer Singh, founder of Yashveer Labs

How this played out on a real project

A client SaaS reported that a specific report endpoint was slow. The team had spent two weeks investigating without finding the cause. The senior engineer had not run EXPLAIN ANALYZE.

We ran EXPLAIN ANALYZE on the query. The plan showed a sequential scan on a multi million row table because of a missing index on a filter column. The fix was a one line CREATE INDEX. The query latency dropped from 4 seconds to 30 milliseconds.

The team had been guessing at the cause for two weeks. The plan made the cause obvious in two minutes. The team adopted EXPLAIN ANALYZE as standard practice. The next performance investigation took an hour instead of two weeks.

For more on the related work, see database indexes a practical primer for SaaS engineers and database query performance the five patterns that hurt the most.

Common mistakes engineers make

1. Never running EXPLAIN ANALYZE.
2. Using EXPLAIN instead of EXPLAIN ANALYZE.
3. Ignoring the slowest node in the tree.
4. No comparison before and after a change.
5. No BUFFERS analysis when memory might matter.
6. Stale statistics. ANALYZE the table.
7. Treating the output as too complex to read.
8. No team practice. The fluency does not spread.

A one week learning plan

1. Day one. Read the Postgres EXPLAIN documentation.
2. Day two. Run EXPLAIN ANALYZE on five queries in your codebase.
3. Day three. Identify the slowest node in each. Hypothesize the fix.
4. Day four. Apply fixes. Run EXPLAIN ANALYZE again. Verify improvement.
5. Day five. Read about the join algorithms in detail.
6. Day six and seven. Practice on more queries. Build pattern recognition.

For more on the related work, read database indexes a practical primer for SaaS engineers and query optimization in PostgreSQL real examples from real projects. On the broader performance side, the slow query log a discipline every SaaS team should practice is the natural next read.

FAQ

Frequently asked

• What is the difference between EXPLAIN and EXPLAIN ANALYZE?
• What is the most important thing to look at?
• What is a sequential scan and when is it bad?
• What is the difference between index scan and index only scan?
• What is a nested loop join and when does it hurt?
• What is a hash join?
• What is the BUFFERS option?

Author

The work I take and why

I take work that compounds. I do not take work that is rework with extra steps. Yashveer Singh, founder of Yashveer Labs. If the topic on this page is what you are dealing with, the question is not whether it can be solved. It can. The question is whether you want to solve it once or four times. I am the person who solves it once.

Start the conversation See the work DM on Instagram