Indexing and Querying JSONB Efficiently in PostgreSQL

A JSONB column with no index is a slow column. Every containment query scans the whole table, parsing JSON on each row. That's fine for a lookup table and a disaster for a million-row events table. The good news is that Postgres can index JSONB, and once you know which index type matches which query, a containment search over millions of documents becomes an instant lookup. The catch is that JSONB indexing has more choices than a plain B-tree, and picking the wrong one means the index sits unused.

This is part 25 of the PostgreSQL Masterclass, following working with JSONB.

GIN: the default JSONB index

As covered in the index-types article, GIN indexes the many items inside a value, which is exactly what a JSONB document is. A GIN index on a JSONB column accelerates containment (@>) and key-existence (?) queries:

CREATE INDEX events_data_gin ON events USING gin (data);

-- both of these can now use the index
SELECT * FROM events WHERE data @> '{"type": "signup"}';
SELECT * FROM events WHERE data ? 'user_id';

This default GIN index supports the full set of JSONB operators: @> (contains), ? (key exists), ?| (any key exists), and ?& (all keys exist). It indexes every key and value in the document, which makes it flexible and somewhat large.

jsonb_path_ops: smaller and faster for containment

If the only thing you query is containment (@>), and you don't need the key-existence operators, there's a better operator class. jsonb_path_ops indexes only the paths-to-values rather than every key and value separately, producing an index that's smaller and faster for containment queries:

CREATE INDEX events_data_path_gin
  ON events USING gin (data jsonb_path_ops);

The tradeoff: jsonb_path_ops supports only @>, not the ? existence operators. Since containment is by far the most common JSONB query, this is often the better default. Choose the plain gin (which uses jsonb_ops) when you need existence checks, and jsonb_path_ops when you only do containment.

Indexing a single field with a B-tree

A whole-document GIN index is the right tool for arbitrary containment. But if you always query one specific field, and especially if you compare it with ranges or sort by it, a targeted expression index on that field is smaller and better:

-- you frequently filter by data->>'status'
CREATE INDEX events_status_idx ON events ((data ->> 'status'));

-- now this uses a plain B-tree
SELECT * FROM events WHERE data ->> 'status' = 'active';

This is just the expression index idea applied to JSONB. For a numeric field you'll compare with ranges, cast it in the index so the comparison is numeric, not text:

CREATE INDEX events_amount_idx ON events (((data ->> 'amount')::numeric));
SELECT * FROM events WHERE (data ->> 'amount')::numeric > 100;

The guideline: GIN for "search anywhere in the document," expression B-tree for "always this one field," and combine with a partial index if you also always filter on the same condition.

Writing queries the index can actually use

An index only helps if the query is written to match it, and JSONB makes it easy to write a query that quietly bypasses the index. Two rules keep you on the fast path.

First, prefer @> containment over chains of ->> for equality, because a GIN index accelerates @> directly:

-- index-friendly
WHERE data @> '{"status": "active"}'

-- needs an expression index on exactly data->>'status', or it scans
WHERE data ->> 'status' = 'active'

Both can be made fast, but @> with a GIN index handles arbitrary fields, while the ->> form needs a dedicated expression index per field. For flexible querying, @> plus GIN is the more general answer.

Second, match the index's expression exactly. An expression index on (data ->> 'status') won't help a query written as data #>> '{status}', even though they return the same thing, because the planner matches on the exact expression. Keep your query and your index expression identical.

Querying nested and array data

JSONB documents often contain arrays, and containment handles those naturally. @> checks whether an array contains an element:

-- rows where tags array contains 'urgent'
SELECT * FROM events WHERE data @> '{"tags": ["urgent"]}';

For richer queries over nested structures, the SQL/JSON path language (jsonb_path_query, @?, @@) added in Postgres 12 lets you express conditions like "any array element with amount greater than 100." These path queries can also use a GIN index with the right operator class, so complex JSON filtering doesn't have to mean a full scan.

Verify with EXPLAIN, always

JSONB indexing is exactly the place where "I added an index and it didn't help" happens most, because a small mismatch between query and index means a silent sequential scan. Run EXPLAIN and confirm you see a Bitmap Index Scan on your GIN index or an Index Scan on your expression index. If you see a Seq Scan, the query and index don't match, and that's the thing to fix, not to add another index.

JSONB gives you schema flexibility, and with the right index it gives it to you without the performance cost that flexibility usually implies. GIN for general containment, jsonb_path_ops when you only do @>, expression indexes for hot single fields, and EXPLAIN to confirm. That's the whole toolkit for fast JSONB.

Next, we cover the other flexible types Postgres offers: arrays, enums, and composite types, and when each beats reaching for JSONB.