EXPLAIN and EXPLAIN ANALYZE

Objectives

By the end of this lesson, you should be able to:

• See how PostgreSQL plans to run a query with EXPLAIN
• See a query’s actual execution with EXPLAIN ANALYZE
• Explain why PostgreSQL sometimes skips an available index

💡 Why this matters: Creating an index (previous lesson) doesn’t guarantee PostgreSQL actually uses it, the query planner decides that for each query, based on real cost estimates. EXPLAIN shows that decision directly, instead of guessing.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table (9 rows) from earlier lessons, with idx_employees_department (from the previous lesson) in place.

EXPLAIN

EXPLAIN SELECT * FROM employees WHERE last_name = 'Castillo';

 Seq Scan on employees  (cost=0.00..1.12 rows=1 width=696)
   Filter: ((last_name)::text = 'Castillo'::text)

EXPLAIN shows the execution plan, what PostgreSQL intends to do, without actually running the query. Seq Scan on employees means a sequential scan, checking every row in the table one by one. cost=0.00..1.12 is an internal cost estimate (not a real time unit, just a relative number the planner uses to compare plans), rows=1 is the planner’s estimate of how many rows will match.

Why a Sequential Scan, Not the Index

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

 Seq Scan on employees  (cost=0.00..1.12 rows=5 width=696) (actual time=0.042..0.054 rows=5.00 loops=1)
   Filter: ((department)::text = 'Engineering'::text)
   Rows Removed by Filter: 5
   Buffers: shared hit=1
 Planning Time: 0.126 ms
 Execution Time: 0.139 ms

Even though idx_employees_department exists on this exact column, PostgreSQL still chose a sequential scan over using it. This isn’t a bug, on a table this small (9 rows), reading the whole table directly is actually faster than the overhead of consulting an index and then looking up each matching row separately. The query planner makes this choice based on real cost estimates, table size, and data distribution, not simply “an index exists, so use it.” On a table with millions of rows, the same query would very likely use idx_employees_department instead, an Index Scan line would appear in the plan.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN alone shows the planned approach without running anything, EXPLAIN ANALYZE actually executes the query and shows the real measured time and row counts alongside the plan (actual time=0.042..0.054, Execution Time: 0.139 ms). EXPLAIN ANALYZE is more informative but has a real side effect worth remembering: it genuinely runs the query, including any writes, running EXPLAIN ANALYZE on a DELETE or UPDATE actually deletes or updates the rows.

Reading the Plan

Key details worth watching for in any plan: Seq Scan (reads the whole table) versus Index Scan (uses an index to jump directly to matching rows), the rows= estimate versus the actual ... rows= count (a large mismatch between them can signal outdated table statistics), and Rows Removed by Filter (how many rows were read but didn’t match, a hint that an index on the filtered column might help at a larger scale).

Try It

1. Run EXPLAIN SELECT * FROM employees; (no WHERE clause) and read the plan, explain why it’s necessarily a sequential scan.
2. Run EXPLAIN ANALYZE on a query filtering employees by salary > 70000, and note the actual execution time.
3. Compare the rows= estimate to the actual ... rows= count in your result from question 2, and note whether they match.
4. Explain, in your own words, why running EXPLAIN ANALYZE on a DELETE statement is riskier than running plain EXPLAIN on it.

Recap

• EXPLAIN shows PostgreSQL’s planned execution strategy without running the query.
• EXPLAIN ANALYZE actually runs the query, showing real measured time and row counts alongside the plan, but with real side effects for write statements.
• The query planner chooses between a sequential scan and using an available index based on cost estimates, not automatically, a small table often favors a sequential scan even with a relevant index present.

Next lesson: query optimization basics, reading an execution plan to spot a real problem.