42. Other index types: Hash, GIN, GiST, BRIN — what each is for

B-tree is the right default for most columns, but Postgres ships several other index types, each built for a data shape B-tree handles poorly. Knowing which problem each one solves is more useful than memorizing syntax — you'll reach for these by recognizing the shape of the problem.

Hash: equality only, nothing else

A hash index supports only = — no range queries, no sorting, no </>. In exchange, an equality lookup on a hash index can be marginally faster than a B-tree's, since there's no tree traversal, just a direct hash-bucket lookup.

CREATE INDEX idx_customer_email_hash ON customer USING hash (email);
EXPLAIN SELECT * FROM customer WHERE email = 'MARY.SMITH@sakilacustomer.org';

In practice: reach for a plain B-tree unless you have measured a specific reason not to. A B-tree supports equality too (it's a strict superset of what hash offers), the performance difference is usually marginal, and a B-tree keeps the door open for range queries on the same column later. Hash indexes are a narrow optimization for a narrow case, not a default.

GIN (Generalized Inverted Index): "does this row contain X"

GIN is built for columns holding multiple values per row — arrays, JSONB (Module 11), and full-text search (Module 11) — where the question is "which rows contain this value," the inverse of a normal index's "which value does this row have."

-- pagila's film.special_features is a real array column, e.g.
-- {"Deleted Scenes","Behind the Scenes"}. A GIN index lets "does this
-- row's array contain X" be answered without scanning every row's array.
CREATE INDEX idx_film_special_features ON film USING gin (special_features);
EXPLAIN SELECT * FROM film WHERE special_features @> ARRAY['Trailers'];

The name "inverted index" is the same concept behind search-engine indexing: instead of "row → its values," GIN stores "value → which rows contain it," which is exactly the direction "contains" queries need. Module 11's JSON and full-text lessons are where GIN actually earns its keep — flagged here so the name is familiar before then.

A realistic wrinkle you'll hit immediately on pagila: film only has 4 distinct special_features values, each present in roughly half the 1000 rows — too common, on too small a table, for the planner to ever prefer the GIN index over a plain sequential scan (the exact same selectivity lesson from the previous lesson's B-tree discussion). To actually see the GIN index get used, you have to make the sequential scan artificially unavailable with a SET enable_... planner toggle — a diagnostic-only trick, never something to leave on in production:

SET enable_seqscan = off;
EXPLAIN SELECT * FROM film WHERE special_features @> ARRAY['Trailers'];
-- Now: Bitmap Heap Scan + Bitmap Index Scan using idx_film_special_features
RESET enable_seqscan;

This is a genuinely realistic situation, not a contrived one: GIN (and every non-default index type in this lesson) pays off at data volumes and selectivities this course's 1000-row demo table can't fully demonstrate on its own — trust the mechanism, and expect real-world tables with millions of rows and rarer values to show the index chosen naturally, without forcing it.

GiST (Generalized Search Tree): flexible, for non-scalar comparisons

GiST supports indexing data types where "equal/less/greater" isn't a simple linear ordering — geometric data, ranges, and (with the btree_gist extension) overlap queries — the kind an EXCLUDE constraint enforces:

-- An EXCLUDE constraint like "no two bookings of the same room may
-- overlap in time" is built on a GiST index under the hood -- EXCLUDE
-- USING gist is literally naming the index type it uses to detect
-- overlaps efficiently.

If you've used PostGIS (geospatial) or range types with overlap checks, you've used GiST. It's the "swiss army knife" index type — more general than GIN or B-tree, at some cost in per-operation speed compared to a purpose-built index for a narrower case.

BRIN (Block Range Index): tiny, for naturally-ordered huge tables

BRIN stores, per block range (a contiguous chunk of physical table storage — not per row), just the min/max values seen in that range. It's dramatically smaller than a B-tree (a BRIN index can be megabytes where an equivalent B-tree would be gigabytes), at the cost of being far less precise — it can only rule out entire block ranges, not pinpoint individual rows.

-- Ideal case: a huge, append-only table where rows are inserted roughly
-- in timestamp order (log data, time-series data). rental.rental_date
-- fits this shape reasonably well in pagila.
CREATE INDEX idx_rental_date_brin ON rental USING brin (rental_date);

(In full pagila distributions the payment table ships partitioned by date — partitioning is the other standard tool for this same "huge, naturally time-ordered table" problem. Partitioning and BRIN solve overlapping problems in different ways, worth knowing both exist, though partitioning itself is beyond this course's scope. It's also why this lesson's BRIN example uses rental instead — creating a plain index directly on a partitioned table's parent doesn't behave quite the same as on an ordinary table.)

BRIN is a correlation-dependent optimization: it only helps when the indexed column's values roughly correlate with physical storage order — exactly true for an append-only log table where new rows are always "the latest timestamp," and completely useless for a column with no relationship to insertion order. This is a narrow, specific optimization for huge tables where a B-tree's size would itself become a problem — not a general-purpose choice.

Its imprecision shows up directly in EXPLAIN ANALYZE as a Bitmap Heap Scan with a Recheck Cond and Heap Blocks: lossy=N — "lossy" means BRIN could only narrow the search down to whole block ranges, not individual rows, so Postgres has to re-check the actual condition against every row in those ranges (visible as Rows Removed by Index Recheck in the query plan). A B-tree never needs this recheck step, since it points at exact rows — this recheck cost is precisely BRIN's precision/size tradeoff made visible in the plan.

Choosing among them: a decision guide

Check yourself

1. Why would you almost never choose a hash index over a B-tree, given that B-tree already supports equality lookups?
2. What kind of question is GIN specifically built to answer quickly, that a B-tree cannot?
3. Why does BRIN's usefulness depend on the indexed column correlating with physical row storage order?