Learn to diagnose and speed up slow PostgreSQL queries. You will understand how B-tree and other index types work, read EXPLAIN and EXPLAIN ANALYZE plans to find the real bottleneck, and design composite, partial and covering indexes while keeping tables healthy with VACUUM and ANALYZE.
Watch the free preview
Why queries get slow — the sequential scan — free to watch, no account needed.
What you'll learn
- Diagnose and speed up slow queries using indexes and query plans
- Explain how a B-tree index works and why it turns a scan into a lookup
- Choose the right index type — B-tree, Hash, GiST, GIN or BRIN — for a workload
- Read EXPLAIN and EXPLAIN ANALYZE output to locate the bottleneck in a plan
- Design composite, partial and covering indexes and keep them healthy with VACUUM and ANALYZE
Syllabus
How Indexes Work
Why queries get slow — the sequential scanFree preview
The B-tree — how an index turns a scan into a lookup
Index Types and Creating Them
Beyond B-tree — Hash, GiST, GIN and BRIN
Creating indexes safely in production
Reading Query Plans with EXPLAIN
EXPLAIN — reading the plan the planner chose
EXPLAIN ANALYZE — measuring what actually happened
Advanced Indexing and Maintenance
Composite, partial and covering indexes
Keeping performance healthy — VACUUM and ANALYZE
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10