Module 7 · Performance and indexing
Sign in to spin up your own Postgres sandbox and run the queries for this lesson.
A B-tree is the right index for =, <, >, BETWEEN, and ORDER BY on a scalar column — which covers most queries. But B-trees index one comparable value per row, and some columns don't fit that shape: a jsonb blob, an array of tags, a full-text document, a huge append-only log. Postgres ships several other access methods, plus a few tricks that reshape any index. This lesson surveys them, each with a query it wins.
The seed is a docs table of five million rows: a tags array, a jsonb meta column, a lopsided status, mixed-case email, an owner_id, and a created_at inserted in time order. Big enough that each index's win shows up in the plan and in the timing. Take a look:
SELECT id, owner_id, status, email, tags, meta, created_at FROM docs ORDER BY id LIMIT 5;
A B-tree can't answer "which docs have the tag gin" — the whole array is one opaque value to it. GIN (Generalized Inverted Index) solves the "many values per row" problem the way a book index does: it stores each element (each tag, each jsonb key, each lexeme) once and points back to the rows containing it.
Index the tags array and ask for containment:
CREATE INDEX docs_tags_gin ON docs USING gin (tags);
EXPLAIN ANALYZE SELECT id FROM docs WHERE tags @> ARRAY['gin'];
@> ("contains") is one of the array operators GIN accelerates, along with \<@ ("is contained by") and && ("overlaps"). GIN works the same on jsonb — index meta and query by containment or key existence:
CREATE INDEX docs_meta_gin ON docs USING gin (meta);
EXPLAIN ANALYZE SELECT id FROM docs WHERE meta @> '{"kind":"report"}';
EXPLAIN ANALYZE SELECT id FROM docs WHERE meta ? 'pinned';
The same idea backs full-text search: a tsvector is "many lexemes per row", and a GIN index on it accelerates the @@ match operator. That's the standard way to make to_tsvector(...) @@ to_tsquery(...) fast.
The cost of the inverted index is real: GIN is slower to update (every element must be posted) and larger and slower to build than a B-tree. Great for read-heavy containment queries; think twice on write-hot columns.
GiST (Generalized Search Tree) is a framework for indexing data where "does this overlap / contain / is near" matters more than "less-than". Its headline uses:
int4range, tstzrange, etc. and GiST accelerates the overlap operator && — "which reservations collide with this window".ORDER BY location <-> point('(3,4)') LIMIT 5 becomes an index scan instead of sorting the whole table.EXCLUDE USING gist (room WITH =, during WITH &&) — "no two bookings for the same room with overlapping times" — is enforced by a GiST index.A range-overlap example (built inline so the seed stays lean):
CREATE TABLE bookings (
room int,
during tstzrange
);
CREATE INDEX bookings_during_gist ON bookings USING gist (during);
-- accelerated by the GiST index:
SELECT * FROM bookings WHERE during && tstzrange(now(), now() + interval '1 hour');
Where GIN indexes elements, GiST indexes bounding shapes — that's why it, not GIN, is the one that does distance ordering and overlap.
BRIN (Block Range Index) doesn't store a pointer per row. It stores a summary — the min and max value — for each block range (a group of physical pages). To answer a range query it skips any block range whose min/max can't match. That makes it minuscule (kilobytes where a B-tree would be megabytes) but coarse.
BRIN only pays off when the column is naturally correlated with physical order. Our created_at is: rows were inserted in time order, so consecutive rows sit on consecutive pages. That's the classic BRIN case — an append-only timestamp.
CREATE INDEX docs_created_brin ON docs USING brin (created_at);
EXPLAIN ANALYZE SELECT count(*) FROM docs
WHERE created_at >= TIMESTAMPTZ '2024-06-01' AND created_at < TIMESTAMPTZ '2024-07-01';
On a shuffled column BRIN is useless — every block range spans the whole value domain, so nothing gets skipped. On a well-correlated giant table it's a spectacular space-for-precision trade.
Hash indexes support exactly one operator: =. No ranges, no ordering. Since PostgreSQL 10 they're WAL-logged and crash-safe (before that they weren't even replicated), so they're finally usable — but a B-tree also handles = well and everything else, so hash rarely wins. Reach for it only for equality on a large, wide key where the slightly smaller hash index measurably helps — a wide text key like email, looked up by exact match, fits the shape better than a slim integer:
CREATE INDEX docs_email_hash ON docs USING hash (email);
EXPLAIN ANALYZE SELECT id FROM docs WHERE email = 'User42@Example.com';
Default to B-tree; treat hash as a rare, measured optimization.
A partial index carries a WHERE clause and stores only the matching rows. When your queries always target a small slice — status = 'active', or deleted_at IS NULL — there's no reason to index the rest.
Our status is 90% 'archived'. If you only ever query the live 10%, index just that:
CREATE INDEX docs_active ON docs (created_at) WHERE status = 'active';
EXPLAIN ANALYZE SELECT id FROM docs
WHERE status = 'active' AND created_at >= TIMESTAMPTZ '2024-03-01'
ORDER BY created_at;
The planner uses the partial index only when it can prove the query's predicate implies the index's WHERE. The payoff: a fraction of the size, fewer levels to walk, and — because 90% of writes touch archived rows the index ignores — much cheaper to maintain.
An index stores column values as-is, so WHERE lower(email) = '...' can't use a plain index on email — the stored value and the searched value differ. An expression (functional) index indexes the result of an expression instead:
CREATE INDEX docs_email_lower ON docs (lower(email));
EXPLAIN ANALYZE SELECT id FROM docs WHERE lower(email) = 'user42@example.com';
The query's expression must match the index's expression exactly for the planner to use it. This is also how you index case-insensitive lookups, date_trunc('day', created_at), or any derived key. (For substring search like LIKE '%foo%' or fuzzy matching, the usual answer is a trigram GIN index via the pg_trgm extension — out of scope for this sandbox, but worth knowing the shape of.)
Normally an index scan finds matching rows, then visits the table heap to fetch the other columns you SELECT. If the index carries every column the query needs, Postgres can skip the heap entirely — an Index Only Scan. INCLUDE adds non-key "payload" columns to a B-tree for exactly this:
CREATE INDEX docs_owner_covering ON docs (owner_id) INCLUDE (price);
EXPLAIN ANALYZE SELECT owner_id, price FROM docs WHERE owner_id = 42;
Look for Index Only Scan in the plan. INCLUDE columns aren't part of the search key (you can't range-scan on them) — they're just along for the ride so the heap fetch disappears. The cost is a fatter index; include only the payload your hot queries actually read.
A quick decision guide:
=, <, >, BETWEEN, ORDER BY → B-tree (the default; start here).tsvector containment/existence (@>, ?, &&, @@) → GIN.WHERE.lower(email)) → expression index.INCLUDE.tsvector. Fast containment reads (@>, ?, &&, @@), but slower to update and larger to build.&&), spatial data, KNN distance ordering (<->), and exclusion constraints.... WHERE ...) index only the rows you query — smaller, faster, cheaper to maintain.... ON t (lower(email))) let a query on a computed value use an index.... INCLUDE (...)) carry payload columns so a query becomes an Index Only Scan and never touches the heap.Up next: query optimization — statistics, ANALYZE, and common anti-patterns.