Query Plans & Indexes

Published on: August 13, 2026

Most important commands to remember

EXPLAIN (ANALYZE, BUFFERS) — execute the SELECT and inspect its work.
ANALYZE — refresh planner statistics for the test table.
CREATE INDEX — add an access path for the chosen column.

Commands and flags

Command or syntax	Meaning
`psql -X -d lab`	Open the test database without startup customizations.
`CREATE TEMP TABLE … AS SELECT`	Build a session-local table from query results.
`generate_series(1, 10000)`	Generate ten thousand integer rows, including both endpoints.
`repeat('x', 100)` / `AS`	Create a 100-character payload / name output columns.
`ANALYZE lab_plan`	Collect statistics; this standalone command does not display a plan.
`EXPLAIN (ANALYZE, BUFFERS)`	Run the query and report actual timing, row counts, and buffer activity.
`WHERE id = 5000`	Select one of the generated rows.
`CREATE INDEX ON lab_plan (id)`	Create a default B-tree index on the ID column.
`;` / `\q`	End an SQL statement / close psql and remove temporary objects.

The concepts that matter

1. A query states a result, the plan chooses the work

SQL describes the data you want. The optimizer chooses an execution plan: scans, joins, filters, sorts, and other operations that can produce it.

Two equivalent-looking queries can produce different plans, and the same query can change plans as data or statistics change. Read the plan as a tree of operations rather than treating its first line as a complete explanation of performance.

2. Estimated cost is not elapsed milliseconds

Planner costs are relative estimates used to compare alternatives. Estimated row counts and widths help determine how much work an operation might require. They are not observed execution times.

With EXPLAIN ANALYZE, actual times and row counts let you compare estimates with execution. Large row-count errors can explain an unsuitable join or scan choice. Where a node runs multiple times, understand its loop count before interpreting per-loop figures as totals.

3. An index trades maintenance for a useful access path

An index stores additional structure that can locate qualifying rows without scanning every table row. It is especially useful when the predicate selects a small part of a large table and matches an available index strategy.

Indexes consume storage and must be maintained on writes. A sequential scan can be the right choice for a small table or a query reading much of the data. An unused index is not automatically evidence that the optimizer is broken.

4. Measurements need context

Buffer information helps distinguish logical data access from simple row output. A buffer hit means the requested block was already in the relevant PostgreSQL buffer cache; it is not an application response-cache hit.

EXPLAIN ANALYZE actually executes the statement and adds measurement overhead. For modifying statements, execution changes data. Warm caches, competing work, and tiny test datasets can distort timing comparisons. Use the example to understand the access path, not to claim a production speedup.

One small example

Optional: open psql, then run the SQL in order. All data and the index belong to a temporary table in this session. Stop on any setup error before comparing plans.

psql -X -d lab

CREATE TEMP TABLE lab_plan AS
SELECT n AS id, repeat('x', 100) AS payload
FROM generate_series(1, 10000) AS n;
ANALYZE lab_plan;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_plan WHERE id = 5000;
CREATE INDEX ON lab_plan (id);
ANALYZE lab_plan;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_plan WHERE id = 5000;

\q

Compare the scan nodes before and after index creation, the estimated and actual matching rows, and buffer activity. The first plan has no index available. The second can choose one, but its exact choice is not guaranteed.

Because this is a temporary table, data and index accesses can appear under local buffers rather than shared. temp buffer activity instead refers to temporary working files. Actual times are in milliseconds; do not invent a percentage improvement from unmeasured output. Quit with the final command to remove the fixture automatically.

Keep this idea: A useful plan explains how many rows and blocks the database must touch; an index helps only when it reduces the relevant work.