Indexes, and asking the planner

What an index buys

Without an index, answering "which readings are 321" means reading every row — a sequential scan. An index is a sorted structure on one or more columns, kept up to date as rows change, which lets the database jump to the values it wants. CREATE INDEX readings_value_idx ON readings (value); builds one on that column, and every future query that filters on value may use it.

The word is "may". An index costs time and space on every write, and the planner weighs that against the rows it would save. For a value that appears in a third of the table, reading the whole table in order is genuinely cheaper than a third of the rows scattered through an index — so PostgreSQL ignores the index, and it is right to.

Reading a plan

EXPLAIN shows the plan without running the query; EXPLAIN ANALYZE runs it and reports actual times and row counts. Read a plan from the inside out — the most indented line runs first — and look for the node type: Seq Scan reads the table, Index Scan follows an index, Bitmap Index Scan collects matches in bulk first, and Aggregate stacks on top of whatever produced the rows.

The numbers beside each node are estimates of cost and rows, and EXPLAIN ANALYZE adds the reality next to them: actual time, actual rows. When estimate and reality differ by a lot, the planner is working from stale statistics — ANALYZE refreshes them, and it is the first thing to try on a query that suddenly chose a terrible plan.

How to index well

Index the columns you filter and join on, in the order the query uses them: a composite index on (sensor, taken_at) serves a question that filters by sensor and orders by time, while an index on (taken_at, sensor) serves a different one. A UNIQUE constraint is an index that also enforces a rule — which is why lookups by primary key never need a second one.

The honest summary is a loop rather than a rule: write the query, read the plan, add an index if the plan says a scan is expensive, and read the plan again to confirm. Guessing which index a query needs is how people end up with seven indexes and a slow writer.

What is inside the index

A B-tree index is a set of pages in key order, each leaf entry holding the key and a pointer to the row. It stays shallow because each page points to many others, so a lookup takes a handful of reads instead of a table walk. Every write to an indexed column updates it too.

An index-only scan is possible when the query needs only columns the index carries — and when the visibility map says the pages are clean enough not to visit the table. That is why vacuuming matters even for reads.

The planner works from estimates

ANALYZE samples a table and records how many distinct values a column has and how they are spread. The planner combines that with cost constants — a sequential page read is assumed cheaper than a random one — to estimate how many rows a filter will keep. A bad estimate produces a bad plan even when the index is perfect.

The estimates explain a rule that looks arbitrary: WHERE lower(email) = '[email protected]' cannot use an index on email, because the index is sorted by the raw value and the query asks about a derived one. An index on lower(email) matches the expression. The query and the index have to agree on what is compared.

Try these next

• Compare EXPLAIN with EXPLAIN ANALYZE and find the estimated versus actual rows.
• Create two indexes in opposite column orders; see which query each serves.
• Index lower(actor) and compare the plan before and after.

The same question, planned twice

-- Before the index: the table has to be read.
EXPLAIN ANALYZE SELECT count(*) FROM readings WHERE value = 321;

CREATE INDEX readings_value_idx ON readings (value);

-- After: the same question, a different plan.
EXPLAIN ANALYZE SELECT count(*) FROM readings WHERE value = 321;

-- An index is not always the answer — this filter matches
-- a fifth of the rows, and a scan beats it.
EXPLAIN SELECT count(*) FROM readings WHERE sensor = 'sensor-3';