Indexing jsonb: GIN + the @> containment operator
A jsonb_path_ops GIN index dramatically speeds up @> containment queries over large document volumes. Verify it's used with EXPLAIN.
Prerequisites
PostgreSQL 9.4+
SQL
CREATE INDEX idx_events_payload
ON events USING GIN (payload jsonb_path_ops);
-- Requête de containment : exploite l'index
SELECT id, created_at
FROM events
WHERE payload @> '{"type": "payment", "status": "failed"}';
-- Contrôle : le plan doit montrer un Bitmap Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE payload @> '{"type": "payment"}';Result
CREATE INDEX
id | created_at
--------+---------------------
184201 | 2026-06-09 22:14:08
184530 | 2026-06-10 03:41:55
(2 rows)
Aggregate (actual time=4.182..4.183 rows=1 loops=1)
-> Bitmap Heap Scan on events (actual rows=8421)
Recheck Cond: (payload @> '{"type": "payment"}')
-> Bitmap Index Scan on idx_events_payload
Execution Time: 4.610 ms -- vs 2 180 ms en Seq ScanSQLGINjsonbPerformance