Intermediate

Indexing and Query Optimization

Learn to read EXPLAIN ANALYZE output, build the right index for the right query, and use PostgreSQL 18's new B-tree skip scan and pg_stat_io observability to find and fix real slow queries instead of guessing.

~3h

0 / 7 steps

🚀

Intro

The planner is making a bet — EXPLAIN shows you the bet

Prerequisites: PostgreSQL Fundamentals.

Postgres doesn't execute your SQL literally as written — it converts it into a tree of physical operations (sequential scan, index scan, hash join, sort, and so on) and picks the tree it estimates will be cheapest, based on statistics about your data's size and distribution. EXPLAIN shows you that chosen plan and its cost estimate before running anything; EXPLAIN ANALYZE actually runs the query and shows you real timings and row counts next to the estimates, which is where the useful debugging happens — a plan whose estimated row count is wildly different from its actual row count is almost always your first clue that something's wrong (usually stale statistics, or a query shape the planner can't estimate well).

This project is entirely about building the habit of reaching for EXPLAIN ANALYZE before guessing at an index, rather than after.

🔨

Generate real data and read your first EXPLAIN ANALYZE

🔨

Build a B-tree index and understand index-only scans

🔨

Multicolumn indexes and Postgres 18's skip scan

🔨

Beyond B-tree: GIN and BRIN for the right shape of data

🎯

Secret Mission

Secret Mission: find and fix a slow query using pg_stat_statements

🧹

Wrap Up

Before You Go

Pro

Test what you just learned

Self-testing is one of the best ways to retain new skills. Unlock project quizzes to check your understanding.

0 / 7 complete