How to Resolve "PostgreSQL Slow Query Due to Missing Index on JSONB"

A query filtering on data stored inside a JSONB column takes far longer than an equivalent query on a normal indexed column, even on a table that isn't especially large. JSONB fields don't automatically get useful indexes the way regular columns often do, and a query filtering into JSON structure without one forces PostgreSQL to scan and parse every row's JSON payload from scratch.

The Problem

A query filtering on a JSONB field takes seconds instead of milliseconds:

EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

Seq Scan on events  (cost=0.00..48213.00 rows=1021 width=812) (actual time=0.045..1842.291 rows=15234 loops=1)
  Filter: (payload @> '{"type": "purchase"}'::jsonb)
  Rows Removed by Filter: 984766
Planning Time: 0.112 ms
Execution Time: 1843.019 ms

The plan shows a sequential scan filtering out nearly a million rows to find the matching ones — exactly the pattern that a good index should eliminate.

Why It Happens

A regular B-tree index (the PostgreSQL default) works well for equality and range comparisons on scalar values, but it doesn't understand the internal structure of a JSONB document, so it's useless for queries filtering on keys nested inside one. Without a specialized index designed for JSON structure, PostgreSQL has no choice but to read and parse every row's JSONB payload to check if it matches your filter — a sequential scan, regardless of how selective the actual filter is. This slowness shows up specifically when:

• Filtering with the containment operator (@>) or key-existence operators (?, ?|, ?&) on a JSONB column with no supporting index at all.
• The table has grown large enough that scanning and parsing every row's JSON is genuinely expensive, whereas the same query felt fast on a small development dataset.
• An index exists but is the wrong type for the query pattern — a plain B-tree index on the whole JSONB column doesn't help containment queries the way a GIN index does.

The Fix

Add a GIN (Generalized Inverted Index) index on the JSONB column, which is purpose-built for containment and existence queries against JSON structure:

CREATE INDEX idx_events_payload ON events USING GIN (payload);

Re-run the query and check the plan again:

EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

Bitmap Heap Scan on events  (cost=68.23..4821.11 rows=1021 width=812) (actual time=0.891..4.523 rows=15234 loops=1)
  Recheck Cond: (payload @> '{"type": "purchase"}'::jsonb)
  ->  Bitmap Index Scan on idx_events_payload  (cost=0.00..68.00 rows=1021 width=0) (actual time=0.612..0.612 rows=15234 loops=1)
Planning Time: 0.203 ms
Execution Time: 4.891 ms

Execution time drops from nearly two seconds to a few milliseconds. If your queries consistently use the containment operator (@>) specifically, and never the key-existence operators, the more compact jsonb_path_ops variant produces a smaller, often faster index for that specific access pattern:

CREATE INDEX idx_events_payload_pathops ON events USING GIN (payload jsonb_path_ops);

If you only ever query a specific known key rather than arbitrary JSON structure, an expression index targeting just that key can be even more efficient than a full GIN index over the whole document:

CREATE INDEX idx_events_type ON events ((payload->>'type'));

SELECT * FROM events WHERE payload->>'type' = 'purchase';

This regular B-tree expression index works well when your access pattern is consistently a single, specific key lookup rather than general JSON containment, and it's typically smaller and faster to maintain than a full GIN index.

Still Not Working?

If you've added a GIN index but the planner still chooses a sequential scan, check whether table statistics are stale — PostgreSQL's query planner relies on statistics to decide whether using an index is actually cheaper than a scan, and outdated statistics can lead it to underestimate the index's benefit:

ANALYZE events;

Re-run EXPLAIN ANALYZE after updating statistics. If the planner still prefers a sequential scan even with a fresh, accurate row count estimate, double-check the query actually matches the operator the index was built for — a GIN index on containment (@>) won't be used automatically for a differently-structured query like a raw text search inside the JSON, which needs its own separate indexing strategy.