PostgreSQL — JSONB Queries Trigger Full Table Scans

Make PostgreSQL use indexes for JSONB containment and path queries, choose between GIN default and jsonb_path_ops operators, and restructure hot predicates as columns.

intermediatedebuggingpostgresqljsonbindexes

Symptoms

Queries with @> or ->> predicates on jsonb columns show Seq Scan on large tables, taking seconds each
EXPLAIN confirms Rows Removed by Filter in the millions although the predicate is highly selective
A dynamic-attributes design (EAV-style jsonb) slows down as the table grows

Possible causes

No GIN index exists on the jsonb column, so containment operators scan the whole table
A GIN index exists but the query uses an operator class mismatch (for example ->> equality instead of @>)
The jsonb_path_ops index cannot serve the operator actually used in the predicate
Frequently filtered fields live inside JSONB instead of as plain columns with btree indexes

Diagnosis steps

Confirm the plan and operator

psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE payload @> '{"type":"signup"}';"

Interpretation

Seq Scan with Rows Removed by Filter confirms the index absence or mismatch; the operator in the predicate decides which index class can serve it.

Check existing index operator classes

psql -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'events';"

Interpretation

indexdef shows the operator class: plain GIN serves @>, jsonb_path_ops serves @> with smaller index, nothing serves ->> equality except expression indexes.

Compare index options on real data

psql -c "CREATE INDEX ON events USING gin (payload jsonb_path_ops); ANALYZE events;" then re-run EXPLAIN

Interpretation

A Bitmap Index Scan appearing in the plan with buffer counts dropping is the direct evidence the operator class matches the predicate.

Root cause

JSONB stores no inherent order or statistics that a plain btree can exploit, and containment operators require a GIN inverted index keyed on the jsonb structure. Without a matching GIN operator class for the exact operator in the predicate, the planner has no index to use and falls back to scanning and deserializing every row.

Fix

Create GIN indexes matching the operators used (default jsonb_ops for broad queries, jsonb_path_ops for pure containment with smaller and faster indexes), add expression indexes for hot ->> equality fields, or promote the hottest JSONB fields to generated columns with btree indexes.

Risk note

GIN indexes are larger and slower to update than btree, so heavy-write tables pay per insert. jsonb_path_ops cannot serve existence operators that jsonb_ops can.

Verify the fix

After indexing, EXPLAIN ANALYZE shows Bitmap Index Scan with realistic row estimates, and query latency drops by an order of magnitude at production data volume.

Symptom walkthrough

The plan shows the brute force plainly:

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

Seq Scan on events  (cost=0.00..284712.00 rows=120 width=987)
  Filter: (payload @> '{"type":"signup"}'::jsonb)
  Rows Removed by Filter: 9312765
Buffers: shared hit=1482112 read=882110

Nine million rows deserialized and discarded per query. The operator class table is the whole fix space: @> needs GIN; ->> ‘key’ = ‘value’ needs an expression index on the extraction; ? (existence) needs default jsonb_ops, not jsonb_path_ops.

Fix in practice

Match the index to the operator:

-- pure containment, the most common hot path:
CREATE INDEX events_payload_pathops
  ON events USING gin (payload jsonb_path_ops);

-- equality on a hot extracted field:
CREATE INDEX events_source
  ON events ((payload->>'source'));

-- promote the hottest field to a real column:
ALTER TABLE events
  ADD COLUMN event_type text
  GENERATED ALWAYS AS (payload->>'type') STORED;
CREATE INDEX ON events (event_type);
ANALYZE events;

The generated-column path is the long-term winner for the few fields that dominate your filters: real statistics, real btree, plain-SQL ergonomics. Keep JSONB for the genuinely dynamic tail, indexed by containment.

Verification

　　Re-run the same EXPLAIN ANALYZE: expect Bitmap Index Scan instead of Seq Scan, estimated rows close to actual, and buffer reads down two orders of magnitude. Then measure at production volume with a range of payload shapes — the point of jsonb_path_ops is its smaller index for containment-only workloads, and the point of promotion is statistics-driven planning, so verify both benefits before committing the schema change.