Postgres JSONB Indexing: GIN vs Expression B-Tree
GIN answers containment, not equality, and an expression B-tree is the reverse. Choose by operator, match the expression exactly, and prove it with EXPLAIN.

Introduction & Quick Summary
Symptom. A jsonb column holds application documents, the dashboard query filters on one field, and EXPLAIN shows a sequential scan over millions of rows. Adding a GIN index makes containment queries fast but leaves WHERE data->>'status' = 'paid' ORDER BY created_at as slow as before, and adding an expression B-tree helps that one query while the @> search degrades. Some queries never use any index, and nobody can tell which predicate the planner disliked.
Cause. A GIN index on jsonb indexes keys and values for the containment and existence operators (@>, <@, ?, ?&, ?|, @?, @@). It cannot answer equality or range comparisons on a single extracted scalar, because there is no ordered structure to walk. An expression B-tree on (data->>'status') is the opposite: perfect for equality, range and sorting, useless for containment. A third failure mode is a type or expression mismatch — data->>'total' is text, so comparing it to the number 100 either fails to match the index or silently compares strings.
Fix. Pick the index from the operator, not from the column. Containment and key existence go to GIN (jsonb_path_ops when you only need @>). Equality, range and ordering on one field go to an expression B-tree, ideally composite with the tenant or foreign key and covering the sort. Mixed workloads use both, with a partial GIN index restricted to the rows that need it, and every candidate is proven with EXPLAIN (ANALYZE, BUFFERS) before and after.
Core Mechanics, Signatures & Reproducible Setup
Reproduce the two shapes with a table that matches production:
CREATE TABLE events (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
data jsonb NOT NULL
);
INSERT INTO events (tenant_id, created_at, data)
SELECT gen_random_uuid(), now() - (g || ' minutes')::interval,
jsonb_build_object('status', (ARRAY['paid','pending','failed'])[1 + g % 3],
'total', (g % 500)::numeric,
'tags', jsonb_build_array('web', 'checkout'),
'meta', jsonb_build_object('country', 'KR'))
FROM generate_series(1, 200000) g;
ANALYZE events;Now compare the two index families on the two operator families:
-- containment: GIN wins
CREATE INDEX events_data_gin ON events USING gin (data jsonb_path_ops);
SELECT count(*) FROM events WHERE data @> '{"status":"paid"}';
-- scalar equality and range: expression B-tree wins
CREATE INDEX events_status_btree ON events (tenant_id, (data->>'status'), created_at DESC);
SELECT id, created_at FROM events
WHERE tenant_id = $1 AND data->>'status' = 'paid'
ORDER BY created_at DESC LIMIT 50;The GIN index serves the first query through the @> operator; the B-tree serves the second through an index scan that also satisfies the ORDER BY. Neither can replace the other. Note the exact expression in the index: (data->>'status') must appear in the WHERE clause character-for-character, because the planner matches expressions structurally.
jsonb_ops and jsonb_path_ops differ in what the index can answer:
CREATE INDEX events_gin_ops ON events USING gin (data);
CREATE INDEX events_gin_path ON events USING gin (data jsonb_path_ops);jsonb_path_ops indexes hashes of whole paths with values, so it is smaller and faster for @> but cannot answer ? (key exists), ?|, ?&, or @?/@@ JSON-path predicates. Start with jsonb_path_ops and add the other class only for queries that need it.
Key Comparison Table
| Predicate | GIN jsonb_ops | GIN jsonb_path_ops | Expression B-tree | Seq scan |
|---|---|---|---|---|
data @> '{"k":1}' | Yes | Yes (best) | No | Yes |
data ? 'k' | Yes | No | No | Yes |
data @? '$.a.b' | Yes | No | No | Yes |
data->>'k' = 'v' | No | No | Yes | Yes |
(data->>'n')::int > 10 | No | No | Yes, with matching cast | Yes |
ORDER BY created_at with status filter | No | No | Yes (composite) | Sort |
| Index size for wide documents | Large | ~40–60% of jsonb_ops | Small | None |
| Need | Index | Notes |
|---|---|---|
| One field, equality and sort | btree (tenant_id, (data->>'status'), created_at DESC) | Best for dashboards |
| One field, range on numbers | btree (((data->>'total')::numeric)) | Cast must match the query |
| Arbitrary containment | gin (data jsonb_path_ops) | Smallest for @> only |
| Key existence | gin (data) | ?, `? |
| Hot subset only | Partial index with WHERE | Smaller, must match the predicate |
| Full document fetch by id | Primary key | Do not put the document in the index |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. The index exists but the query does not use it
Expression indexes are matched on the parsed expression tree, so any wrapper defeats them: lower(data->>'status'), data->>'Status', or coalesce(data->>'status','x'). Check with EXPLAIN and compare the printed expression with the index definition:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE (data->>'status') = 'paid';If the plan shows Seq Scan while the index exists, the expression or the type differs. data->>'total' = '100' on a text extraction matches '100' exactly; (data->>'total')::int = 100 matches only a matching cast index.
2. Casts that cannot be indexed
Only IMMUTABLE functions may appear in an index expression. now(), current_date, and timezone(text, timestamptz) are stable, not immutable, so this fails:
-- ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON events ((timezone('UTC', (data->>'created')::timestamptz)));Store the value as timestamptz inside JSON and cast directly, or add a generated column with an immutable expression and index that.
3. Generated columns as an alternative to expression indexes
A stored generated column gives the planner a real column, which reads better in ORMs and can join other indexes:
ALTER TABLE events
ADD COLUMN status text GENERATED ALWAYS AS (data->>'status') STORED;
CREATE INDEX events_status_created ON events (tenant_id, status, created_at DESC);Changing the expression later requires dropping and re-adding the column, which rewrites the table; on a large table do it in a maintenance window.
4. GIN pending list and bulk loads
GIN writes go to a pending list (fastupdate = on by default) that is flushed on commit when it exceeds gin_pending_list_limit (4 MB). Right after a bulk load, queries scan the pending list and look slow even though the index is correct. Either index after the load, or force a flush:
SELECT gin_clean_pending_list('events_data_gin');Check the pending list size with pg_stat_user_indexes (idx_tup_read) or pg_relation_size of the _pending relation.
5. Mixed tenant and containment filters
A report that filters by tenant and containment benefits from a partial index when the containment predicate is narrow and selective:
CREATE INDEX events_paid_gin ON events USING gin (data jsonb_path_ops)
WHERE data @> '{"status":"paid"}';The planner uses it only when the query contains a predicate that implies the WHERE clause. A partial index on a condition that most rows satisfy wastes space.
6. Reading a long query plan without losing the shape
Complex JSONB reports grow into nested CTEs that are impossible to read inline. Format first, then run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) and compare estimated rows with actual rows per node; a factor of 100 between them is why the planner chose a nested loop.
WITH paid AS (
SELECT tenant_id, created_at, (data->>'total')::numeric AS total
FROM events
WHERE tenant_id = $1 AND data @> '{"status":"paid"}'
)
SELECT date_trunc('day', created_at) AS day, sum(total) AS revenue
FROM paid GROUP BY 1 ORDER BY 1 DESC;Paste the inner query into the SQL formatter when the CTE levels get deep; formatting does not change the plan, but it makes the predicate that broke the index visible.
Common Compilation/Runtime Mistakes & Version Gotchas
- One GIN index for everything. It answers
@>, not=. Adding a second GIN index of the other op class rarely helps; add the B-tree instead. - Comparing text to numbers.
data->>'total' > 100casts text to integer implicitly per row and cannot use a numeric index. Always cast explicitly with the same expression the index has. jsoninstead ofjsonb. Thejsontype stores text, has no GIN support for containment, and cannot be indexed the same way. Convert withALTER TABLE … USING data::jsonb.jsonb_path_opsplus?. The index is silently unusable for key-existence queries; the planner falls back to a scan with no warning.CREATE INDEXlocking writes. On a busy table useCREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and can leave an invalid index that needs dropping and retrying.- Duplicate keys collapse.
jsonbkeeps the last duplicate key, so a document built by concatenation silently loses values; validate at insert time when the producer is untrusted. - Index bloat from updates. Updating one key rewrites the whole
jsonbvalue and creates a new index entry. High-churn fields belong in real columns, with the document holding the long tail.
Related tools. Format and lint the EXPLAIN-driven report queries with the SQL formatter, convert a jsonb document and its flattened columns back and forth with the CSV/JSON converter, and pretty-print a single document to see the nesting depth that decides between jsonb_ops and jsonb_path_ops with the JSON formatter.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesPostgres Deadlock Detected 40P01: Reading pg_locks and Lock Order
The executor locks rows in plan order, not the order you listed them. A consistent lock order, the pg_locks query that proves the cycle, and a retry that works.
Postgres too many clients: Pool Sizing and Connection Exhaustion
Each process caches its own pool and nothing coordinates them. Divide the ceiling by replicas, put a transaction-mode pooler in front, and stop idle-in-transaction sessions from holding slots.
Postgres RLS 42P17: infinite recursion detected in policy
A policy on members that reads members re-enters itself. One security definer function with a pinned search_path ends the recursion and leaves the rest of the policy intact.
SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.