# GIN Indexes for JSONB, Arrays and Text Search — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/x-gin

> Indexes for "contains" queries.

## 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.

```sql
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.

**Quiz:** Which index type suits jsonb @> containment queries?

- [ ] Hash
- [ ] B-tree on the whole column
- [x] GIN
- [ ] No index can help

*Answer:* GIN. Inverted indexes for element lookups.