Indexes & EXPLAIN

How PostgreSQL finds rows, reading query plans, and B-tree, partial and expression indexes.

Without an index, finding rows means reading the whole table. An index is a separate structure that maps values to row locations. EXPLAIN shows the plan PostgreSQL chose for a query, which is how you find out whether an index is used.

From a sequential scan to index scans

Before the index: Seq Scan — read every row, keep those matching the filter.

With a B-tree index on node: Bitmap Index Scan finds the matching row locations, Bitmap Heap Scan fetches them.

EXPLAIN ANALYZE actually runs the query: 400 rows matched, stored in 400 different blocks.

The partial index (WHERE temp > 99) only contains hot readings, so it is small; the query can be answered from the index alone (Index Only Scan).

An expression index on upper(node) lets WHERE upper(node) = ... use an index.

Index types and rules of thumb

CREATE INDEX ON telemetry (node); then WHERE lower(node) = 'node_7'

CREATE INDEX ON telemetry (lower(node));

An index on a column cannot be used for a function of that column. Index the expression you filter on.

Adding an index to every column "just in case" Index for real query patterns, and check with EXPLAIN (ANALYZE, BUFFERS)

Unneeded indexes cost write time, memory and disk, and the planner may still not use them.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan with estimates without running the query. EXPLAIN ANALYZE runs it and adds actual row counts and times (so be careful with INSERT/UPDATE/DELETE: wrap them in a transaction you roll back).

What is a partial index?

An index with a WHERE clause that only includes some rows, e.g. CREATE INDEX ON orders (created_at) WHERE status = 'open'. It is smaller and faster for queries with the same condition.