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..totalis the planner’s estimate in arbitrary units;rows=is how many rows it expects.actual time=androws=after it are what happened. A big gap between expected and actual rows means bad statistics, and often a bad plan.Rows Removed by Filtershows 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
| Type | Good for |
|---|---|
| B-tree (default) | Equality, ranges, sorting: almost everything |
| GIN | Values that contain many items: jsonb, arrays, full-text, trigrams |
| GiST | Ranges, geometry, nearest-neighbour, exclusion constraints |
| BRIN | Huge tables whose values follow the physical order, such as append-only logs by time |
| Hash | Equality 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
| Task | SQL |
|---|---|
| See the plan | EXPLAIN query; |
| Run and measure | EXPLAIN (ANALYZE, BUFFERS) query; |
| Refresh statistics | ANALYZE table; |
| Basic index | CREATE INDEX ON t (col); |
| Filter + sort | CREATE INDEX ON t (a, b DESC); |
| Only some rows | CREATE INDEX ON t (a) WHERE cond; |
| Expression | CREATE INDEX ON t (lower(col)); |
| Covering | CREATE INDEX ON t (a) INCLUDE (b); |
| Substring search | CREATE INDEX ON t USING gin (col gin_trgm_ops); |
| Without blocking writes | CREATE INDEX CONCURRENTLY ... |
| Usage statistics | pg_stat_user_indexes |
Exercise
EXPLAIN ANALYZE shows rows=5 estimated but rows=480000 actual on a scan. What should you try first?
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?
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?
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
CONCURRENTLYin 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.