PostgreSQL JSONB Index Guide: GIN Operator Classes vs Expression Indexes

How GIN indexes JSONB and which queries it can serve

A GIN (Generalized Inverted Index) on a jsonb column does not store documents. It breaks each document into items and maps every item to the rows that contain it. With the default operator class, jsonb_ops, keys and values become separate entries. Each entry points to a posting list of heap tuple IDs:

Copy heap rows                              GIN entries -> posting list
 (0,1) {"status":"paid","tier":"pro"}   key   "status" -> (0,1) (0,2) (0,3)
 (0,2) {"status":"open","tier":"pro"}   key   "tier"   -> (0,1) (0,2)
 (0,3) {"status":"paid"}                value "paid"   -> (0,1) (0,3)
                                        value "open"   -> (0,2)
                                        value "pro"    -> (0,1) (0,2)

This layout answers "which rows contain these items?" A containment query (@>) such as {"status":"paid"} looks up the entries for status and paid, intersects their posting lists, and gets candidate rows (0,1) and (0,3). The executor then rechecks each candidate against the actual document, because the entries do not record which key a value belongs to. Key-existence operators (?, ?|, ?&) are a single lookup. Jsonpath matches (@?, @@) work when the path can be reduced to the same kind of lookups.

The index cannot serve amount > 100 or ORDER BY created_at. The jsonb operator classes define no range operators, and GIN cannot return rows in any sort order.

That is the central point of this article: the index you choose depends on the operators your queries use, not on the column's type. If you are a backend or platform engineer keeping semi-structured data in jsonb, check your query shapes before you add an index.

Copy HEAP (table rows)                    GIN INDEX on doc (jsonb_ops)
+--------------------------------+   +--------------------------------------+
| (0,1) {"status":"paid",        |   | entry            posting list       |
|        "tier":"pro"}           |   |--------------------------------------|
| (0,2) {"status":"open",        |<--| key   "status" -> (0,1) (0,2) (0,3)  |
|        "tier":"pro"}           |<--| key   "tier"   -> (0,1) (0,2)        |
| (0,3) {"status":"paid"}        |<--| value "paid"   -> (0,1) (0,3)        |
+--------------------------------+   | value "open"   -> (0,2)              |
                                     | value "pro"    -> (0,1) (0,2)        |
                                     +--------------------------------------+

  WHERE doc @> '{"status":"paid"}'
        |
        v
  +-----------------------+   +------------------------+   +---------------+
  | 1. lookup entries     |-->| 2. intersect postings  |-->| 3. recheck in |
  |    "status", "paid"   |   |    -> (0,1), (0,3)     |   |    heap docs  |
  +-----------------------+   +------------------------+   +---------------+

  NOT SERVED by this index:
    (doc->>'amount')::numeric > 100   no range operators in jsonb opclasses
    ORDER BY doc->>'created_at'       GIN returns rows in no defined order

A jsonb_ops GIN index splits each document into key and value entries whose posting lists point to heap rows. This lets a containment query intersect posting lists and recheck the candidate rows, but range comparisons and ORDER BY get no help from the index.

jsonb_ops versus jsonb_path_ops

PostgreSQL ships two GIN operator classes for jsonb, and they store different index entries.

The default, jsonb_ops, creates a separate entry for every key and every value in the document. Because keys are stored independently, it can answer key-existence questions (?, ?|, ?&) as well as containment (@>). Create it with CREATE INDEX orders_doc_gin ON orders USING gin (doc);.

jsonb_path_ops creates one entry per value: a hash of that value together with the path of keys leading to it. Create it with CREATE INDEX orders_doc_path ON orders USING gin (doc jsonb_path_ops);. A containment query such as doc @> '{"status": "shipped"}' becomes a lookup of a single hash. With jsonb_ops, the same query has to intersect a posting list for the key with a posting list for the value. Hashed path entries are usually more selective, and the index is often smaller.

jsonb_path_ops has real weaknesses. Because it indexes values, it cannot serve a query that only asks whether a key is present. Containment on a very common value, such as {"active": true}, matches most of the table. In that case the planner will usually prefer a sequential scan anyway. Containment on empty or shallow structures, such as {"tags": []}, produces no useful hash entry, so the index scan has to visit every row.

The size difference depends on how many distinct keys your documents have, how deeply they nest and how long the values are. Build both indexes on a copy of production data and compare them with pg_relation_size rather than trusting a ratio.

Copy-- Build both operator classes on a copy of production data.
CREATE INDEX orders_doc_gin  ON orders USING gin (doc);                 -- jsonb_ops (default)
CREATE INDEX orders_doc_path ON orders USING gin (doc jsonb_path_ops);

-- Refresh statistics so the planner sees the real data distribution.
ANALYZE orders;

-- Compare on-disk size directly instead of trusting a ratio.
SELECT c.relname AS index_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relname IN ('orders_doc_gin', 'orders_doc_path');

-- Containment: both indexes can serve this.
EXPLAIN SELECT id FROM orders WHERE doc @> '{"status": "shipped"}';

-- Key existence: only jsonb_ops can serve this.
-- If orders_doc_gin is dropped, this falls back to a sequential scan.
EXPLAIN SELECT id FROM orders WHERE doc ? 'refund_id';

-- Very common value: the planner may choose a sequential scan with either index.
EXPLAIN SELECT id FROM orders WHERE doc @> '{"active": true}';

-- Keep only the index your queries need, for example if you never use ?, ?| or ?&:
-- DROP INDEX orders_doc_gin;

Creates a jsonb_ops index and a jsonb_path_ops index on the same column, compares their sizes with pg_relation_size, and uses EXPLAIN to check which queries each index can serve.

Why operator matching decides whether the index is used

A GIN index on data cannot serve every predicate that mentions data. The planner considers an index only when a WHERE clause has the form indexed_column operator constant and that operator belongs to the index's operator class. data->>'status' = 'active' does not have that form. The ->> operator extracts a text value, and the = that follows compares two text values. Neither jsonb_ops nor jsonb_path_ops contains text equality, so the planner has no index path. EXPLAIN shows a Seq Scan on events with a Filter: ((data ->> 'status') = 'active') line.

Containment, written as data @> '{"status":"active"}', asks for the same rows. Both GIN operator classes support @>. The plan becomes a Bitmap Index Scan on the GIN index that feeds a Bitmap Heap Scan with Recheck Cond: (data @> '{"status": "active"}'::jsonb).

The recheck exists because GIN returns candidate rows, not confirmed matches. jsonb_path_ops stores hashes, so collisions are possible. jsonb_ops indexes keys and values without fully recording their structure. A candidate can also be rechecked when the bitmap overflows work_mem and becomes lossy, which EXPLAIN ANALYZE reports as lossy= in the Heap Blocks line. In each case the heap tuple is tested again against the original condition.

EXPLAIN ANALYZE reports the cost of that recheck. In an illustrative example, not a measured result, a plan shows rows=1200 and Rows Removed by Index Recheck: 48000. This means the index narrowed the table poorly and most heap fetches were wasted work. A small removed count relative to returned rows means the index is doing its job.

Copy-- One GIN index, two predicates that ask for the same rows.
CREATE INDEX events_data_gin ON events USING gin (data jsonb_path_ops);

-- 1) Extract-then-compare: text = text is not in the operator class.
EXPLAIN SELECT * FROM events WHERE data->>'status' = 'active';
--  Seq Scan on events
--    Filter: ((data ->> 'status'::text) = 'active'::text)

-- 2) Containment: @> is in both jsonb_ops and jsonb_path_ops.
EXPLAIN SELECT * FROM events WHERE data @> '{"status":"active"}';
--  Bitmap Heap Scan on events
--    Recheck Cond: (data @> '{"status": "active"}'::jsonb)
--    ->  Bitmap Index Scan on events_data_gin
--          Index Cond: (data @> '{"status": "active"}'::jsonb)

-- 3) Measure what the recheck costs. Numbers below are an ILLUSTRATIVE
--    example, not a measured result; costs and timings omitted.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE data @> '{"status":"active"}';
--  Bitmap Heap Scan on events (actual rows=1200 loops=1)
--    Recheck Cond: (data @> '{"status": "active"}'::jsonb)
--    Rows Removed by Index Recheck: 48000   -- candidates that failed the heap test
--    Heap Blocks: exact=900 lossy=3100      -- lossy>0: bitmap exceeded work_mem
--    ->  Bitmap Index Scan on events_data_gin (actual rows=... loops=1)
--
-- Read it as: 1200 rows kept vs 48000 removed means the index narrowed
-- the table poorly. With lossy blocks present, retry with more work_mem
-- before blaming the index:
SET work_mem = '64MB';
-- Re-run the EXPLAIN ANALYZE above; if lossy drops to 0 and the removed
-- count falls, the waste came from the lossy bitmap, not the index.
-- On a small test table the planner may still pick a Seq Scan for (2);
-- that is a cost decision, not a missing index path.

Against the same GIN index, data->>'status' = 'active' gets a sequential scan while data @> '{"status":"active"}' gets a Bitmap Index Scan feeding a Bitmap Heap Scan, and EXPLAIN ANALYZE's recheck lines (illustrative numbers) show how well the index narrowed the rows.

Expression, partial and targeted indexes for hot fields

When a few fields dominate the query load, a narrow index usually beats a whole-document GIN index. A B-tree on ((data->>'customer_id')) supports equality, range predicates, ORDER BY and merge joins. GIN supports none of these except a form of equality through containment. The B-tree is also smaller, because it stores one key per row instead of one entry per key or path in the document.

The planner uses an expression index only when the query contains the same expression. data->>'customer_id' = '42' matches. data->'customer_id' = '42' does not, because -> returns jsonb, not text. Casts follow the same rule. An index on ((data->>'amount')::numeric) serves (data->>'amount')::numeric > 100 but not data->>'amount' > '100', which is a text comparison and gives wrong results anyway. The cast also runs on every insert and update, so a single non-numeric amount value causes the write to fail.

A partial index restricts the index to the rows you actually query. For example, an index on ((data->>'customer_id')) with WHERE data->>'status' = 'open' skips closed rows. The planner uses it only if the query's predicate implies the index predicate. For array-valued fields, a GIN index on (data->'tags') serves data->'tags' @> '["urgent"]' without indexing the rest of the document. It does not serve data @> '{"tags":["urgent"]}'.

These indexes are brittle. When an ORM upgrade or a refactor changes ->> to -> or drops a cast, the index stops being used and nothing reports an error. Add tests that run EXPLAIN on critical queries and assert that the expected index name appears in the plan. Small test tables can lead the planner to choose a sequential scan regardless of indexes, so set enable_seqscan = off in those test sessions.

CopyCREATE TABLE orders (id bigserial PRIMARY KEY, data jsonb NOT NULL);

-- B-tree on the text value: supports =, ranges, ORDER BY and joins
CREATE INDEX orders_customer_id_idx ON orders ((data->>'customer_id'));
-- Typed expression: the cast runs on every write, so a non-numeric amount fails the INSERT/UPDATE
CREATE INDEX orders_amount_idx ON orders (((data->>'amount')::numeric));
-- Partial: index only open orders, the rows we actually query
CREATE INDEX orders_open_customer_idx ON orders ((data->>'customer_id'))
  WHERE data->>'status' = 'open';
-- GIN on the sub-document only, not the whole column
CREATE INDEX orders_tags_idx ON orders USING gin ((data->'tags'));

-- Can use the indexes: the expressions match exactly
SELECT id FROM orders WHERE data->>'customer_id' = '42';
SELECT id FROM orders WHERE (data->>'amount')::numeric > 100;
SELECT id FROM orders WHERE data->>'status' = 'open' AND data->>'customer_id' = '42';
SELECT id FROM orders WHERE data->'tags' @> '["urgent"]';

-- Cannot use them: different operator, missing cast, whole-column containment
SELECT id FROM orders WHERE data->'customer_id' = '"42"';   -- -> returns jsonb
SELECT id FROM orders WHERE data->>'amount' > '100';          -- text comparison, also wrong
SELECT id FROM orders WHERE data @> '{"tags":["urgent"]}';  -- not the (data->'tags') expression

-- Regression test: fail if the critical query stops using its index
SET enable_seqscan = off;  -- small test tables would otherwise favor seq scans
DO $$
DECLARE
  line text;
  plan text := '';
BEGIN
  FOR line IN EXECUTE $q$EXPLAIN SELECT id FROM orders WHERE data->>'customer_id' = '42'$q$ LOOP
    plan := plan || line || E'\n';
  END LOOP;
  IF plan NOT LIKE '%orders_customer_id_idx%' THEN
    RAISE EXCEPTION 'expected orders_customer_id_idx, got plan:%', E'\n' || plan;
  END IF;
END $$;

Expression, cast, partial and sub-document GIN indexes on a jsonb column, the query shapes that can and cannot use them, and an EXPLAIN-based test that fails when a query stops using its index.

Planner statistics and row estimates inside JSONB

PostgreSQL's ANALYZE treats a jsonb column as one opaque value. It records statistics about whole documents, such as most common values and histogram bounds, but nothing about the distribution of individual keys. When a query filters on payload ->> 'status' = 'failed' or payload @> '{"region": "eu"}', the planner has no data about how often that key holds that value. It falls back to built-in default selectivities.

Those defaults are fixed fractions of the table. They are wrong whenever real data is skewed, and JSONB data usually is. A status that matches most rows and one that matches almost none get the same estimate. The estimate then drives join order, join method and the choice between index and sequential scans. A tenfold misestimate on a filter can turn a hash join into a nested loop that runs for minutes.

Giving the planner real numbers

An expression index on (payload ->> 'status') fixes this as a side effect. After the next ANALYZE, PostgreSQL stores statistics for the indexed expression, and the planner uses them for any predicate that matches the expression exactly. You get the improved estimates even when the index itself is not chosen for the scan.

If you want the statistics without the write cost of an index, newer PostgreSQL releases support extended statistics on expressions through CREATE STATISTICS. Confirm that your version supports this before you rely on it.

Checking estimates

Run EXPLAIN ANALYZE and compare rows= in the estimated cost section with rows= in the actual section on each node. A gap of an order of magnitude or more on a JSONB filter means the planner is guessing.

When to avoid JSONB indexes, and the alternatives

A whole-column GIN index is a poor default on write-heavy tables. Updating a JSONB value writes a new row version, and GIN then inserts an entry for every key and value (jsonb_ops) or every path hash (jsonb_path_ops) in the new document, even if only one field changed. Because the indexed column changed, the update also cannot be a heap-only tuple (HOT) update.

fastupdate reduces the immediate cost by appending new entries to an unsorted pending list instead of placing them in the main index. That list is merged during vacuum, during autoanalyze, by an explicit gin_clean_pending_list() call, or when it outgrows gin_pending_list_limit. In the last case, the write that crosses the limit pays for the whole merge. Every index scan also has to read the pending list sequentially. The result is lower average write latency, but occasional slow writes and reads that are hard to predict.

Expression indexes are not free either. Each one adds work to every insert and non-HOT update, and adds work to every vacuum. If filters span dozens of fields that change from one request to the next, a single GIN index costs less than a large set of expression indexes.

When a field is stable and filtered often, promote it to a typed column or a stored generated column. That gives you an ordinary B-tree, NOT NULL and CHECK constraints, foreign keys, and per-column statistics. If your core queries depend on JSONB indexes, the data is probably relational and should be modelled that way.

A decision procedure and its effect on cost and engineering time

Run the same four steps for every JSONB column:

1. Inventory query shapes. Pull the real predicates from application code and from pg_stat_statements, not from what the schema suggests.
2. Map each predicate to its operator. For example, @>, ?, ?|, @? or ->> with =. Only the operator determines which index can serve the query.
3. Pick the index and operator class. Choose the one whose operator support covers those operators, and nothing broader.
4. Verify with EXPLAIN ANALYZE. Use production-like row counts and skew. A few thousand uniform rows fit in cache, so sequential scans look cheap and selectivity looks even. That can hide both a useless index and a missing one.

Every unneeded GIN index still has to absorb each write, through pending-list cleanup or direct insertion. It also adds vacuum work and takes disk space. An index that matches its operators is tuned once, which avoids repeated tuning cycles.

Two habits shorten debugging. First, add a CI check that runs EXPLAIN on key queries against a seeded, skewed dataset and fails when the plan type changes. Second, promote fields to real columns as soon as they appear in joins or range filters.

Illustrative estimate (assumed numbers, not a benchmark):

hours saved per year = regressions × (production debug hours − CI fix hours)

Assume 4 regressions a year, 6 hours each to diagnose in production, and 1 hour each to fix when caught in CI:

4 × (6 − 1) = 20 engineer-hours saved per year.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql jsonb index, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Consumers learn about a breaking change at review time rather than from production errors.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.