GIN Indexing for JSONB, Containment Queries, jsonpath, and Selectivity Tradeoffs

Learning outcomes

ServiceHub now has thousands of JSONB work-order profiles. The search workload includes structural containment, top-level key existence, tag membership, and jsonpath predicates. A GIN index is useful only when its operator class matches those operators. “GIN on JSONB” is not one universal index contract.

1. Build representative JSONB documents

DROP TABLE IF EXISTS app.ch17_profile;CREATE TABLE app.ch17_profile (  work_order_id bigint PRIMARY KEY,  document jsonb NOT NULL);INSERT INTO app.ch17_profileSELECT 1700000 + g,       jsonb_build_object(         'region', (ARRAY['north','south','east','west'])[(g % 4)+1],         'status', (ARRAY['queued','assigned','completed'])[(g % 3)+1],         'priority', (g % 5)+1,         'tags', CASE                   WHEN g % 10 = 0 THEN jsonb_build_array('urgent','pump')                   WHEN g % 7 = 0 THEN jsonb_build_array('electrical','inspection')                   ELSE jsonb_build_array('routine')                 END,         'metrics', jsonb_build_object(           'duration', (g * 13) % 240,           'attempts', (g % 4)+1         )       )FROM generate_series(1,20000) AS g;ANALYZE app.ch17_profile;

The dataset is deterministic, but planner choices remain machine/statistics dependent. We use the same data for before/after plans and avoid claiming a universal latency multiplier.

2. Default jsonb_ops: broad operator coverage

CREATE INDEX ch17_profile_document_ops_ginON app.ch17_profile USING GIN (document);SELECT pg_size_pretty(pg_relation_size('app.ch17_profile_document_ops_gin'))       AS jsonb_ops_size;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch17_profileWHERE document @> '{"region":"north","status":"queued"}'::jsonb;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch17_profileWHERE document ? 'priority';

The default jsonb_ops class supports containment @>, top-level existence ?/?|/?&, and jsonpath @?/@@. A small table can still make a sequential scan cheaper; index capability and planner choice are separate questions.

3. jsonb_path_ops: narrower operators, different key representation

CREATE INDEX ch17_profile_document_path_ginON app.ch17_profile USING GIN (document jsonb_path_ops);SELECT  pg_size_pretty(pg_relation_size('app.ch17_profile_document_ops_gin'))  AS ops_size,  pg_size_pretty(pg_relation_size('app.ch17_profile_document_path_gin')) AS path_ops_size;

jsonb_path_ops supports @>, @?, and @@, but not key-existence operators. It creates a hash-like index key from each value plus its key path, which often makes containment searches more specific and the index smaller. It also produces no index entry for value-less structures such as {"a":{}}, making some containment searches poor fits.

4. Prove an unsupported operator instead of assuming

BEGIN;DROP INDEX app.ch17_profile_document_ops_gin;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch17_profileWHERE document ? 'priority';ROLLBACK;

Within that transaction the only whole-document GIN is jsonb_path_ops, which cannot service ?. The planner must find another path—typically a sequential scan. Rolling back restores the default GIN index. This is stronger evidence than looking at one plan while both indexes compete.

5. jsonpath can be indexable—but only when the path yields indexable clauses

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT work_order_idFROM app.ch17_profileWHERE document @@ '$.region == "north" && $.status == "queued"';

For @?/@@, PostgreSQL extracts equality clauses such as an accessor chain equal to a constant and searches GIN keys for those clauses. Not every jsonpath expression becomes an efficient index lookup. Arbitrary arithmetic/range logic may still require rechecks or a scan.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch17_profileWHERE document @? '$.metrics.duration ? (@ > 180)';

If duration thresholds are a dominant query dimension, a typed generated/ordinary column plus B-tree statistics may be a better model than asking GIN/jsonpath to behave like a numeric range index.

6. Expression GIN can target a frequently searched subdocument

CREATE INDEX ch17_profile_tags_ginON app.ch17_profile USING GIN ((document -> 'tags'));EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT work_order_idFROM app.ch17_profileWHERE (document -> 'tags') ? 'urgent';

The expression in the query must match the indexed expression closely enough for the planner to use it. This targeted index can be smaller and cheaper to maintain than indexing every key/value in a large document when the workload only searches tags.

7. Operator semantics before index syntax

SELECT  '{"tags":["urgent","pump"]}'::jsonb     @> '{"tags":["urgent"]}'::jsonb AS contains_urgent,  '{"priority":1}'::jsonb     @> '{"priority":"1"}'::jsonb AS number_is_not_string,  '{"a":{}}'::jsonb     @> '{"a":{}}'::jsonb AS contains_empty_object;

JSONB containment is type- and structure-aware. It is not substring matching. That precision is why GIN can map composite documents to searchable keys.

8. Planner selectivity and write cost

GIN improves read access by expanding each document into index keys/posting lists. That costs storage, WAL, insert/update work, vacuum maintenance, and possibly pending-list behavior. JSONB statistics also cannot understand every arbitrary nested predicate perfectly.

SELECT indexrelid::regclass AS index_name,       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,       idx_scanFROM pg_stat_user_indexesWHERE relid = 'app.ch17_profile'::regclassORDER BY indexrelid::regclass::text;SELECT pg_size_pretty(pg_table_size('app.ch17_profile')) AS table_size,       pg_size_pretty(pg_indexes_size('app.ch17_profile')) AS all_indexes;

9. Checkpoint

Authoritative references

These data types and index/operator contracts are version-sensitive. The lesson uses the PostgreSQL 18 primary documentation below.

• PostgreSQL 18 — JSONB Indexing
• PostgreSQL 18 — GIN Built-in Operator Classes
• PostgreSQL 18 — JSON Functions / jsonpath