Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#PostgreSQL#JSONB#Indexing#Performance
3D illustration of JSON brace blocks feeding a wide cyan mesh index tower and a slim amber tree tower

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:

sql
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:

sql
-- 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:

sql
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

PredicateGIN jsonb_opsGIN jsonb_path_opsExpression B-treeSeq scan
data @> '{"k":1}'YesYes (best)NoYes
data ? 'k'YesNoNoYes
data @? '$.a.b'YesNoNoYes
data->>'k' = 'v'NoNoYesYes
(data->>'n')::int > 10NoNoYes, with matching castYes
ORDER BY created_at with status filterNoNoYes (composite)Sort
Index size for wide documentsLarge~40–60% of jsonb_opsSmallNone
NeedIndexNotes
One field, equality and sortbtree (tenant_id, (data->>'status'), created_at DESC)Best for dashboards
One field, range on numbersbtree (((data->>'total')::numeric))Cast must match the query
Arbitrary containmentgin (data jsonb_path_ops)Smallest for @> only
Key existencegin (data)?, `?
Hot subset onlyPartial index with WHERESmaller, must match the predicate
Full document fetch by idPrimary keyDo 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:

sql
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:

sql
-- 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:

sql
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:

sql
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:

sql
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.

sql
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' > 100 casts text to integer implicitly per row and cannot use a numeric index. Always cast explicitly with the same expression the index has.
  • json instead of jsonb. The json type stores text, has no GIN support for containment, and cannot be indexed the same way. Convert with ALTER TABLE … USING data::jsonb.
  • jsonb_path_ops plus ?. The index is silently unusable for key-existence queries; the planner falls back to a scan with no warning.
  • CREATE INDEX locking writes. On a busy table use CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and can leave an invalid index that needs dropping and retrying.
  • Duplicate keys collapse. jsonb keeps 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 jsonb value 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.