EXPLAIN ANALYZE Postgres: Read Query Plans Like a Pro
Learn how explain analyze in Postgres works: read query plans, spot Seq Scans, decode costs and Buffers, and find out why your index is ignored.
"My query is slow" is not a diagnosis — it's a symptom. The diagnosis lives in the query plan, and EXPLAIN ANALYZE is how you read it. Learning to use explain analyze in Postgres turns slow-query debugging from guessing ("maybe add an index?") into reading ("the planner chose a Seq Scan because the index can't be used with this filter"). This tutorial builds a 100k-row table, runs real plans against it, and teaches you to read every part: scan nodes, join strategies, costs, Buffers, and the classic "why is my index ignored" traps. What you'll learn The difference between EXPLAIN and EXPLAIN ANALYZE How to read plan nodes: Seq Scan, Index Scan, Bitmap Heap Scan What cost= , rows= , and actual time= really mean Hash Join vs Nested Loop: how Postgres picks a join strategy What Buffers: shared hit/read tells you about I/O Why Postgres ignores an existing index (4 classic reasons) Finding your slowest queries with pg_stat_statements 🎬 Watch: slow queries fixed with EXPLAIN ANALYZE Prefer video? This demo profiles real query plans with EXPLAIN ANALYZE, compares sequential vs index scans on a multi-million-row table, and takes a 4-second query down to milliseconds — the same workflow this guide teaches. ⏱ 9:04 · Build with Drew — click to watch on YouTube. Step 0 — Build a table worth planning about Query plans are boring on tiny tables — the planner will Seq Scan anything small no matter what. Generate real volume: CREATE TABLE products ( product_id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price NUMERIC(10,2) NOT NULL ); INSERT INTO products (name, category, price) SELECT 'Product ' || g::text, (ARRAY['books', 'electronics', 'toys'])[1 + (g % 3)], (g % 100) + 0.99 FROM generate_series(1, 100000) g;…