Other Index Types

Key features

• Hash indexes support only the = operator — no <, <=, >=, >, no BETWEEN, no ORDER BY support
• B-tree already supports equality efficiently, in addition to range/sort — Hash offers no capability B-tree lacks, only a narrower one
• Hash indexes cannot be declared UNIQUE — only B-tree indexes can
• Since PostgreSQL 10, Hash indexes are WAL-logged (crash-safe) and usable in replication, which was not always true historically — worth knowing if reasoning from older material
• The practical guidance is straightforward: default to B-tree even for pure-equality workloads; reach for Hash only with a specific, measured reason (rare in practice)

Command

CREATE INDEX idx_hash_status ON orders USING hash (status);

SELECT * FROM orders WHERE status = 'pending';  -- usable
SELECT * FROM orders WHERE status > 'pending';  -- NOT usable -- Hash has no range support

Key features

• GiST is a general-purpose extensible framework — its actual operators depend entirely on the operator class chosen for the specific data type
• GiST supports geometric data (points, boxes, polygons) with operators like <<, &&, @>, <@, and crucially supports nearest-neighbor queries via ORDER BY ... <-> ...
• SP-GiST supports non-balanced, disk-based partitioning structures — quadtrees, k-d trees, radix trees (tries) — suited to hierarchical or space-partitioned data
• Both frameworks support nearest-neighbor (distance-ordered) searches when the chosen operator class supports it — a capability neither B-tree nor Hash has any equivalent for
• PostGIS (the geospatial extension) builds heavily on GiST for its spatial indexing — a common, real-world example of GiST's use case

Command

CREATE INDEX idx_location ON places USING gist (location);

-- nearest-neighbor query -- a capability unique to GiST/SP-GiST-class indexes
SELECT name FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

Key features

• GIN supports array containment/overlap operators (@>, <@, &&, =) — "does this array contain all of these," "any of these," "overlaps with"
• GIN supports JSONB containment (@>, <@) and key-existence (?, ?|, ?&) operators — querying nested document structure efficiently
• GIN supports full-text search via tsvector/tsquery — indexing individual lexemes (normalized words) for fast "contains this word" matching
• GIN indexes are typically larger than an equivalent B-tree, and per-entry updates can be more expensive — a real write-cost trade-off for the containment-query capability
• GIN never supports index-only scans (a structural limitation, covered in B-Tree Indexes' index-only-scan-requirements concept) — every GIN-satisfied query still visits the heap

Command

CREATE INDEX idx_tags ON products USING gin (tags);              -- array
SELECT * FROM products WHERE tags @> ARRAY['sale'];

CREATE INDEX idx_metadata ON products USING gin (metadata);      -- jsonb
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

CREATE INDEX idx_search ON articles USING gin (to_tsvector('english', body));  -- full-text
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('postgresql');

Key features

• BRIN summarizes a RANGE of pages (not one entry per row) with the min and max value seen in that range
• It supports the same operators as B-tree (<, <=, =, >=, >) but with much coarser precision — it identifies candidate PAGE RANGES, not exact rows
• BRIN indexes are dramatically smaller than an equivalent B-tree — often orders of magnitude, since it stores a handful of bytes per page range instead of an entry per row
• BRIN is effective specifically when the column correlates with physical insertion order — an append-only log or time-series table with an ever-increasing timestamp is the textbook case
• A BRIN index on a column with NO physical correlation (e.g. a randomly-assigned UUID) provides little to no benefit, since min/max per page range would span almost the entire value range regardless of which page range is checked

Command

-- effective: an append-only log, timestamp roughly matches insertion order
CREATE INDEX idx_brin_created_at ON event_log USING brin (created_at);

SELECT * FROM event_log WHERE created_at > now() - interval '1 hour';
-- BRIN quickly skips page ranges that couldn't possibly contain matches

Key features

• Different index types support different operators — pg_indexes / \d on a table, or the index-type-specific operator class tables, tell you definitively which operators a given index type actually supports
• The same query can sometimes be served by more than one index type (e.g. equality by both B-tree and Hash) — in that case, workload considerations (write cost, need for range queries too, uniqueness) decide between them
• A table's size and write pattern matter as much as the operator — GIN and GiST cost more to maintain than B-tree; BRIN trades precision for tiny size, worthwhile mainly at large scale with real physical correlation
• "This is the index type I always use" is not a valid justification on its own — the operator classes actually needed by the query should drive the choice, checked explicitly rather than assumed
• EXPLAIN is the way to confirm, after the fact, whether the chosen index type is actually being used for the query it was intended to help

Command

-- the decision procedure, made explicit:
-- 1. what operator does the query actually use?      (=, range, @>, <->, ...)
-- 2. which index type(s) support that operator?       (check indexes-types docs)
-- 3. of those, which fits the workload?                (write cost, size, correlation)
CREATE INDEX idx_tags ON products USING gin (tags);  -- @> needs GIN, not habit