postgres jsonb gin index slow queries

TL;DR

Match the index to the operator: the default jsonb_ops GIN index serves @>, ?, ?&, and ?|; jsonb_path_ops is smaller and faster but only serves @>. If EXPLAIN still seq-scans, the query is probably not sargable or the table is too small for the planner to care.

postgres jsonb gin index slow queries

Use this when

• A GIN index exists but queries still sequential-scan
• JSONB containment or key-existence queries are slow
• You are choosing a JSONB index strategy

Not for this skill when

• Autovacuum is falling behind
• You need upsert syntax
• Materialized view refreshes block readers

Steps

1. Confirm the planner's actual choice with EXPLAIN:

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

Expected output: either Bitmap Heap Scan with a Bitmap Index Scan on your GIN index (good) or Seq Scan (the problem). Do not tune on assumptions; read the plan.

1. Create the right GIN index for your operators:

-- general purpose: supports @>, ?, ?&, ?|
CREATE INDEX ON events USING gin (payload);
-- smaller and faster, but ONLY for @> containment:
CREATE INDEX ON events USING gin (payload jsonb_path_ops);

Expected output: the index builds. Pick jsonb_path_ops when every query is @>; it indexes only hashed paths and is noticeably smaller. Pick default jsonb_ops when you also query ? (key exists) or ?&/?|.

1. If the plan still seq-scans, check selectivity. The planner is often right:

SELECT count(*) FILTER (WHERE payload @> '{"type": "click"}') AS matches,
       count(*) AS total FROM events;

Expected output: if matches are a large fraction of total, a seq scan genuinely is faster and the index is correctly ignored. Indexes help selective queries.

1. For queries on one hot key, an expression index beats a general GIN:

CREATE INDEX ON events ((payload ->> 'user_id'));
SELECT * FROM events WHERE payload ->> 'user_id' = '42';

Expected output: a small btree on exactly the extracted value. When 90% of queries filter on the same two keys, targeted expression indexes outperform a GIN on the whole document.

1. Keep the index healthy: GIN indexes bloat under write churn:

ALTER INDEX events_payload_idx SET (fastupdate = off);
-- or schedule REINDEX for write-heavy tables

Expected output: with fastupdate=off, entries go straight to the main index instead of the pending list, trading slower writes for consistently fast reads. For read-mostly analytics tables the default is fine.

Variant phrasings

postgres gin index not used jsonb

Usually wrong operator for the opclass (step 2), non-selective predicate (step 3), or stale statistics. Run ANALYZE after creating the index.

jsonbpathops vs jsonb_ops

jsonb_path_ops is smaller/faster but only accelerates @>. If any query uses ?, ?&, or ?|, you need jsonb_ops.

postgres jsonb query slow no index

Add the GIN index (step 2), but first check with EXPLAIN whether the query shape is even indexable. Functions wrapping the column (e.g. lower(payload->>'x')) defeat plain indexes; use expression indexes.

Why it happens

GIN indexes accelerate specific JSONB operators, not JSONB in general. A query using ->> extraction gets nothing from a GIN on the whole document, and a jsonb_path_ops index is invisible to ? queries. The planner then correctly falls back to seq scan, which looks like "the index does not work."

Edge cases

• GIN pending-list bloat makes freshly-written data slow to query; fastupdate=off or periodic VACUUM clears it.
• Very large JSONB documents make GIN entries huge; consider extracting hot fields to real columns.
• jsonb_path_ops does not support indexing the existence operator at all, queries silently seq-scan.
• Partial GIN indexes (WHERE payload ? 'type') shrink the index when only a subset of rows carry JSONB.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_cNOhKkQFG0Hk2AqqMGnzcw