Read Query Plans with EXPLAIN ANALYZE

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Read Query Plans with EXPLAIN ANALYZE
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

`EXPLAIN` shows an estimated plan without executing the query; `EXPLAIN ANALYZE` executes it and adds actual metrics. Cost is not milliseconds. Large estimated-versus-actual row gaps can reveal stale statistics or data correlation. `BUFFERS` exposes cache and page activity.

Connect it to a real scenario

Compare the published-feed plan before and after its index. Remember that `EXPLAIN ANALYZE` really executes writes, so test modifying statements only in a safe transaction or staging environment.

Cost is not milliseconds
Compare estimated and actual rows
Use `BUFFERS` to inspect I/O

Try the working example

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT tutorial_id, title, published_at
FROM app.tutorials
WHERE is_published = true
ORDER BY published_at DESC, tutorial_id DESC
LIMIT 10;

You should see

The execution plan shows scan type, estimated/actual rows, and buffer usage.

5-minute try-it

Capture a plan before and after adding an index, then describe three changes.

One important caution

Do not assume a sequential scan is wrong on a small table; it can genuinely be cheaper than index access.

PostgreSQL — Using EXPLAIN — PostgreSQL Global Development Group

Easy traps

Do not assume a sequential scan is wrong on a small table; it can genuinely be cheaper than index access.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Capture a plan before and after adding an index, then describe three changes.

You'll know it worked when: The execution plan shows scan type, estimated/actual rows, and buffer usage.