Read EXPLAIN ANALYZE

When a query is slow, guessing is expensive. PostgreSQL will tell you exactly what it did, if you ask.

EXPLAIN shows the chosen plan. EXPLAIN ANALYZE runs the statement and adds actual rows and timing. Use BUFFERS on a read to see cache and storage work:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM app.notes
WHERE title = 'Plan the week';

Index Scan using notes_title_idx on notes
    (cost=0.29..8.30 rows=1 width=40)
    (actual time=0.041..0.043 rows=1 loops=1)
  Index Cond: (title = 'Plan the week'::text)
  Buffers: shared hit=3
Planning Time: 0.180 ms
Execution Time: 0.071 ms

Read it in this order. The node type first: Index Scan means the index found the row directly. Then compare estimated rows with actual rows; rows=1 estimated against rows=1 actual means the planner’s statistics match reality. When the two diverge wildly, the plan was chosen on bad information. Then find the largest scan, repeated loop, sort, or buffer count. shared hit=3 means three pages, all already in memory.

When the index is ignored

Here is the trap you will hit first in real code. Wrap the indexed column in a function and the plan changes:

EXPLAIN ANALYZE
SELECT id, title
FROM app.notes
WHERE lower(title) = 'plan the week';

Seq Scan on notes
    (cost=0.00..2041.00 rows=500 width=40)
    (actual time=0.011..14.2 rows=1 loops=1)
  Filter: (lower(title) = 'plan the week'::text)
  Rows Removed by Filter: 99999

Seq Scan plus a big Rows Removed by Filter is the signature: PostgreSQL read the whole table to find one row. The index stores title, not lower(title), so it cannot serve this predicate. Fix it with an expression index on lower(title), or stop transforming the column in the query.

ANALYZE executes the query

EXPLAIN ANALYZE executes writes too. Analyzing an UPDATE really updates. Wrap a test write in a transaction and roll it back:

BEGIN;
EXPLAIN ANALYZE
UPDATE app.notes SET title = 'Plan Monday' WHERE id = 1;
ROLLBACK;

Do this on test data. Functions and sequence changes can have effects that a rollback does not undo. When you only need the plan and not the timings, plain EXPLAIN never executes anything and is always safe.