How do you use EXPLAIN and EXPLAIN ANALYZE?

Question

Accepted Answer

**`EXPLAIN`** shows PostgreSQL's **query execution plan** — how it will run a query (which scans, joins, indexes). **`EXPLAIN ANALYZE`** actually runs the query and shows real timing and row counts. These are the primary tools for diagnosing slow queries.

## EXPLAIN — the plan (without running)

```sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
-- shows the PLAN: scan type, estimated cost, estimated rows — but doesn't run it
```

## EXPLAIN ANALYZE — the plan with REAL execution data

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- ACTUALLY runs it → shows REAL time, ACTUAL rows, and where time was spent
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- also shows buffer/cache usage
```

`EXPLAIN ANALYZE` is more useful because it shows *actual* performance (real timing, real row counts) — revealing where the query really spends time, not just estimates.

## Reading the plan: the key things to look for

```text
SCAN TYPES (most important):
  Seq Scan      → FULL TABLE SCAN (reads every row) — slow on big tables, often the problem
  Index Scan    → uses an index to find rows — fast
  Index Only Scan → reads only the index (doesn't touch the table) — fastest
  Bitmap Scan   → for combining indexes / many matches

WATCH FOR:
  ✗ Seq Scan on a large table being filtered → likely a MISSING INDEX
  ✗ Estimated rows VERY different from actual rows → outdated statistics (run ANALYZE)
  ✗ Expensive joins, sorts spilling to disk ("external merge")
  ✗ The node with the highest actual time = the bottleneck
```

## A diagnostic example

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- "Seq Scan on orders ... actual time=120ms" → a full scan, slow

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- "Index Scan using idx_orders_customer ... actual time=0.05ms" → fixed!
```

## Why it matters

`EXPLAIN` and `EXPLAIN ANALYZE` are essential, primary tools for **diagnosing and optimizing slow queries** in PostgreSQL — query performance is critical for applications, and these tools are how you understand *why* a query is slow and verify fixes, so mastering them is important senior-level knowledge for database performance work.

The fundamental value is **visibility into the execution plan**: rather than guessing why a query is slow, `EXPLAIN ANALYZE` (which runs the query and shows real timing and row counts) reveals exactly how Postgres executes it and where time is spent.

The most important things to recognize are the **scan types** — especially identifying a **`Seq Scan` (full table scan)** on a large filtered table, which usually indicates a **missing index** (the most common and impactful query performance problem), versus a fast **`Index Scan`**.

Understanding how to read the plan (scan types, comparing estimated vs actual rows to spot outdated statistics, identifying the most expensive node as the bottleneck, watching for disk-spilling sorts) is the key skill for query optimization.

The diagnostic workflow — run `EXPLAIN ANALYZE`, identify the bottleneck (often a Seq Scan), apply the fix (usually adding an index), and re-run to verify — is the standard, measurement-driven approach to query optimization.

Since slow queries are a leading cause of poor database performance, and since `EXPLAIN`/`EXPLAIN ANALYZE` are *the* tools for diagnosing them (revealing missing indexes, bad plans, and bottlenecks) and verifying optimizations, mastering them is important, high-value knowledge for PostgreSQL performance — it's how you systematically find and fix slow queries rather than guessing, a critical skill for performant databases and a common topic for database optimization work.