## TL;DR
Prefix the query with `EXPLAIN (ANALYZE, BUFFERS)` to see what the database actually did: which steps ran, how many rows each touched, and where the time went. The usual culprits are sequential scans on big tables (missing index), nested loops over large inputs, and row-count misestimates. Read the plan top-down from the slowest node.

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
```

## Use this when
- A query is slow and you dont know why
- You want to check whether an index is being used
- Comparing two rewrites of the same query

## Not for
- "too many connections" or pooling issues
- Deadlocks
- Wrong results (correctness), the plan wont help

## Steps

1. Get the real plan with execution stats:

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT o.* FROM orders o
WHERE o.created_at > '2026-01-01';
```
Expected output: the plan tree with actual time and row counts per node. Without ANALYZE you get estimates only.

2. Look for sequential scans on large tables:

```text
Seq Scan on orders  (cost=...) (actual rows=2000000)
```
Expected finding: a Seq Scan over millions of rows where you expected an index. Thats the first thing to fix.

3. Add the missing index and re-check:

```sql
CREATE INDEX CONCURRENTLY ON orders (created_at);
```
Expected output: the plan flips to an Index Scan or Bitmap Heap Scan, time drops.

4. Compare estimated vs actual rows:

```text
rows=10 (actual rows=500000)
```
Expected finding: a big mismatch means stale statistics. Fix with `ANALYZE orders;` and re-run EXPLAIN.

5. Watch for nested loop joins with a large inner side:

```text
Nested Loop  (actual rows=1000000)
```
Expected finding: nested loops are fine for small inputs, terrible for large ones. The planner usually picks hash or merge joins when stats are fresh; stale stats are the common reason it doesnt.

## Variant phrasings

### how to read postgres explain output
Read inside-out: the most indented nodes run first. `actual time` is per-loop, multiply by loops for the real cost.

### seq scan instead of index scan postgres
Either no usable index exists, the table is tiny (seq scan is genuinely faster), or stats are stale. Check all three in order.

### explain analyze buffers meaning
ANALYZE runs the query for real timings. BUFFERS shows cache hits vs disk reads: high disk reads mean the working set exceeds shared_buffers or the OS cache.

## Why it happens
The planner picks the cheapest plan by its cost model, which depends on table statistics. Stale stats, missing indexes, or skewed data make the model wrong, and the plan it picks can be orders of magnitude slower than the right one. EXPLAIN shows you the model's reasoning and the reality side by side.

## Edge cases
- EXPLAIN ANALYZE actually runs the query: dont use it on INSERT/UPDATE/DELETE in production; wrap in a transaction and roll back, or use plain EXPLAIN.
- `Buffers: shared hit` vs `read`: hits are cache, reads are disk. Slow queries with high reads are IO-bound.
- Paste plans into explain.dalibo.com for a readable visual breakdown.
- Parameterized queries can get a generic plan; EXPLAIN with literal values may differ from production behavior.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_52dmIqS-Q_9DzXL43b0onQ