A JSONB column with a GIN index. Author wrote a query using `metadata->>'tag' = $1` and assumed the GIN index would help. EXPLAIN shows seq scan.
The vulnerable diff
-- migrations/add_jsonb_index.sql
CREATE INDEX docs_meta_gin ON docs USING GIN (metadata);
-- search/by-tag.ts query:
SELECT * FROM docs WHERE metadata->>'tag' = 'release';
-- EXPLAIN: Seq Scan on docs (rows=3,000,000)
-- index unused because the query shape doesn't match the operator class.What is wrong
Postgres GIN indexes on JSONB support specific operators: containment (`@>`), existence (`?`, `?|`, `?&`), and JSON path (`@?`, `@@`). They do not support the extract-and-compare pattern (`->>` and `->` followed by `=`). The fix is either to rewrite the query to use a containment shape (`metadata @> '{"tag":"release"}'::jsonb`), or to create a btree index on the specific JSONB extraction (`CREATE INDEX ON docs ((metadata->>'tag'))` — useful if you have a small number of well-known JSON keys).
The attack
EXPLAIN comparison:
-- Query with GIN index, wrong shape
EXPLAIN ANALYZE SELECT * FROM docs WHERE metadata->>'tag' = 'release';
-- Seq Scan on docs (rows=3,000,000, total time: 4,800 ms)
-- Rewrite to containment
EXPLAIN ANALYZE SELECT * FROM docs WHERE metadata @> '{"tag":"release"}'::jsonb;
-- Bitmap Index Scan using docs_meta_gin (rows=120, total time: 1.8 ms)
-- Or with a btree expression index
CREATE INDEX docs_meta_tag ON docs ((metadata->>'tag'));
EXPLAIN ANALYZE SELECT * FROM docs WHERE metadata->>'tag' = 'release';
-- Index Scan using docs_meta_tag (rows=120, total time: 0.9 ms)2000× speedup either way; the choice between containment and expression-index depends on whether you query by many JSON keys or a small fixed set.
Mesrai's review comment
mesraipilot · Bot · reviewed 90 sec ago
[mesrai] [code-review] [Performance] [JSONB-Index] [medium]
GIN on `metadata` does not help `metadata->>'tag' = ...`. The index
supports containment (@>), existence (?), and path operators — not
extract-and-compare.
Two fixes, pick by query pattern:
1. Rewrite to containment (uses GIN index as-is):
SELECT * FROM docs WHERE metadata @> jsonb_build_object('tag', $1::text);
2. Add a btree expression index for that specific key:
CREATE INDEX docs_meta_tag ON docs ((metadata->>'tag'));
-- query unchanged: metadata->>'tag' = $1
Use containment when you query by varying JSON shapes. Use expression
index when you query by a small set of well-known keys.
For `jsonb_path_ops`-style indexes (smaller, faster, query-shape
restricted), see Postgres docs on GIN operator classes for JSONB.The fix
-- search/by-tag.ts — fixed (option 1: containment)
SELECT * FROM docs WHERE metadata @> jsonb_build_object('tag', $1::text);
-- search/by-tag.ts — fixed (option 2: expression index)
CREATE INDEX docs_meta_tag ON docs ((metadata->>'tag'));
-- query unchanged: SELECT * FROM docs WHERE metadata->>'tag' = $1;Two parallel fixes. Containment uses the existing GIN index. Expression index uses a btree on the specific extracted key. Both are p99 ~1ms. The decision criterion: containment is more general but requires the query to use the `@>` operator; expression-index keeps the original query shape but requires one index per key.
Why human review missed it
JSONB indexes are a topic most application engineers learn once and forget. The query looks like it should use the index — `metadata` is indexed, the filter is on `metadata`. The operator-class mismatch is silent at the application layer. Mesrai catches it by flagging any `->` / `->>` query on a JSONB column where a GIN index exists.
Related rules + further reading
Mesrai rule pack: performance/jsonb-index-shape — flags JSONB queries whose shape does not match the available index operator class.
Postgres docs: JSONB operator classes.
Common in event-store and document-DB-on-Postgres patterns.
Takeaway
GIN indexes support specific operators. Extract-and-compare needs a different index. Containment works on the existing GIN.