EXPLAIN ANALYZE / explain('executionStats')

What a Query Plan Is

Before running a query, the database's query planner decides how to run it: which index to use (if any), in what order to read tables, whether to sort. That decision is the query plan.

EXPLAIN (PostgreSQL) and explain() (MongoDB) show you the plan. With the analyze or execution-stats option, the database also runs the query and reports what really happened: rows read, time taken.

Think of a GPS route preview. EXPLAIN shows the route it plans to take. EXPLAIN ANALYZE actually drives it and tells you how long each part took.

	PostgreSQL	MongoDB
Plan only (doesn't run the query)	`EXPLAIN`	`explain("queryPlanner")`
Plan + real run stats	`EXPLAIN ANALYZE`	`explain("executionStats")`
Also show memory / disk reads	`EXPLAIN (ANALYZE, BUFFERS)`	`explain("allPlansExecution")` shows rejected plans too

Careful: the analyze versions really run the query. EXPLAIN ANALYZE DELETE ... deletes the rows. Wrap writes in a transaction and roll back.

Running It

We'll check the "My Orders" query: a user's delivered orders, newest first.

EXPLAIN ANALYZE
SELECT id, amount, created_at
FROM orders
WHERE user_id = 42 AND status = 'delivered'
ORDER BY created_at DESC
LIMIT 20;

-- for UPDATE / DELETE: run it, then undo it
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'archived' WHERE user_id = 42;
ROLLBACK;

Reading a PostgreSQL Plan

A plan is a tree of steps (called nodes). Read it from the most indented line outward: the inner steps run first and feed their rows to the outer ones.

Before the composite index (numbers are illustrative):

1Limit  (actual time=812.4..812.4 rows=20 loops=1)
2  ->  Sort  (actual time=812.4..812.4 rows=20 loops=1)
3        Sort Key: created_at DESC
4        ->  Seq Scan on orders  (cost=0.00..208334.00 rows=290 width=40)
5                                (actual time=0.02..790.1 rows=300 loops=1)
6              Filter: ((user_id = 42) AND (status = 'delivered'))
7              Rows Removed by Filter: 9999700
8Execution Time: 812.5 ms

After creating the index on (user_id, status, created_at):

1Limit  (actual time=0.03..0.09 rows=20 loops=1)
2  ->  Index Scan Backward using idx_orders_user_status_date on orders
3                                (actual time=0.03..0.08 rows=20 loops=1)
4        Index Cond: ((user_id = 42) AND (status = 'delivered'))
5Execution Time: 0.1 ms

Backward means it read the index from the end, because we sort newest first and the index is ascending. No Sort step and no thrown-away rows.

What each part means:

Part	Meaning
`Seq Scan`	Full scan: reads every row
`Index Scan`	Uses an index, then fetches each matching row
`Index Only Scan`	Gets everything from the index, never touches the table (a covering index)
`Bitmap Heap Scan`	Collects matching row locations from an index first, then reads them in bulk; common for medium-sized results
`Sort`	Extra sorting step, often avoidable with the right index
`cost=0.00..208334.00`	Planner's estimate in its own units: startup cost..total cost. Only useful for comparing plans
`rows=290` (in the cost part)	How many rows the planner expected
`actual time=0.02..790.1`	Real milliseconds: time to first row..time to last row
`rows=300` (in the actual part)	How many rows really came out
`loops=1`	How many times this step ran. Multiply time and rows by loops for the true total
`Rows Removed by Filter`	Rows read and then thrown away. A big number means a missing or unused index

Estimated vs actual rows. If the planner expected 290 rows and got 300, its statistics are good. If it expected 5 and got 500,000, it probably picked a bad plan. Running ANALYZE orders; refreshes the statistics.

Reading a MongoDB Plan

The most useful part of executionStats:

1BEFORE (no suitable index)
2  executionTimeMillis: 4100
3  totalKeysExamined:   0
4  totalDocsExamined:   10000000
5  nReturned:           20
6  stages:  LIMIT → SORT → COLLSCAN
7 
8AFTER (index { userId: 1, status: 1, createdAt: -1 })
9  executionTimeMillis: 1
10  totalKeysExamined:   20
11  totalDocsExamined:   20
12  nReturned:           20
13  stages:  LIMIT → FETCH → IXSCAN (userId_1_status_1_createdAt_-1)

Stages are nested too: the innermost (inputStage) runs first.

Field / stage	Meaning
`COLLSCAN`	Full collection scan
`IXSCAN`	Walks an index
`FETCH`	Loads full documents after an index scan
`SORT`	In-memory sort, avoidable with an index in the right order
`nReturned`	Documents returned
`totalKeysExamined`	Index entries read
`totalDocsExamined`	Documents read
`executionTimeMillis`	Total time in milliseconds

The key ratio: totalDocsExamined vs nReturned. Ideally close to 1:1. Examining 10,000,000 to return 20 means a missing index. A totalDocsExamined of 0 with an IXSCAN means a covered query: answered from the index alone.

Red Flags Checklist

You see	It usually means	Try
`Seq Scan` / `COLLSCAN` on a big table	No usable index	Add an index on the filter columns
Huge `Rows Removed by Filter` / docs examined ≫ returned	Index doesn't cover all filters	Composite index
`Sort` / `SORT` step	Sort order not served by the index	Add the sort column to the index (ESR order)
Estimated rows far from actual rows	Stale statistics	`ANALYZE table`
Index exists but isn't used	Function on the column, type mismatch, leftmost prefix broken, or low selectivity	Fix the query or the index
Large `loops` on an inner step	A step repeating many times, like a nested loop over many rows	Check the join and its indexes

Interview Angle

How to Explain it in an Interview

The 30-Second Spoken Pitch

"When a query is slow, I don't guess, I look at its plan. In Postgres I run EXPLAIN ANALYZE, which executes the query and shows each step with real timings. I read it from the innermost step outward and look for a Seq Scan on a big table, a large Rows Removed by Filter, an extra Sort step, or estimated rows that are far from actual rows, which points to stale statistics. In MongoDB I use explain with executionStats and compare total documents examined with documents returned. Examining millions to return twenty means I need an index, usually a composite one in the right column order. Then I run the explain again to confirm the plan switched to an index scan. I'm careful with EXPLAIN ANALYZE on writes, since it really runs them, so I wrap those in a transaction and roll back."

Common mistakes:

Running EXPLAIN ANALYZE on an UPDATE or DELETE in production without a rollback.
Treating cost as milliseconds. It's an estimate in arbitrary units.
Testing plans on a tiny local table. The planner may choose a full scan there and an index in production.

Summary

1EXPLAIN / queryPlanner          → the plan, query not run
2EXPLAIN ANALYZE / executionStats → plan + real run stats (really runs it!)
3Read plans                      → innermost step first
4Postgres red flags  → Seq Scan, Rows Removed by Filter, Sort, estimated ≠ actual rows
5Mongo red flags     → COLLSCAN, SORT, docsExamined ≫ nReturned
6Fix                 → index (composite, right order), ANALYZE for stats, then re-check

Most plans you'll fix this way come from queries hitting the wrong index or none at all. Next, a full query debugging walkthrough takes one slow page from symptom to fix, step by step.

Next TopicQuery Debugging Walkthrough