What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the planned execution. EXPLAIN ANALYZE actually runs the query and shows what really happened. The differences between planned and actual reveal bad statistics, wrong indexes, and other issues. Always use ANALYZE for diagnostic work.

What is the most important thing to look at?

The operation that took the most time. The output is a tree. Each node shows the time. The slowest node is usually where the fix is. The fix is often an index, a query rewrite, or a parameter change.

What is a sequential scan and when is it bad?

A seq scan reads every row in the table. Fine on small tables. Brutal on large ones. The fix is the right index. The EXPLAIN ANALYZE shows whether the planner chose seq scan or index scan.

What is the difference between index scan and index only scan?

Index scan reads the index then fetches the row from the table. Index only scan reads only the index because all the needed columns are in the index. Index only scan is faster. The INCLUDE clause on the index can make a regular query an index only query.

What is a nested loop join and when does it hurt?

A nested loop joins by looking up each row of the outer table in the inner table. Fast when the outer is small or the lookup is by index. Slow when the outer is large and the inner is unindexed. The fix is often the right index on the join column.

The database builds a hash table on one side of the join and probes it with the other. Fast for large joins where neither side has a useful index. Memory intensive. The planner picks this when it expects it to be the fastest.

What is the BUFFERS option?

EXPLAIN ANALYZE BUFFERS shows how many disk blocks were read from cache versus from disk. High disk reads suggest the working set does not fit in memory. The fix is more memory or a smaller working set.

Performance OptimizationMay 22, 202612 min read

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

EXPLAIN ANALYZE runs a query and reports the actual execution plan including timing, row counts, and the operations the database performed. The output is the most useful diagnostic for query performance work. The format looks complex. The patterns are simple. The engineers who can read EXPLAIN ANALYZE fix performance problems in minutes that take other engineers days.

Written by Yashveer Singh, founder of Yashveer Labs.

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.

The teams that have engineers fluent in EXPLAIN ANALYZE solve performance problems quickly. The teams that do not throw hardware at problems that an 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

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

A one week learning plan

Day one. Read the Postgres EXPLAIN documentation.
Day two. Run EXPLAIN ANALYZE on five queries in your codebase.
Day three. Identify the slowest node in each. Hypothesize the fix.
Day four. Apply fixes. Run EXPLAIN ANALYZE again. Verify improvement.
Day five. Read about the join algorithms in detail.
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

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