What EXPLAIN and EXPLAIN ANALYZE show

A different layer than a repeated-query log

"ActiveRecord querying: N+1 detection" taught reading this app's log/development.log for a burst of repeated queries: one label appearing dozens of times, identical except for one varying id. That's a pattern across many queries. This course starts one level down, at what a single query's execution plan says when you run it once and read it closely. A log pattern never shows that layer. Neither replaces the other. A burst in the log tells you a query shape ran too many times; a plan tells you whether any one of those runs did its work efficiently.

EXPLAIN: the plan, without running anything

EXPLAIN asks the Postgres query planner what it would do with a SQL statement and prints the tree of operations it chose, without executing any of them. Every number in a plain EXPLAIN is an estimate, built from statistics Postgres keeps about each table, never a measurement. That makes it safe to run against a write, an UPDATE or DELETE, without the write happening.

EXPLAIN ANALYZE: the plan, plus what happened

EXPLAIN ANALYZE plans the query the same way, then executes it, timing each operation and counting the rows each one produced. The output keeps the same tree, cost estimates included, and adds actual time=... and rows=... measurements next to each node. This course uses it throughout, because the point is comparing what the planner expected with what happened, and that comparison needs the query to have run.

One consequence is worth stating plainly: EXPLAIN ANALYZE on an INSERT, UPDATE or DELETE performs the write. Every capture in this course runs a read, never a write, for that reason.

Running it against this app's database

This course captures each plan against this app's development database through bin/rails runner, the same tool "ActiveRecord querying: N+1 detection" used to capture log output:

sql = Catalog::Course.active.order(:position).to_sql
plan = ActiveRecord::Base.connection.select_all("EXPLAIN (ANALYZE, BUFFERS) #{sql}").rows
plan.each { |row| puts row.first }

Catalog::Course.active is this app's scope, scope :active, -> { where(active: true) }. .to_sql turns the ActiveRecord relation into the SQL string Postgres will run, and wrapping it in EXPLAIN (ANALYZE, BUFFERS) adds page-read counts, Buffers: shared hit=N, next to the timing. The development database runs PostgreSQL 18, while production runs the postgres:16 image, and newer versions print a few extra details (fractional rows= counts, for one). The next three lessons run this pattern against three queries this app ships and read the output.

What this course covers

1. A plan's basic anatomy: scan types, cost=, actual time= and rows=, read against a query on this app's courses table.
2. A case where an index gets used and Postgres still adds a separate sort step, and why.
3. Reading a join and an aggregate together, on the cross-domain rank query this app used to run for every profile page, since replaced by precomputed snapshots.
4. A case caught live where the planner's row estimate was wrong, and what fixes it.
5. Prompting an AI tool to read a plan well, building on the "paste, don't paraphrase" discipline from earlier AI courses.
6. Putting the workflow together, from a raw captured plan to a verified conclusion.