how to fix a slow SQL query: EXPLAIN basics

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.

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:

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.

1. Look for sequential scans on large tables:

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.

1. Add the missing index and re-check:

CREATE INDEX CONCURRENTLY ON orders (created_at);

Expected output: the plan flips to an Index Scan or Bitmap Heap Scan, time drops.

1. Compare estimated vs actual rows:

rows=10 (actual rows=500000)

Expected finding: a big mismatch means stale statistics. Fix with ANALYZE orders; and re-run EXPLAIN.

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

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/pst52dmIqS-Q9DzXL43b0onQ