jsonb_path_ops vs jsonb_ops for GIN Indexes #

A GIN index on a jsonb column of event payloads is 84 GB — larger than the table. Containment queries using it take 300 ms and return four rows. Rebuilt with the jsonb_path_ops operator class, the same index is 21 GB and the same query takes 9 ms. Rebuilt as a small expression index on the one key the application actually filters by, it is 900 MB and takes 0.1 ms.

GIN’s structure and use cases are introduced in when to use GIN over B-tree. This page is about which GIN index to build on a jsonb column — or whether to build one at all.

The Index Condition #

A GIN index stores keys extracted from each value and, for each key, a posting list of the rows containing it. For jsonb there are two built-in operator classes:

jsonb_ops (the default) extracts every key and every value as separate index entries. That supports the widest set of operators:

jsonb_path_ops extracts one entry per path-to-value, hashed. It supports only @>, @? and @@ — no key-existence operators — but its entries are fewer and its searches more selective: a containment query on {"a": {"b": 1}} matches the hashed path directly instead of intersecting the posting lists of a, b and 1 separately.

Practical consequences:

And the third option: if queries filter on one or two known keys, an expression index on those keys — B-tree on (payload->>'tenant'), or GIN on (payload -> 'tags') — is far smaller than indexing the whole document.

Three ways to index a jsonb column A table comparing the options. jsonb_ops supports containment, key existence and jsonpath, and is the largest. jsonb_path_ops supports containment and jsonpath only, and is smaller and more selective. An expression index on one key is by far the smallest but only serves queries on that key. operators relative size jsonb_ops (default) @>, ?, ?|, ?&, @? largest jsonb_path_ops @>, @?, @@ 2–4× smaller expression index that key only smallest index the whole document only when queries really are unpredictable

Annotated EXPLAIN Evidence #

-- default operator class
CREATE INDEX events_payload_gin ON events USING gin (payload);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE payload @> '{"tenant": "acme", "kind": "signup"}';
Bitmap Heap Scan on events  (actual time=298.4..301.2 rows=4 loops=1)
  Recheck Cond: (payload @> '{"kind": "signup", "tenant": "acme"}'::jsonb)
  Heap Blocks: exact=4
  Buffers: shared hit=48210
  ->  Bitmap Index Scan on events_payload_gin  (actual time=298.1..298.1 rows=4 loops=1)
        Index Cond: (payload @> '{"kind": "signup", "tenant": "acme"}'::jsonb)
        Buffers: shared hit=48206
-- 48k index buffers: posting lists for "tenant", "acme", "kind" and "signup" intersected

With jsonb_path_ops:

CREATE INDEX events_payload_pathops_gin ON events USING gin (payload jsonb_path_ops);
  ->  Bitmap Index Scan on events_payload_pathops_gin  (actual time=8.6..8.6 rows=4 loops=1)
        Index Cond: (payload @> '{"kind": "signup", "tenant": "acme"}'::jsonb)
        Buffers: shared hit=2104
-- one hashed entry per path: far fewer index pages touched

With an expression index on the filtered keys:

CREATE INDEX events_tenant_kind_idx ON events ((payload->>'tenant'), (payload->>'kind'));
ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE payload->>'tenant' = 'acme' AND payload->>'kind' = 'signup';
Index Scan using events_tenant_kind_idx on events  (actual time=0.03..0.04 rows=4 loops=1)
  Index Cond: (((payload ->> 'tenant'::text) = 'acme'::text) AND ((payload ->> 'kind'::text) = 'signup'::text))
  Buffers: shared hit=7
-- also gives the planner real statistics on those two expressions
Buffers per containment query Three plan fragments are annotated. The default GIN index reads 48 thousand index buffers because several posting lists are intersected. The path-ops index reads 2,104 because one hashed path is looked up. The expression index reads seven buffers and also supplies statistics. gin (payload) → 48,206 index buffers posting lists intersected gin (payload jsonb_path_ops) → 2,104 one hashed path per key-value btree on two extracted keys → 7 plus real statistics fewer, more selective entries beat more, less selective ones

Step-by-Step Resolution #

  1. Collect the real query shapes against the JSON column from pg_stat_statements. Count how many use @>, how many use ?, and how many filter on a small fixed set of keys.

  2. If a handful of keys dominate, index those keys with expression indexes, and consider generated columns so the values get ordinary statistics:

    ALTER TABLE events ADD COLUMN tenant text GENERATED ALWAYS AS (payload->>'tenant') STORED;
CREATE INDEX CONCURRENTLY events_tenant_idx ON events (tenant);

  3. If queries are genuinely open-ended containment, use jsonb_path_ops:

    CREATE INDEX CONCURRENTLY events_payload_pathops_gin ON events USING gin (payload jsonb_path_ops);

  4. Keep jsonb_ops only if key-existence operators are used. Check for ?, ?| and ?& in application SQL before dropping it.

  5. Index a sub-document rather than the whole payload when queries always look inside one branch:

    CREATE INDEX CONCURRENTLY events_attrs_gin ON events USING gin ((payload -> 'attrs') jsonb_path_ops);
-- query: WHERE payload -> 'attrs' @> '{"plan": "pro"}'

  6. Add expression statistics for the extracted keys used in filters, as described in expression statistics for computed predicates; GIN containment estimates are otherwise crude.

  7. Re-measure size, build time and query time for each candidate; the differences are large enough to decide on.

Index size on a 210 GB events table The default jsonb_ops GIN index occupies 84 gigabytes. The jsonb_path_ops index occupies 21 gigabytes. A GIN index on one sub-document occupies 6 gigabytes. A B-tree on two extracted keys occupies 0.9 gigabytes. gin (payload) 84 GB gin, jsonb_path_ops 21 GB gin (payload -> 'attrs') 6 GB btree on two keys 0.9 GB smaller indexes also build faster and stay in cache

Before and After #

-- BEFORE: gin (payload) with the default operator class
Bitmap Index Scan on events_payload_gin  Buffers: shared hit=48206        Execution Time: 301.2 ms

-- AFTER: btree on the two keys that queries actually filter by
Index Scan using events_tenant_kind_idx  Buffers: shared hit=7            Execution Time: 0.04 ms

When indexing the whole document is right #

Open-ended search over semi-structured data — an audit log where operators filter by arbitrary attributes, a product catalogue with per-category attributes — is exactly what GIN containment is for. There the alternative is no index at all, and jsonb_path_ops gives the best size and selectivity for @> queries.

Two caveats apply even then. First, selectivity estimates for containment are rough: the planner has limited statistics about which key-value pairs are common, so join and row estimates above a JSON filter are often wrong, and plans above them can be poor. Second, GIN cannot support index-only scans or ordering, so every match costs a heap fetch and any ORDER BY needs a sort.

For data that is really relational but stored as JSON for convenience, the durable fix is to promote the hot keys to columns — generated columns keep them in sync with no application changes — and index those. That gives statistics, index-only scans, ordering and small indexes, while the document remains available for the rare open-ended query.

Common Pitfalls #

Using the default operator class by habit. It is the largest and least selective for containment. Diagnostic signal: a GIN index bigger than the table. Fix: jsonb_path_ops unless key-existence operators are needed.

Dropping jsonb_ops while ? is in use. Those queries lose their index. Diagnostic signal: sequential scans after a rebuild. Fix: check application SQL for ?, ?| and ?& first.

Expecting index-only scans. GIN cannot provide them. Diagnostic signal: heap blocks for every match. Fix: promote hot keys to columns.

Ignoring estimate quality. Containment estimates are crude and mislead joins above them. Diagnostic signal: bad join choices above a JSON filter. Fix: extracted columns with real statistics.

Frequently Asked Questions #

What is the difference between jsonb_ops and jsonb_path_ops? #

jsonb_ops indexes every key and value separately and supports containment, key existence and jsonpath operators. jsonb_path_ops indexes hashed paths to values, supporting containment and jsonpath only, with a smaller and more selective index.

Which jsonb GIN operator class should I use? #

jsonb_path_ops when your queries use containment, which is most applications. Keep jsonb_ops only when you need key-existence operators such as ? or ?|.

Is indexing one key better than indexing the whole jsonb document? #

Usually, when queries filter on a small set of known keys. An expression or generated column index on those keys is far smaller, supports ordering and index-only scans, and gives the planner real statistics.

Up: Specialized Index Types (GIN/GiST)