Lesson 11 intermediate

Indexes and Query Performance

Load 200,000 notes, read query plans with EXPLAIN and EXPLAIN ANALYZE, and make slow queries fast with the right index: B-tree, composite, partial, expression, covering, trigram GIN and BRIN; build indexes without blocking writes, understand what indexes cost, and find the ones you do not need.

With five notes, every query is instant. With five million, a missing index turns a 2 ms query into a 2 second one. In this lesson you give Orbit enough data to feel the difference, learn to read query plans and add the indexes Orbit’s queries need.

Add some volume

generate_series produces rows out of thin air. Insert 200,000 notes spread over the three users, one per minute starting on 1 January 2026:

INSERT INTO notes (user_id, title, body, created_at)
SELECT 1 + g % 3,
       'Generated note ' || g,
       md5(g::text),
       timestamptz '2026-01-01 00:00+00' + g * interval '1 minute'
FROM generate_series(1, 200000) AS g;
ANALYZE notes;
INSERT 0 200000
ANALYZE

ANALYZE refreshes the statistics the planner uses to estimate how many rows each step returns. Autovacuum does it eventually, but run it yourself after a bulk load.

How an index helps

Without an index, the only way to find rows is a sequential scan: read every page of the table and test every row. A B-tree index keeps a sorted copy of one or more columns, with pointers to the rows, so Postgres can jump straight to the matching range, like the index at the back of a book.

B-tree is the default index type and handles =, <, <=, >, >=, BETWEEN, IN, IS NULL, sorting with ORDER BY and prefix searches like LIKE 'abc%' (with the right collation or operator class).

Reading a query plan

EXPLAIN shows the plan the planner chose, without running the query. EXPLAIN ANALYZE runs it and adds what really happened. Ask for one hour of notes:

EXPLAIN ANALYZE
SELECT id, title FROM notes
WHERE created_at >= '2026-03-01 10:00+00' AND created_at < '2026-03-01 11:00+00';

The result has this shape (trimmed here; your costs and timings will differ, and on a machine with several cores the scan may run as a Parallel Seq Scan under a Gather node):

Seq Scan on notes  (cost=… rows=… width=…) (actual time=… rows=60 loops=1)
  Filter: ((created_at >= …) AND (created_at < …))
  Rows Removed by Filter: …
  Buffers: shared hit=…
Planning Time: … ms
Execution Time: … ms

How to read it:

  • Each line starting with a node name (Seq Scan, Index Scan, Hash Join, Sort…) is a step. Indented steps feed their parent.
  • cost=start..total is the planner’s estimate in arbitrary units; rows= is how many rows it expects.
  • actual time= and rows= after it are what happened. A big gap between expected and actual rows means bad statistics, and often a bad plan.
  • Rows Removed by Filter shows work wasted on rows that were read and thrown away. Here almost the whole table was read to return 60 rows.
  • Buffers (shown by default since PostgreSQL 18) counts 8 kB pages found in memory (hit) or read from disk (read).

Your first index

CREATE INDEX notes_created_at_idx ON notes (created_at);

Run the same EXPLAIN ANALYZE again:

Index Scan using notes_created_at_idx on notes  (cost=… rows=… width=…) (actual time=… rows=60 loops=1)
  Index Cond: ((created_at >= …) AND (created_at < …))
  Buffers: shared hit=…

Index Cond means the index itself found the rows: no filtering, a handful of pages instead of the whole table, and an execution time that drops by orders of magnitude.

Tip: Use EXPLAIN (ANALYZE, BUFFERS) on the real query with real parameters. Never guess. Paste large plans into a visualiser such as explain.dalibo.com to spot the slow node.

Index foreign keys

Orbit’s most common query is “this user’s latest notes”:

SELECT id, title FROM notes WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20;

There is no index on user_id, so Postgres reads all notes of the user (or the whole table), sorts them and keeps 20. A composite index serves both the filter and the sort:

CREATE INDEX notes_user_created_idx ON notes (user_id, created_at DESC);

Now the plan is an Index Scan that reads exactly 20 entries in order and stops: no Sort node at all. The same index also speeds up ON DELETE CASCADE from users and joins on user_id, because its first column is user_id.

Column order matters. An index on (user_id, created_at) helps queries that filter on user_id, or on user_id and created_at. It is much less useful for a filter on created_at alone. Put columns compared with = first, then the range or sort column. PostgreSQL 18 can sometimes still use such an index when the first column is not filtered, with a skip scan, but it works best when that first column has few distinct values.

Partial indexes

Pinned notes are shown at the top of the app, and only a few notes are pinned. Index just those rows:

CREATE INDEX notes_pinned_idx ON notes (user_id) WHERE pinned;

The index is tiny and is used by queries whose WHERE includes pinned, such as WHERE user_id = 1 AND pinned. Partial indexes are perfect for “active”, “unprocessed” or “not deleted” rows.

Expression indexes

An index on email cannot help WHERE lower(email) = 'ana@orbit.dev', because the indexed value is not lower(email). Lesson 10 already created a unique index on lower(email), so that query can use it. Any deterministic expression can be indexed: date_trunc('day', created_at), (settings ->> 'theme'), and so on. The query must use exactly the same expression.

Covering indexes and index-only scans

If every column a query needs is in the index, Postgres can answer from the index alone with an Index Only Scan. INCLUDE adds columns to the index’s leaf entries without making them part of the search key:

CREATE INDEX notes_user_created_cover_idx ON notes (user_id, created_at DESC) INCLUDE (title);

Index-only scans rely on the table’s visibility map, which VACUUM maintains (lesson 17). On a freshly loaded table, run VACUUM notes; before measuring.

Searching inside text: trigram indexes

WHERE title ILIKE '%deploy%' starts with a wildcard, so a B-tree is useless. The pg_trgm extension indexes three-letter fragments and makes such searches fast:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX notes_title_trgm_idx ON notes USING gin (title gin_trgm_ops);
EXPLAIN SELECT id, title FROM notes WHERE title ILIKE '%deploy%';

The plan now uses a Bitmap Index Scan on notes_title_trgm_idx. pg_trgm also brings similarity search for typos: WHERE title % 'Deploy cheklist'.

Other index types

TypeGood for
B-tree (default)Equality, ranges, sorting: almost everything
GINValues that contain many items: jsonb, arrays, full-text, trigrams
GiSTRanges, geometry, nearest-neighbour, exclusion constraints
BRINHuge tables whose values follow the physical order, such as append-only logs by time
HashEquality only, rarely better than B-tree

A BRIN index on the created_at of a billion-row log table is a few megabytes instead of tens of gigabytes, as long as rows are inserted roughly in time order.

Building indexes on a live table

A plain CREATE INDEX blocks inserts, updates and deletes on the table until it finishes. On production tables, use:

CREATE INDEX CONCURRENTLY notes_title_idx ON notes (title);
DROP INDEX CONCURRENTLY notes_title_idx;

CONCURRENTLY takes longer and cannot run inside a transaction block, but writes continue. If it fails halfway, it leaves an index marked INVALID: drop it and try again. REINDEX INDEX CONCURRENTLY rebuilds a bloated index the same way.

Indexes are not free

Every index must be updated on every INSERT, on UPDATEs that touch its columns, and it takes disk space and memory. Too many indexes slow down writes and crowd useful data out of the cache. Check which ones are actually used:

SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 after weeks of normal traffic is a candidate for removal, unless it enforces uniqueness or serves queries on a replica (statistics are per server). The opposite signal, tables read mostly by sequential scans, comes from seq_scan and idx_scan in pg_stat_user_tables.

Quick reference

TaskSQL
See the planEXPLAIN query;
Run and measureEXPLAIN (ANALYZE, BUFFERS) query;
Refresh statisticsANALYZE table;
Basic indexCREATE INDEX ON t (col);
Filter + sortCREATE INDEX ON t (a, b DESC);
Only some rowsCREATE INDEX ON t (a) WHERE cond;
ExpressionCREATE INDEX ON t (lower(col));
CoveringCREATE INDEX ON t (a) INCLUDE (b);
Substring searchCREATE INDEX ON t USING gin (col gin_trgm_ops);
Without blocking writesCREATE INDEX CONCURRENTLY ...
Usage statisticspg_stat_user_indexes

Exercise

EXPLAIN ANALYZE shows rows=5 estimated but rows=480000 actual on a scan. What should you try first?

  1. Add more indexes on every column in the query
  2. Run ANALYZE on the table so the planner has current statistics
  3. Restart the server to clear the cache
  4. Rewrite the query as a CTE
Show answer

Answer: B. A large mismatch between estimated and actual rows usually means stale or insufficient statistics. With good estimates the planner can choose a better plan.

Exercise

Which index best serves WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20?

  1. (user_id, created_at DESC)
  2. (created_at DESC, user_id)
  3. Two separate indexes, one on user_id and one on created_at
  4. A GIN index on user_id
Show answer

Answer: A. With user_id first, the index jumps to that user’s entries, which are already sorted by created_at, so Postgres reads exactly 20 entries with no sort step.

Exercise

Why should CREATE INDEX on a busy production table usually be CREATE INDEX CONCURRENTLY?

  1. It builds a smaller index
  2. It is the only way to index text columns
  3. A normal build blocks inserts, updates and deletes on the table until it finishes
  4. It makes queries use the index immediately, before it is complete
Show answer

Answer: C. CONCURRENTLY lets writes continue while the index is built. It takes longer and cannot run in a transaction block, and a failed build leaves an INVALID index to drop.

Key takeaways

  • Use EXPLAIN (ANALYZE, BUFFERS) to see what really happens; compare estimated and actual rows.
  • B-tree indexes serve equality, ranges and sorting; put equality columns first in composite indexes.
  • Index foreign keys and the filter-plus-sort combinations your application uses most.
  • Partial, expression, covering and trigram indexes solve specific problems cheaply.
  • Build and drop indexes CONCURRENTLY in production, and remove indexes that are never used.

FAQ

Why does Postgres ignore my index?

Common reasons: the query returns a large share of the table (a sequential scan is then cheaper), the query wraps the column in a function or a cast, the data types do not match, or the statistics are stale. EXPLAIN and ANALYZE tell you which.

Should I index every column?

No. Index for the queries you actually run, starting with the slowest and most frequent ones (lesson 17 shows how to find them with pg_stat_statements). Each extra index slows down writes.

Does the primary key get an index automatically?

Yes, primary keys and unique constraints are backed by unique B-tree indexes. Foreign key columns are not indexed automatically.

How big can an index get?

Often as big as a sizeable part of the table. Check with \di+ or pg_relation_size. Partial indexes and leaving rarely used columns out keep them small.

Can I force Postgres to use an index?

Not directly; there are no query hints in core Postgres. For diagnosis you can run SET enable_seqscan = off; in your session to see the alternative plan and its cost, then fix the real cause: statistics, the query or the index.

↑ ↓ navigate Enter open Esc close