Indexing jsonb

One question, three ways to ask it

An events table stores each event’s details as a document. To count failed events, all three of these give the same answer:

SELECT count(*) FROM events WHERE payload @> '{"status": "failed"}';   -- containment
SELECT count(*) FROM events WHERE payload ->> 'status' = 'failed';      -- extract and compare
SELECT count(*) FROM events WHERE payload ? 'error';                    -- key exists (only failures have one)

To PostgreSQL they are three different operators, and an index serves an operator, not a meaning.

Three indexes, three queries

We built events with one million rows (195 MB), about 2% of them failed, and tried each kind of index in turn. Second run of each query, PostgreSQL 18.3 on a laptop:

Without an index, every query reads all 24,949 pages of the table. With a matching index, the same counts read under 600.

CREATE INDEX ON events USING gin (payload);                    -- default: @>, ?, ?|, ?&
CREATE INDEX ON events USING gin (payload jsonb_path_ops);     -- @> only, smaller
CREATE INDEX ON events ((payload ->> 'status'));               -- this one expression

Choosing

• Queries search on many different fields? One GIN index on the column. Use jsonb_path_ops if you only ever use @>: it was 26% smaller here and slightly faster.
• A few fields are filtered on all the time? A B-tree expression index for each: smallest and fastest, and it supports ranges and sorting too.
• A field is filtered on in almost every query? That is a hint it should be a real column. The next page is about that decision.

Write your queries to match the index you have. payload @> '{"status": "failed"}' and payload ->> 'status' = 'failed' look interchangeable, but with a GIN index one takes 4 ms and the other 37.

Reproduce the measurements

SELECT setseed(0.7);
CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL);
INSERT INTO events
SELECT g, jsonb_build_object(
  'type', (ARRAY['login','logout','scan','export','invite','sync','webhook','payment','search','upload'])[1 + g % 10],
  'status', CASE WHEN random() < 0.02 THEN 'failed' ELSE 'ok' END,
  'user', jsonb_build_object('id', (random() * 50000)::int, 'plan', (ARRAY['free','pro','team'])[1 + g % 3]),
  'duration_ms', (random() * 2000)::int,
  'tags', jsonb_build_array('v' || (g % 7), 'region-' || (g % 4)))
FROM generate_series(1, 1000000) g;
UPDATE events SET payload = payload || '{"error": "timeout"}' WHERE payload ->> 'status' = 'failed';
VACUUM ANALYZE events;

Then run the three queries with EXPLAIN (ANALYZE, BUFFERS) before and after creating each index, dropping it before the next.

Check yourself

1. A table has a GIN index on payload. Why does WHERE payload ->> 'status' = 'failed' still read the whole table?

   Show answer

   A GIN index on the column supports operators like @> and ?, not ->> followed by =. That comparison needs a B-tree index on the expression payload ->> 'status'.

   I could explain this without looking

2. When would you choose jsonb_path_ops over the default GIN operator class?

   Show answer

   When queries only use containment (@>) and not key-existence operators. The index is smaller (20 MB instead of 27 MB here) and searches are more specific.

   I could explain this without looking

3. Rewrite payload ->> 'status' = 'failed' so a default GIN index on payload can serve it.

   Show answer

   payload @> '{"status": "failed"}'.

   I could explain this without looking