GIN Indexes for JSONB, Arrays and Text Search

Inverted indexes

GIN (generalised inverted) indexes map each element (a JSON key-value, an array item, a word) to the rows containing it, which suits containment queries such as payload @> '{"device": "ios"}', array overlaps, and full-text search. The jsonb_path_ops operator class makes smaller, faster indexes when you only need @>. GIN indexes are slower to update than B-trees, so add them where reads justify it.

Containment before and after a GIN index, run

I ran this with psql against PostgreSQL 16.2 on a fresh database with a 200,000-row events table (created with generate_series and analysed). Without an index, PostgreSQL scans the table in parallel; after creating a GIN index on payload, it uses a bitmap index scan. One in three events has device ios: 66,666 rows. For a condition matching a third of the table, the planner may still prefer a sequential scan in other situations; GIN pays off most for selective conditions.

EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE payload @> '{"device": "ios"}';
CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE payload @> '{"device": "ios"}';
SELECT count(*) FROM events WHERE payload @> '{"device": "ios"}';

Output:

                             QUERY PLAN                              
---------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Seq Scan on events
                     Filter: (payload @> '{"device": "ios"}'::jsonb)
(6 rows)

                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on events
         Recheck Cond: (payload @> '{"device": "ios"}'::jsonb)
         ->  Bitmap Index Scan on events_payload_idx
               Index Cond: (payload @> '{"device": "ios"}'::jsonb)
(5 rows)

 count 
-------
 66666
(1 row)

Index selective conditions

Indexes help most when a condition matches a small fraction of rows; for broad conditions, scanning can be just as fast.

Quick check: Which index type suits jsonb @> containment queries?

• Hash
• B-tree on the whole column
• GIN
• No index can help