PostgreSQL Basics #5 Reading EXPLAIN: Diagnosing Queries with Execution Plans

Infrastructure PostgreSQL Database
4 min read

In chapter 4 we deferred a question: “whether an index is used — and if not, why — is confirmed with the execution plan.” This chapter is that tool. EXPLAIN shows how PostgreSQL will execute (or did execute) a query, and it converts nearly every argument about query performance from speculation into fact.

EXPLAIN vs EXPLAIN ANALYZE #

EXPLAIN and EXPLAIN ANALYZE 
-- Look at the plan only (does not execute)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Actually execute and show measurements alongside
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

EXPLAIN shows only the planner’s expectations; EXPLAIN ANALYZE actually runs the query and shows expectations and measurements side by side. Diagnosis almost always needs ANALYZE. Because it executes the query, using it on an UPDATE or DELETE changes data. When diagnosing write queries, the safe habit is wrapping them: BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.

The plan tree: read from the innermost (deepest-indented) node #

Example plan output 
Nested Loop  (cost=0.57..205.31 rows=50 width=52) (actual time=0.041..0.318 rows=48 loops=1)
  ->  Index Scan using users_pkey on users u  (cost=0.29..8.30 rows=1 width=24)
        (actual time=0.019..0.020 rows=1 loops=1)
        Index Cond: (id = 42)
  ->  Index Scan using idx_orders_user_id on orders o  (cost=0.29..196.51 rows=50 width=36)
        (actual time=0.018..0.284 rows=48 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.210 ms
Execution Time: 0.361 ms

An execution plan is a tree, and the most deeply indented node runs first, feeding results outward. The example reads as: “found 1 row in users via the PK index, found 48 rows in orders via the user_id index, joined them with a Nested Loop.” The two parenthesized pairs on each node are the crux. The first, cost=..., rows=..., is the planner’s expectation; the second, actual time=..., rows=..., is the measurement. Cost is a relative unit, not milliseconds, so use it less for absolute values and more for “which node eats most of the total.”

The four scan nodes #

NodeMeaningSignal
Seq ScanReads the whole table in orderSuspicious on a big table with a narrowing condition (revisit chapter 4’s “reasons it’s not used”)
Index ScanFinds locations via the index, fetches rows from the tableThe normal pattern for small lookups
Index Only ScanAnswers from the index alone (skips the table)The fastest form. Covering indexes come in the practice series
Bitmap Heap ScanCollects candidate locations from the index, reads the table in one passThe normal pattern for mid-sized results (a compromise between Index Scan and Seq Scan)

A Seq Scan by itself is not a crime. As chapter 4 showed, when the condition doesn’t narrow rows enough, a Seq Scan is optimal. The problem case is “a query fetching dozens of rows out of millions, yet a Seq Scan” — then you go back to chapter 4’s checklist (leading column, column transforms, type mismatch).

The single most important comparison: estimated rows vs actual rows #

If you can check only one thing when reading a plan, it’s this: find the node where estimated rows and actual rows diverge by orders of magnitude. The planner picks join methods and scan methods based on statistics, so if “1 row expected” turns out to be 100,000 rows, every choice below that node stands on a false premise. The first remedy is refreshing statistics.

Refresh statistics 
ANALYZE orders;  -- recollect statistics (autovacuum normally handles this, but do it manually right after bulk loads)

A sudden performance drop right after a bulk INSERT or a migration is very often stale statistics. If estimates stay wrong after that, you’re into deeper territory such as strongly correlated column combinations — we pick that up in the practice series’ performance diagnostics.

BUFFERS: the habit of watching read volume #

BUFFERS option 
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

The BUFFERS option shows how many blocks each node read. shared hit is from memory (cache), read is from disk. When the same query is fast one day and slow the next, the plan may not have changed at all — the cache hit ratio did, and these numbers are what tell those cases apart. Reading volume alongside time raises the resolution of “why is this slow” by a level.

In production you run this diagnosis not query by query but as a routine — “find what’s slow, then dig in with EXPLAIN” — and the front half of that (pg_stat_statements) is a practice-series topic.

Summary #

  • EXPLAIN is expectation, EXPLAIN ANALYZE is measurement. Diagnosis defaults to ANALYZE, with write queries wrapped in BEGIN/ROLLBACK.
  • Read the plan tree from the deepest indentation outward. Cost is a relative unit — ask “which node eats the most.”
  • A Seq Scan can be the result of judgment. Only “a narrowing query yet a Seq Scan” sends you back to chapter 4’s checklist.
  • If you check one thing, check the order-of-magnitude gap between estimated and actual rows. When it’s large, refresh statistics with ANALYZE first.
  • BUFFERS separates “fast thanks to cache” from “a good plan.” Next chapter: transactions and MVCC.
X