Other Index Types

What we're doing: Confirm a Hash index is rejected for a range query that a B-tree on the same column would happily serve.

CREATE INDEX idx_hash_status ON orders USING hash (status);

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Index Scan using idx_hash_status -- equality works fine

EXPLAIN SELECT * FROM orders WHERE status > 'pending';
-- Seq Scan -- the Hash index CANNOT be used for a range comparison,
-- regardless of how the planner would like to use it

3–4

Exactly the one thing a Hash index supports — equality — works as expected.

6–8

A range comparison, something B-tree handles trivially, has no possible plan using this Hash index at all.

-- equality: Index Scan using the hash index
-- range: Seq Scan -- no hash-index plan exists for this query shape

Why this works: This demonstrates the Hash index's real limitation directly: it is not merely less efficient for a range query, it is structurally incapable of answering one at all — there is no plan node that could use a Hash index for anything other than an equality comparison, which is exactly the trade-off that makes B-tree the safer default even for equality-heavy workloads.

What we're doing: Show a nearest-neighbor query using a GiST index, and confirm via EXPLAIN that the index (not a sort over a full scan) is what makes it efficient.

CREATE TABLE places (id SERIAL PRIMARY KEY, name text, location point);
CREATE INDEX idx_location ON places USING gist (location);

EXPLAIN SELECT name FROM places
 ORDER BY location <-> point '(101,456)' LIMIT 10;
-- Limit
--   ->  Index Scan using idx_location on places
--         Order By: (location <-> '(101,456)'::point)

2

A B-tree could not do this -- there's no meaningful "less than" relationship between two 2D points to sort by, only a distance from a reference point.

4–7

The plan shows the GiST index directly driving the ORDER BY ... <-> ... distance sort, not a separate sort step over a full table scan.

-- plan shows "Index Scan using idx_location" with "Order By: (location <-> ...)"
-- -- the index itself is answering the nearest-neighbor question

Why this works: This is the concrete demonstration of what B-tree fundamentally cannot do: there is no total ordering of 2D points that a B-tree could sort by, but GiST's operator class for point data specifically understands distance, which is what lets the planner push the ORDER BY ... <-> ... directly into the index scan instead of computing distances for every row and sorting afterward.

What we're doing: Confirm a GIN index accelerates an array containment query, and show the same query falling back to a sequential scan with no GIN index at all.

EXPLAIN SELECT * FROM products WHERE tags @> ARRAY['sale'];
-- (no index yet) Seq Scan on products
--   Filter: (tags @> '{sale}'::text[])

CREATE INDEX idx_tags ON products USING gin (tags);
EXPLAIN SELECT * FROM products WHERE tags @> ARRAY['sale'];
-- Bitmap Heap Scan on products
--   ->  Bitmap Index Scan on idx_tags
--         Index Cond: (tags @> '{sale}'::text[])

1–3

Without a GIN index, checking array containment requires examining every row's array individually.

5–8

The GIN index's inverted structure lets PostgreSQL jump directly to rows whose array contains 'sale', instead of checking every row.

-- before: Seq Scan, checking every row
-- after: Bitmap Index Scan using idx_tags -- direct lookup via the inverted structure

Why this works: The before/after EXPLAIN comparison is the direct evidence for GIN's actual benefit — without the inverted index, "does this array contain X" has no shortcut and must check every row's array contents individually; the GIN index turns that into a direct lookup by the component value 'sale', exactly the inverted-index mechanism this concept describes.

What we're doing: Compare BRIN's index size dramatically against an equivalent B-tree on the same large, physically-correlated column.

CREATE TABLE event_log (id BIGSERIAL PRIMARY KEY, created_at timestamptz, payload text);
INSERT INTO event_log (created_at, payload)
  SELECT now() - (n || ' seconds')::interval, 'data'
  FROM generate_series(1, 10000000) n;  -- 10 million rows, chronological order

CREATE INDEX idx_btree_created_at ON event_log USING btree (created_at);
SELECT pg_size_pretty(pg_relation_size('idx_btree_created_at'));
-- 214 MB

CREATE INDEX idx_brin_created_at ON event_log USING brin (created_at);
SELECT pg_size_pretty(pg_relation_size('idx_brin_created_at'));
-- 152 kB -- roughly 1400x smaller

1–4

A large, append-only table where created_at closely tracks physical insertion order — exactly BRIN's intended case.

6–7

The B-tree, one entry per row, costs real, substantial storage at this scale.

9–10

BRIN, one summary per page range, costs a tiny fraction of that — while still supporting the same range queries usefully, BECAUSE the correlation holds.

pg_size_pretty
----------------
214 MB

pg_size_pretty
----------------
152 kB

Why this works: This size difference is BRIN's entire value proposition made concrete — at 10 million rows, a B-tree costs a substantial, real amount of disk space, while BRIN, relying on the fact that created_at values are physically clustered by insertion order, achieves useful filtering for a tiny fraction of that cost.

What we're doing: Walk through the decision procedure for a real query, rejecting a habitual B-tree choice in favor of the index type the actual operator requires.

-- query in question:
-- SELECT * FROM products WHERE tags @> ARRAY['sale'];

-- STEP 1: the operator is @> (array containment)
-- STEP 2: which index types support @> on an array? -- GIN (and some GiST cases)
--          -- B-tree does NOT support @> on an array at all
-- STEP 3: workload -- moderate write frequency, containment queries common
--          -- GIN is the appropriate choice; no further alternative needed here

CREATE INDEX idx_tags ON products USING gin (tags);  -- follows directly
                                                       -- from the operator, not habit

3–3

Identifying the actual operator is step one — everything else follows from it.

4–6

Checking which index types even support that operator rules out B-tree immediately, regardless of habit.

9

The final choice is a direct, traceable consequence of the operator the query needs — not a default reached for out of familiarity.

-- the index choice is justified by the operator (@>), not habit

Why this works: Working through operator → supporting index types → workload fit as an explicit sequence, rather than reaching for a familiar default, is what actually prevents the mismatched-index mistakes covered throughout this section (a B-tree that cannot do containment, a Hash index that cannot do range, a BRIN index with no physical correlation to exploit) — each of those mistakes is exactly what skipping this procedure produces.