Query Planning and EXPLAIN

Before the database reads a single row, it plans. You submit a query, and the query planner considers several different strategies for answering it, estimates the cost of each, and picks the one it expects to be fastest. Only then does execution begin.

Learning to read that plan is one of the most practical things you can do as a SQL developer. When a query is slow, the plan stops the guessing and shows you exactly why.

EXPLAIN

EXPLAIN shows the plan the database intends to use, without actually executing the query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Safe to run at any time - no data is touched.

EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query and shows the planner's estimates alongside the real measurements:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

This is the one you will use most for diagnosing slow queries. The gap between what the planner expected and what actually happened is where the useful information is.

For more detail, add BUFFERS:

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

BUFFERS tells you how many data pages came from the cache versus disk. That distinction matters: a slow query from a cache miss is a different problem than a slow query from a missing index.

Reading the output

The output is a tree. Each node is one step. Child nodes run first and feed their results up to the parent. Read from the most-indented line outward - that is the execution order.

Seq Scan on orders  (cost=0.00..450.00 rows=12 width=108)
                    (actual time=0.02..3.41 rows=15 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 4985

Breaking down the key pieces:

Term	Meaning
`cost=0.00..450.00`	Estimated work: startup cost .. total cost (arbitrary units)
`rows=12`	Planner's estimate of rows this step produces
`actual time=0.02..3.41`	Real time in milliseconds: first row .. all rows
`actual rows=15`	Rows this step actually produced
`loops=1`	How many times this node ran (important for joins)

The number to watch is the gap between rows=12 (estimated) and actual rows=15 (actual). A small difference is normal. A large one - say, estimated 12 rows but actually 500,000 - means the planner was working with bad statistics and may have chosen the wrong strategy entirely.

Common node types

Node	What it means
`Seq Scan`	Reading the entire table row by row
`Index Scan`	Using a B-tree index to find rows
`Bitmap Heap Scan`	Using an index to collect row locations, then fetching in batches
`Nested Loop`	For each outer row, scan the inner table (fast with indexes; slow without)
`Hash Join`	Build a hash table from one side, probe with the other (good for large tables)
`Merge Join`	Merge two pre-sorted inputs (fast when both are sorted already)

A Seq Scan on a large table is usually the first thing to look for in a slow query. It means no index was used - either one does not exist, or the query is written in a way that prevents the planner from using it.

Diagnosing common problems

Seq scan on a large table where you expected an index: First confirm the index exists. Then check whether the query filters directly on the column or on a function of it - WHERE LOWER(email) = ... cannot use an index on email. The filter has to touch the column as-is.

Large row estimate discrepancy: The planner's statistics are stale. Run ANALYZE tablename to refresh them. On tables that change rapidly, autovacuum may not be keeping up.

High loop count on a nested loop: A node showing loops=50000 means the inner side is running 50,000 times. If that inner side is a sequential scan, you have an N+1 at the query level. Adding an index on the join column collapses each of those scans to a fast lookup.

ANALYZE and VACUUM

PostgreSQL tracks statistics about each table to power the planner. ANALYZE refreshes those statistics. VACUUM reclaims space from deleted rows and keeps the table tidy. Autovacuum handles both in the background, but on high-churn tables it can fall behind - and when it does, the planner starts making bad estimates. If you see a big discrepancy between estimated and actual row counts, run ANALYZE manually and try again.

A practical habit

When a query feels slow, do not start by adding indexes at random or rewriting it from memory. Run EXPLAIN ANALYZE first. The plan shows you the actual bottleneck, the row counts at each step, and whether the planner's assumptions matched reality. A few seconds reading the plan will save you from hours of guessing.