Fixing PostgreSQL JSONB Query That Ignores Index and Falls Back to Seq Scan

You added a GIN index to your JSONB column, ran your query, and EXPLAIN ANALYZE still shows Seq Scan on your_table with a cost that climbs as your data grows. The index exists — you can see it in \d your_table — but the planner refuses to use it.

This is one of the most frustrating PostgreSQL gotchas because the fix is rarely obvious from the error. The planner doesn't tell you why it chose a sequential scan; it just does. This article walks you through every common reason this happens and shows you how to confirm the fix with concrete query examples.

What You'll Learn

• How PostgreSQL's GIN index works with JSONB and which operators it supports
• Why operator mismatches, casts, and operator class choices silently break index usage
• How to read EXPLAIN ANALYZE output to diagnose the exact cause
• How to write partial indexes that reduce index size and improve selectivity
• How to confirm a working fix before deploying to production

Prerequisites

You should be comfortable writing basic PostgreSQL queries and reading EXPLAIN output. You'll need access to a PostgreSQL instance at version 12 or later. The examples use a table called events with a payload column of type jsonb.

How PostgreSQL Indexes JSONB Data

A standard B-tree index stores individual scalar values in sorted order. JSONB documents are structured data, not scalars, so B-tree indexes don't apply. PostgreSQL uses a GIN (Generalized Inverted Index) instead. GIN decomposes each document into its component keys and values, then builds an inverted index mapping each element back to the rows that contain it.

When you run CREATE INDEX idx_events_payload ON events USING GIN (payload);, PostgreSQL creates this inverted structure. The critical constraint: GIN can only be used when the query operator matches what the index was built to support. Use the wrong operator and the planner simply can't use the index, even if the data is identical.

There are two relevant operator classes for JSONB GIN indexes:

• jsonb_ops (the default) — supports @>, ?, ?|, ?&, and @@
• jsonb_path_ops — supports only @> and @@, but produces a smaller and faster index for containment queries

The Most Common Cause: Using the Wrong Operator

The single most frequent reason a JSONB index gets ignored is querying with an operator the GIN index doesn't support. The -> and ->> extraction operators are not indexable by a standard GIN index. They extract a value for comparison, but the comparison itself happens outside the index lookup.

This query will not use your GIN index:

-- Seq Scan — the GIN index cannot handle ->> with equality
SELECT *
FROM events
WHERE payload->>'event_type' = 'purchase';

To make the planner use the index, rewrite using the containment operator @>:

-- Index Scan — GIN supports @>
SELECT *
FROM events
WHERE payload @> '{"event_type": "purchase"}'::jsonb;

Both queries return the same rows, but only the second one lets PostgreSQL use the GIN index. The @> operator asks "does this document contain this sub-document?" which maps directly to the inverted index structure.

The same fix applies for numeric comparisons on JSONB keys. Instead of payload->>'amount' > '100' (which compares strings), use a containment pattern or create a separate expression index on the extracted value.

Casting and Expression Mismatches That Break Index Use

Even when you use @>, implicit casts can prevent index usage. The right-hand side of the containment operator must be jsonb, not text or json. If PostgreSQL has to implicitly coerce your literal, it may evaluate the expression differently than expected.

Always cast your literal explicitly:

-- Explicit cast — safe and unambiguous
SELECT *
FROM events
WHERE payload @> '{"status": "active"}'::jsonb;

A subtler problem occurs when you wrap the JSONB column in a function. Any function call on the indexed column breaks GIN index usage unless you've created a matching expression index. For example, jsonb_strip_nulls(payload) @> '...' will force a sequential scan because the index is on payload, not jsonb_strip_nulls(payload).

If your application always queries a normalized form of the column, build the index on that expression:

CREATE INDEX idx_events_payload_stripped
ON events
USING GIN (jsonb_strip_nulls(payload));

Then your query must use the same expression, exactly, in the WHERE clause.

Operator Class Mismatch: jsonb_ops vs jsonb_path_ops

PostgreSQL offers two GIN operator classes for JSONB. If your index was created with jsonb_path_ops but your query uses the key-existence operator ?, the planner will ignore the index entirely.

Here's a concrete example. Suppose you created the index like this:

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

This query will not use that index because jsonb_path_ops does not support ?:

-- Seq Scan — jsonb_path_ops doesn't support ?
SELECT * FROM events WHERE payload ? 'user_id';

You have two options: switch back to the default jsonb_ops index for key-existence checks, or maintain two indexes — one per operator class — if you need both query patterns.

The tradeoff is worth understanding. A jsonb_path_ops index is typically 20–40% smaller because it only indexes values (not keys), so it's faster for pure containment queries on large datasets. Use jsonb_ops when you also need ?, ?|, or ?&.

If you're not sure which operator class your existing index uses, check with:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'events' AND indexname LIKE '%payload%';

When Statistics and Table Size Override the Index

Sometimes the query and index are both correct, but the planner still chooses a sequential scan. This happens when the planner's cost model estimates that a seq scan is cheaper. Two conditions trigger this:

• Low selectivity: If your query matches more than roughly 5–10% of rows, a seq scan is genuinely faster for heap-based tables. The GIN index lookup returns many row pointers, and fetching them randomly is slower than reading the table in order.
• Stale statistics: If ANALYZE hasn't run recently, the planner may have an inaccurate row-count estimate and make the wrong choice.

Run ANALYZE events; to refresh statistics, then re-run your EXPLAIN ANALYZE. If the plan changes, stale statistics were the culprit.

If selectivity is genuinely low, consider whether a partial index is more appropriate (see the next section). Alternatively, you can test whether the planner is making the wrong call by temporarily disabling seq scans in your session:

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM events WHERE payload @> '{"event_type": "purchase"}'::jsonb;
SET enable_seqscan = on; -- always reset this

If the index scan is dramatically faster with seq scan disabled, the planner is miscalibrated. You can tune random_page_cost (default 4.0) down toward 1.0–2.0 on SSD-backed storage to help the planner prefer index scans. See the broader guide on seq scans despite an index for a full walkthrough of cost parameter tuning.

Partial Indexes for Selective JSONB Queries

If only a fraction of your rows match a common filter, a partial index can dramatically cut index size and improve scan performance. Instead of indexing the entire payload column, you index only the rows that your queries actually care about.

-- Only index rows where the event type is 'purchase'
CREATE INDEX idx_events_purchase_payload
ON events
USING GIN (payload)
WHERE payload->>'event_type' = 'purchase';

For the planner to use this index, your query must include the partial index condition verbatim in the WHERE clause:

SELECT *
FROM events
WHERE payload->>'event_type' = 'purchase'
  AND payload @> '{"amount": 500}'::jsonb;

Partial indexes work well when one JSONB key acts as a discriminator and most queries filter on it first. They're also a good fit for soft-delete patterns where you only ever query non-deleted rows.

Note that the partial condition uses ->> (text extraction) which normally wouldn't use a GIN index — but here it's part of the index predicate, not the search expression. The @> in the query body still drives the index scan.

Verifying Your Fix with EXPLAIN ANALYZE

After making any change, always confirm with EXPLAIN (ANALYZE, BUFFERS). The output you're looking for contains Bitmap Index Scan or Index Scan on your GIN index, not Seq Scan.

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

A healthy output looks like this:

Bitmap Heap Scan on events  (cost=24.50..89.10 rows=45 width=312)
  Recheck Cond: (payload @> '{"event_type": "purchase"}'::jsonb)
  ->  Bitmap Index Scan on idx_events_payload  (cost=0.00..24.49 rows=45 width=0)
        Index Cond: (payload @> '{"event_type": "purchase"}'::jsonb)
Planning Time: 0.3 ms
Execution Time: 1.2 ms

Compare this to the same query before the fix, where you'd see Seq Scan on events with a much higher execution time. Pay attention to the Buffers line too — a large number of shared hit or read blocks on a seq scan confirms you were touching every page of the table.

This diagnostic approach is covered in more depth for general cases in the article on diagnosing seq scans despite an index. The pattern for JSONB is a specific instance of the same planner mechanics.

One more thing to check: make sure the index itself is valid. A failed or in-progress index build shows up as indisvalid = false in pg_index:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indrelid
WHERE pg_class.relname = 'events';

An invalid index won't be used by the planner. Drop it and rebuild it with CREATE INDEX CONCURRENTLY to avoid locking your table during the rebuild.

Common Pitfalls

• Mixing ->> comparisons with numeric types. payload->>'score' > '50' does a lexicographic string comparison, not a numeric one. Cast the extracted value: (payload->>'score')::numeric > 50. This won't use GIN; create a B-tree expression index on the cast expression instead.
• Forgetting to run ANALYZE after bulk loads. Autovacuum may not have caught up after a large COPY or INSERT. Run ANALYZE manually after loading significant amounts of data.
• Using jsonb_path_query or jsonb_each in the WHERE clause. Set-returning functions in WHERE are evaluated row by row and cannot use an index. Move the logic to @> or @@ with a jsonpath expression.
• Creating the index in a transaction that was rolled back. If your migration script created the index inside a transaction that failed, the index may not exist at all. Check \d events or query pg_indexes.
• Over-indexing nested JSONB paths. GIN indexes on deep, highly variable documents can become large and slow to update. Consider whether you should promote frequently-queried keys to dedicated columns instead.

The pattern of silent planner overrides also shows up in other query types. For example, the article on CTEs that materialize when you need them inline covers a related scenario where PostgreSQL's planner makes a structural choice that hurts performance without any visible error.

Next Steps

Here are four concrete things you can do right now to resolve and prevent this problem:

1. Audit your JSONB queries for ->> comparisons and rewrite them using @> where the value is an equality check. This is the fastest win for most applications.
2. Check your index operator class with pg_indexes and confirm it matches the operators your queries actually use. Recreate with the correct class if needed.
3. Run ANALYZE on affected tables if bulk loads or large deletes have happened recently, then re-check your query plans.
4. Add partial indexes for your highest-traffic JSONB query patterns if only a subset of rows match a common discriminator key.
5. Tune random_page_cost to reflect your storage type (closer to 1.1 for NVMe SSDs) so the planner's cost estimates favor index scans on selective queries.

If you're troubleshooting a different kind of silent planner behavior, the article on PostgreSQL UPDATE FROM that modifies wrong rows on a join is worth reading — it shows another case where PostgreSQL does something unexpected without raising an error.