Lab: BRIN, partial and hash indexes measured

Before you start

You need about 1.5 GB of free space in a throwaway database. Our numbers: PostgreSQL 18.3 on an Apple Silicon laptop, each query’s second run, with parallel workers disabled so the plans are easy to compare (SET max_parallel_workers_per_gather = 0;).

SELECT setseed(0.5);
CREATE TABLE ev (id bigint, created_at timestamptz, status text, email text);
INSERT INTO ev
SELECT g, timestamptz '2025-01-01' + g * interval '6 seconds',
       CASE WHEN random() < 0.01 THEN 'pending' ELSE 'done' END,
       'user' || (g % 200000) || '@example.com'
FROM generate_series(1, 5000000) g;
CREATE TABLE ev_shuffled AS SELECT * FROM ev ORDER BY random();
VACUUM ANALYZE ev;
VACUUM ANALYZE ev_shuffled;

ev is 365 MB: five million events, one every six seconds, in arrival order. About 1% are pending, and every email appears 25 times. ev_shuffled holds the same rows in random order.

1. BRIN for time ranges

Count one day’s events (14,400 rows) with no index, a B-tree, and a BRIN index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM ev WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02';

CREATE INDEX ev_btree ON ev (created_at);           -- measure, then DROP INDEX ev_btree;
CREATE INDEX ev_brin  ON ev USING brin (created_at);
CREATE INDEX evs_brin ON ev_shuffled USING brin (created_at);

BRIN was a little slower than the B-tree and about 3,400 times smaller. On the shuffled copy it was useless: every block range spanned the whole year, so it read the entire table, more slowly than a plain scan. Left alone, the planner did not use it there. Check SELECT correlation FROM pg_stats WHERE tablename = 'ev' AND attname = 'created_at';: 1.000 here, −0.002 for the shuffled table.

2. Partial index for the rows you want

The work queue needs the newest pending events, 1% of the table:

SELECT id FROM ev WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

CREATE INDEX ev_status_created ON ev (status, created_at);   -- measure, then drop
CREATE INDEX ev_pending ON ev (created_at) WHERE status = 'pending';

Both find 20 rows instantly. The partial index does it while holding entries for only 1% of the table, and costs nothing when the 99% of done events are inserted. It can only serve queries that ask for pending events: WHERE status = 'done' ORDER BY created_at DESC LIMIT 20 could not use it, and took 674 ms.

3. Hash against B-tree

A hash index looks ideal for email = '...': it supports only equality, and goes straight to a bucket.

CREATE INDEX ev_email_btree ON ev (email);               -- measure, then drop
CREATE INDEX ev_email_hash  ON ev USING hash (email);
SELECT count(*) FROM ev WHERE email = 'user4242@example.com';

The B-tree won on both counts. Each email appears 25 times, and B-tree deduplication stores each repeated key once with a list of row pointers. A hash index stores an entry per row, and rows with the same value pile into the same bucket’s overflow pages. The documentation says hash indexes suit unique or nearly unique data. A hash index also cannot help LIKE 'user4242@%', which read the whole table.

The worst case

On a separate one-million-row table, we put a hash index on status, which has only two values:

Two values means two enormous buckets, each a long chain of overflow pages.

Clean up

DROP TABLE ev, ev_shuffled;

Check yourself

1. Why was the BRIN index about 3,400 times smaller than the B-tree on created_at?

   Show answer

   It stores one minimum and maximum per range of blocks instead of one entry per row. For five million rows that is a handful of pages.

   I could explain this without looking

2. When can a query use CREATE INDEX ... WHERE status = 'pending'?

   Show answer

   Only when its own condition guarantees status = 'pending', so that every row it needs is in the index.

   I could explain this without looking

3. Why was the hash index on email larger than the B-tree?

   Show answer

   Each email appears 25 times. The B-tree deduplicated repeated keys into one entry with a list of rows; the hash index kept an entry per row in overflow chains.

   I could explain this without looking