Index a JSONB Column With GIN

A JSONB column holds whatever the application sends, and sooner or later someone filters on a key inside it. Without an index, every such query reads the whole table. A GIN index on the column fixes that, but only for the operators it supports, and the way you write the filter decides whether the index is used. This page indexes an events table, shows the filter shapes that use the index, and the ones that do not.

Step 1: a table of 20,000 events

Each event has a type, a nested user object and a list of tags.

CREATE TABLE events AS
SELECT g AS event_id,
       jsonb_build_object(
         'type', CASE WHEN g % 50 = 0 THEN 'refund'
                      WHEN g % 3 = 0 THEN 'login'
                      ELSE 'view' END,
         'user', jsonb_build_object('id', (g % 400) + 1,
                                    'plan', CASE WHEN g % 10 = 0 THEN 'pro' ELSE 'free' END),
         'tags', CASE WHEN g % 200 = 0 THEN jsonb_build_array('vip', 'mobile')
                      ELSE jsonb_build_array('web') END) AS payload
FROM generate_series(1, 20000) AS g;
ALTER TABLE events ADD PRIMARY KEY (event_id);
ANALYZE events;
SET max_parallel_workers_per_gather = 0;
SELECT payload FROM events WHERE event_id = 200;

SELECT 20000
ALTER TABLE
ANALYZE
SET
                                      payload
-----------------------------------------------------------------------------------
 {"tags": ["vip", "mobile"], "type": "refund", "user": {"id": 201, "plan": "pro"}}
(1 row)

Step 2: the query without an index

The containment operator @> asks "does this document contain this smaller document?"

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload @> '{"type": "refund"}';

                        QUERY PLAN
----------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on events (actual rows=400.00 loops=1)
         Filter: (payload @> '{"type": "refund"}'::jsonb)
         Rows Removed by Filter: 19600
(4 rows)

It reads all 20,000 rows and throws 19,600 away.

Step 3: add a GIN index

CREATE INDEX events_payload_gin ON events USING gin (payload);
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload @> '{"type": "refund"}';
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload @> '{"user": {"plan": "pro"}, "tags": ["vip"]}';

CREATE INDEX
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on events (actual rows=400.00 loops=1)
         Recheck Cond: (payload @> '{"type": "refund"}'::jsonb)
         Heap Blocks: exact=328
         ->  Bitmap Index Scan on events_payload_gin (actual rows=400.00 loops=1)
               Index Cond: (payload @> '{"type": "refund"}'::jsonb)
               Index Searches: 1
(7 rows)

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on events (actual rows=100.00 loops=1)
         Recheck Cond: (payload @> '{"tags": ["vip"], "user": {"plan": "pro"}}'::jsonb)
         Heap Blocks: exact=100
         ->  Bitmap Index Scan on events_payload_gin (actual rows=100.00 loops=1)
               Index Cond: (payload @> '{"tags": ["vip"], "user": {"plan": "pro"}}'::jsonb)
               Index Searches: 1
(7 rows)

Both queries use the index, even the one that looks into a nested object and into an array. The index stores every key and value in the documents, so one index serves any @> filter you write later. It is a bitmap scan: the index returns the matching row locations, and the table is read once for those blocks.

Step 4: the same filter written with an arrow

The next query asks for the same events, written with ->>, the operator that extracts a key as text.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload ->> 'type' = 'refund';

                          QUERY PLAN
---------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on events (actual rows=400.00 loops=1)
         Filter: ((payload ->> 'type'::text) = 'refund'::text)
         Rows Removed by Filter: 19600
(4 rows)

The GIN index is there, and the plan scans the table. A GIN index on the whole column answers questions about the column, such as @> and ?. It knows nothing about the expression payload ->> 'type'. If the application writes filters with ->>, either change the filter to @>, or index the expression.

Step 5: index the expression you filter on

For one key that is queried with equality all the time, an ordinary B-tree index on the expression is usually smaller than a GIN index, and it also supports <, > and sorting. The query has to use the same expression as the index.

CREATE INDEX events_type_idx ON events ((payload ->> 'type'));
ANALYZE events;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload ->> 'type' = 'refund';

CREATE INDEX
ANALYZE
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on events (actual rows=400.00 loops=1)
         Recheck Cond: ((payload ->> 'type'::text) = 'refund'::text)
         Heap Blocks: exact=328
         ->  Bitmap Index Scan on events_type_idx (actual rows=400.00 loops=1)
               Index Cond: ((payload ->> 'type'::text) = 'refund'::text)
               Index Searches: 1
(7 rows)

Step 6: a smaller GIN index, with fewer operators

The jsonb_path_ops operator class is built for @> only. It is smaller and faster for that one job.

CREATE INDEX events_payload_path ON events USING gin (payload jsonb_path_ops);
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('events_payload_gin', 'events_payload_path')
ORDER BY relname;

CREATE INDEX
       relname       |  size
---------------------+--------
 events_payload_gin  | 376 kB
 events_payload_path | 216 kB
(2 rows)

It is not a free swap. Drop the default index and the key-exists operator ?, which asks whether a key or an array element is present, stops using an index:

DROP INDEX events_payload_gin;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload -> 'tags' ? 'vip';

DROP INDEX
                        QUERY PLAN
-----------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on events (actual rows=100.00 loops=1)
         Filter: ((payload -> 'tags'::text) ? 'vip'::text)
         Rows Removed by Filter: 19900
(4 rows)

Check which operators your queries use before you choose the class. If they are all @>, use jsonb_path_ops. If you also use ?, ?| or ?&, keep the default.

Related course: SQL Advanced.