Jsonb Query Slow Without Gin Index
A jsonb filter is functionally correct but reads far more of the table than the result size justifies, and its latency scales with table size rather than...
In 10 seconds
A query that filters a jsonb column with the containment operator -- WHERE data @> '{"status":"archived"}' -- returns the right rows but reads the entire table to do it, and gets slower every week as the table grows. The column is correctly typed jsonb and the predicate is correct, so nothing looks wrong in the code; the problem is purely a missing access path. PostgreSQL's containment, key-exists, and path operators on jsonb (@>, ?, ?&, ?|, and the jsonpath @@/@?) can only be answered by a GIN index. A plain B-tree index -- including the primary key -- cannot serve them, so when no GIN index exists the planner has exactly one option: a Sequential Scan that reads every row and re-checks the @> filter, even when the predicate matches a tiny fraction of the table. The lab makes the cost exact: `docs` holds 300,000 jsonb rows where only ~0.1% (300 rows) carry status=archived. With no GIN index, EXPLAIN of the count shows a Parallel Seq Scan over all 300,000 rows at a total cost of 6848.74, with Rows Removed by Filter: 99900 per worker -- the whole table is read and thrown away to return 300 rows. Adding a single GIN index (CREATE INDEX ... USING gin (data)) flips the same query to a Bitmap Index Scan on the GIN index at cost 134.57, reading Heap Blocks: exact=300 -- a roughly 51x drop in estimated cost because the work is now proportional to the matches, not the table. The recipe shows the FREE diagnosis (the plan is a Seq Scan, and listing the indexes proves the only one is the B-tree primary key, which cannot serve @>), the PREMIUM diagnosis (a catalog-only scan that finds EVERY jsonb column lacking a GIN index across the whole database, ranking the largest tables first and correctly leaving the already-indexed app_config.settings alone), the FIX (the GIN index, which serves arbitrary containment keys -- status and owner alike -- from one index), and the PREVENTION (jsonb_path_ops, a smaller GIN opclass purpose-built for @>, plus the same scanner re-run as a standing CI guard so a new unindexed jsonb column can never ship unnoticed).
Why This Happens
jsonb is a flexible document type, but its rich operators are not free for the planner to evaluate with a B-tree. A B-tree index orders rows by a scalar key, so it can answer equality and range predicates on that key; it has no way to answer 'does this document CONTAIN this sub-document?' (@>), 'does it have this key?' (?), or a jsonpath match (@@/@?). Those operators are implemented by the GIN (Generalized Inverted Index) access method, which indexes the individual components of each document -- by default, via the jsonb_ops operator class, every key and every value as separate index entries -- so a containment query can intersect posting lists instead of scanning rows.
Without a GIN index on the column, the planner has no index path for @> at all, and its only choice is a Sequential Scan that loads every heap page and re-evaluates the @> filter on each row. That is exactly what the lab captures: 300,000 rows scanned at cost 6848.74 to return 300, with 99900 rows removed by the filter per worker. The cost is O(table), so the query degrades as the table grows regardless of how selective the predicate is. Creating a GIN index gives the planner a Bitmap Index Scan: it probes the GIN index for the entries that satisfy @>, builds a bitmap of candidate heap pages, and a Bitmap Heap Scan reads only those pages with a Recheck of the original condition.
In the lab the same query drops to cost 134.57 reading Heap Blocks: exact=300 -- the work is now proportional to the matches. A crucial property is that ONE GIN index on the whole jsonb document serves arbitrary containment keys: the recipe proves that the same docs_data_gin index answers both data @> '{"status":...}' and data @> '{"owner":...}', because GIN indexed every key/value pair, not just one path. For a workload that uses ONLY containment (@>) and not the key-exists operators, the jsonb_path_ops operator class is the right-sized default: it stores a single hash per path-to-value rather than separate key and value entries, so the index is materially smaller (4680 kB vs 3696 kB in the lab) and cheaper to build and maintain, at the cost of not supporting ?, ?&, ?|.
The reason this trap is so common is that jsonb columns are usually created without an index 'until we need it', and the @> queries work correctly from day one -- they are merely O(table), so the regression hides until the table is large enough for a full scan to hurt. The durable prevention is to treat 'a jsonb column queried with @> must have a GIN index' as an invariant and to check it automatically: a catalog scan over pg_class/pg_attribute/pg_type joined against pg_index/pg_am can enumerate every jsonb column and confirm a GIN index covers it, catching a new unindexed column in CI before it ever ships a Seq Scan to production. The durable prevention is to treat 'a jsonb column queried with @> must have a GIN index' as an invariant and to check it automatically: a catalog scan over pg_class/pg_attribute/pg_type joined against pg_index/pg_am can enumerate every jsonb column and confirm a GIN index covers it, catching a new unindexed column in CI before it ever ships a Seq Scan to production.
Diagnose
Free Tier -- Basic Approach
The on-call's first read with zero special tooling: look at the plan, then ask why. EXPLAIN already names the problem -- a Seq Scan with the @> predicate as a Filter -- and a one-line catalog query proves the cause: the only index on the table cannot serve containment.
EXPLAIN (COSTS ON)
SELECT count(*) FROM docs WHERE data @> '{"status":"archived"}';
QUERY PLAN
-----------------------------------------------------------------------------------
Finalize Aggregate (cost=6848.74..6848.76 rows=1 width=8)
-> Gather (cost=6848.53..6848.74 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=5848.53..5848.54 rows=1 width=8)
-> Parallel Seq Scan on docs (cost=0.00..5848.50 rows=12 width=0)
Filter: (data @> '{"status": "archived"}'::jsonb)
(6 rows)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'docs' ORDER BY indexname;
indexname | indexdef -----------+--------------------------------------------------------------- docs_pkey | CREATE UNIQUE INDEX docs_pkey ON public.docs USING btree (id) (1 row)
The free diagnostic runs EXPLAIN (COSTS ON) of the same count and shows the 'Parallel Seq Scan on docs (cost=0.00..5848.50 rows=12 width=0)' with 'Filter: (data @> '{"status": "archived"}'::jsonb)' at a top-level cost of 6848.74 -- the whole table scanned for a selective predicate. Then it lists the indexes on the table with pg_indexes, which returns exactly one row: 'docs_pkey | CREATE UNIQUE INDEX docs_pkey ON public.docs USING btree (id)'.
That single fact closes the diagnosis: the only index is a B-tree primary key, and a B-tree cannot answer the @> containment operator -- only a GIN index can. Any operator can run these two reads (one EXPLAIN, one catalog list) with no extensions and conclude 'this jsonb column has no GIN index'.
What the free read does NOT tell you is which OTHER jsonb columns across the database are sitting on the same trap -- that fleet-wide, predictive view is the premium step.
Fix
The fix is a single index: give the @> operator the access path it was missing. A GIN index on the jsonb column lets the planner switch from reading the whole table to probing the index for only the matching rows.
CREATE INDEX docs_data_gin ON docs USING gin (data); ANALYZE docs;
CREATE INDEX ANALYZE
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF, SUMMARY OFF, COSTS ON)
SELECT count(*) FROM docs WHERE data @> '{"status":"archived"}';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=134.57..134.58 rows=1 width=8) (actual rows=1 loops=1)
-> Bitmap Heap Scan on docs (cost=21.65..134.49 rows=30 width=0) (actual rows=300 loops=1)
Recheck Cond: (data @> '{"status": "archived"}'::jsonb)
Heap Blocks: exact=300
-> Bitmap Index Scan on docs_data_gin (cost=0.00..21.64 rows=30 width=0) (actual rows=300 loops=1)
Index Cond: (data @> '{"status": "archived"}'::jsonb)
(6 rows)
The fix capture runs CREATE INDEX docs_data_gin ON docs USING gin (data) and ANALYZE, then re-runs the same count under EXPLAIN ANALYZE. The plan is transformed: 'Aggregate (cost=134.57..134.58 rows=1 width=8) (actual rows=1 loops=1)' over a 'Bitmap Heap Scan on docs (cost=21.65..134.49 rows=30 width=0) (actual rows=300 loops=1)' with 'Heap Blocks: exact=300', fed by a 'Bitmap Index Scan on docs_data_gin (cost=0.00..21.64 rows=30 width=0) (actual rows=300 loops=1)' whose 'Index Cond' is the @> predicate.
The Parallel Seq Scan is gone; the total cost falls from 6848.74 to 134.57 -- roughly 51x -- and the query reads only 300 heap blocks (the matches) instead of every page of the table. The work is now proportional to the result, not the table size, which is the whole point of the index.
On a real, busy table prefer CREATE INDEX CONCURRENTLY so the build does not block writes for its duration.
Verify
Two checks confirm the fix is complete and general: the one GIN index serves ANY containment key (not just the one we tested), and the premium scanner now reports the column as covered.
EXPLAIN (COSTS OFF)
SELECT count(*) FROM docs WHERE data @> '{"status":"archived"}';
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on docs
Recheck Cond: (data @> '{"status": "archived"}'::jsonb)
-> Bitmap Index Scan on docs_data_gin
Index Cond: (data @> '{"status": "archived"}'::jsonb)
(5 rows)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM docs WHERE data @> '{"owner":"user5"}';
QUERY PLAN
-----------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on docs
Recheck Cond: (data @> '{"owner": "user5"}'::jsonb)
-> Bitmap Index Scan on docs_data_gin
Index Cond: (data @> '{"owner": "user5"}'::jsonb)
(5 rows)
SELECT c.relname AS table_name,
a.attname AS jsonb_column,
EXISTS (
SELECT 1 FROM pg_index i
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am am ON am.oid = ic.relam
WHERE i.indrelid = c.oid
AND am.amname = 'gin'
AND a.attnum = ANY (i.indkey)
) AS has_gin_index
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
JOIN pg_type t ON t.oid = a.atttypid
WHERE c.relkind = 'r' AND n.nspname = 'public' AND t.typname IN ('jsonb','json')
AND c.relname = 'docs'
ORDER BY a.attname;
table_name | jsonb_column | has_gin_index ------------+--------------+--------------- docs | data | t (1 row)
The verify capture runs EXPLAIN (COSTS OFF) of the count twice with different containment keys. Filtering on status plans as a 'Bitmap Heap Scan on docs' fed by a 'Bitmap Index Scan on docs_data_gin' with 'Index Cond: (data @> '{"status": "archived"}'::jsonb)'.
Filtering on a DIFFERENT key plans exactly the same way -- 'Bitmap Index Scan on docs_data_gin' with 'Index Cond: (data @> '{"owner": "user5"}'::jsonb)' -- proving that one GIN index on the whole document answers arbitrary containment keys, because GIN (jsonb_ops) indexed every key/value pair, not just one path. Then the capture re-runs the premium catalog scanner restricted to docs and gets 'docs | data | t': the landmine is cleared.
The fix is therefore not a one-query patch -- the single index covers the entire containment workload on that column, and the catalog now confirms it.
⏪ Rollback
Nothing here is destructive, and the entire reproduction runs against a throwaway database created and dropped on a PostgreSQL instance; the lab is torn down at the end and only the literal capture files are kept. The fix and prevention are both CREATE INDEX statements, which add an index and change no data and no existing rows. Each is trivially reversible with DROP INDEX docs_data_gin (or DROP INDEX docs_data_gin_pathops), which removes the index and returns the planner to the Seq Scan -- no data is touched. On a real, large table, prefer CREATE INDEX CONCURRENTLY so the build does not hold an ACCESS EXCLUSIVE lock that blocks writes for the duration (the lab uses a plain CREATE INDEX because the throwaway database has no concurrent traffic); a failed CONCURRENTLY build leaves an INVALID index that you simply DROP and retry. Building a GIN index does consume CPU, IO, and maintenance_work_mem and produces an on-disk structure (4680 kB for jsonb_ops, 3696 kB for jsonb_path_ops in the lab) that must be kept up to date on every write, so the only ongoing 'cost' to weigh is write amplification and disk, not correctness. No global server setting is changed, no ALTER SYSTEM or ALTER ROLE is used, and no schema is altered beyond adding (and optionally dropping) an index, so there is nothing to undo beyond dropping the index if you decide the write overhead is not worth it.
📦 Version Matrix (PG 14--18)
The same trap and the same fix on every supported major version -- the plan-shape flip (Seq Scan -> Bitmap Index Scan) is structural, not version-specific.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=Seq Scan | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=Seq Scan | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=Seq Scan | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=Seq Scan | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=Seq Scan | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why is my jsonb @> query reading the whole table when it only returns a few rows?
Because there is no GIN index on the jsonb column. The containment operator @> (like the key-exists operators ? ?& ?| and the jsonpath operators @@ @?) can only be answered by a GIN index. With no GIN index the planner has no access path for @> and must fall back to a Sequential Scan that reads every row and re-checks the filter -- in the lab, 300,000 rows scanned at cost 6848.74 with 99900 rows removed by the filter per worker, just to return 300 rows. The query is correct; it is simply O(table) until you add the index.
I already have an index on the column -- the primary key -- why doesn't it help?
The primary key is a B-tree index, and a B-tree cannot serve jsonb containment. A B-tree orders rows by a scalar key and answers equality and range predicates on that key; it has no representation that can answer 'does this document contain this sub-document?'. Listing the indexes on the table shows only 'docs_pkey ... USING btree (id)', which is why the @> query still Seq Scans. You need an index built with USING gin on the jsonb column.
How do I fix it?
Create a GIN index on the jsonb column: CREATE INDEX docs_data_gin ON docs USING gin (data). In the lab the same query immediately switches from a Parallel Seq Scan (cost 6848.74, all 300,000 rows) to a Bitmap Index Scan on the GIN index (cost 134.57, reading Heap Blocks: exact=300) -- about a 51x drop in estimated cost. On a large, busy table add CONCURRENTLY (CREATE INDEX CONCURRENTLY ...) so the build does not block writes.
Do I need one GIN index per JSON key I query on?
No. A single GIN index on the whole jsonb document serves arbitrary containment keys. The recipe proves the one docs_data_gin index answers both data @> '{"status":"archived"}' and data @> '{"owner":"user5″}', because GIN (the default jsonb_ops opclass) indexes every key and value in each document. Create one GIN index on the column for containment queries; reserve per-key expression indexes for cases where you always filter one fixed scalar field with equality.
What is jsonb_path_ops and should I use it?
jsonb_path_ops is a GIN operator class purpose-built for the containment operator @> (and the jsonpath operators). It stores a single hash per path-to-value instead of separate key and value entries, so the index is materially smaller and cheaper to maintain -- 3696 kB vs 4680 kB for the default jsonb_ops in the lab -- and the query still plans as a Bitmap Index Scan. The trade-off: jsonb_path_ops does NOT support the key-exists operators ?, ?&, ?|. If your workload only uses @> containment, jsonb_path_ops is the right-sized default; if you also test for key existence with ?, use the default jsonb_ops.
How do I stop this from happening again?
Treat 'every jsonb column queried with @> must have a GIN index' as an invariant and check it automatically. The premium scan in this recipe enumerates every jsonb/json column from the catalog (pg_class/pg_attribute/pg_type) and reports whether a GIN index covers it (pg_index/pg_am), correctly flagging an uncovered column while leaving an already-indexed one alone. Run it as a CI guard -- it returns a count of uncovered jsonb columns (0 when everything is covered) -- so a new unindexed jsonb column cannot ship a Seq Scan to production unnoticed.
References
- JSON Types -- jsonb and its operators
- JSON Indexing -- jsonb_ops vs jsonb_path_ops and which operators each supports
- GIN Indexes -- the access method behind jsonb containment
- Built-in Operator Classes -- jsonb_ops and jsonb_path_ops for GIN
- JSON Functions and Operators -- @>, ?, ?&, ?|, @@, @?
- CREATE INDEX -- USING gin and CONCURRENTLY
- Bitmap Scans -- how a GIN index feeds a Bitmap Heap Scan
- Using EXPLAIN -- reading Seq Scan vs Bitmap Index Scan and Rows Removed by Filter
- pg_indexes -- listing the indexes on a table