Why didn't the B-tree index on metadata get used at all?

A B-tree index supports equality and ordering comparisons (=, , BETWEEN) — the @> containment operator isn't any of those, so the planner has no way to use a B-tree for it and falls back to scanning every row. The index wasn't broken; it was simply the wrong index type for this operator.

What does the @> operator actually check?

It tests whether the left-hand JSONB value contains the right-hand one as a sub-structure — {"rating": 4.5, "pages": 300} @> {"rating": 4.5} is true because every key/value pair on the right appears in the left. It works on nested objects and arrays, not just top-level keys.

Is GIN only useful for JSONB?

No — GIN (Generalized Inverted Index) is Postgres's general-purpose index type for values containing multiple component values that need to be searched independently: JSONB keys, array elements, and full-text search's lexemes (tsvector) all use GIN for the same underlying reason — B-tree can't efficiently answer 'does this composite value contain that element.'

Why is the GIN-indexed query still slower than the author B-tree lookup from the indexing-strategy lesson?

GIN indexes trade some lookup speed for the ability to index composite values at all — 9ms here is still a large improvement over the 47ms Seq Scan, but a inverted index over JSON keys is inherently more work per lookup than a B-tree over a single scalar column. Compare against the right baseline (no index) rather than against a different query's best case.

Does GIN support range queries like BETWEEN on a JSONB numeric field?

Not directly and not efficiently — GIN's default jsonb_ops operator class only supports containment and existence (@>, ?, ?|, ?&). For range comparisons on a specific JSONB field, extract it into its own column (or an expression index using ->>) and index that with a regular B-tree instead.

JSONB Containment Queries Need a GIN Index, Not a B-tree

An index that exists and an index that’s actually usable for a given query are two different things — the operator has to match the index type.

The bug: a B-tree index on jsonb, expecting it to help

CREATE INDEX idx_books_metadata_btree ON books(metadata);
EXPLAIN ANALYZE SELECT * FROM books WHERE metadata @> '{"rating": 4.5}';

CREATE INDEX
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..6338.00 rows=4040 width=118) (actual time=0.039..37.210 rows=4062 loops=1)
   Filter: (metadata @> '{"rating": 4.5}'::jsonb)
   Rows Removed by Filter: 195938
 Planning Time: 0.316 ms
 Execution Time: 37.330 ms
(5 rows)

The index was created without error and takes up real disk space — and the plan is still a full Seq Scan, exactly as if no index existed at all. A default B-tree index supports equality and ordering; @> (containment) isn’t either of those, so the planner has no way to use it here.

The fix: GIN, the index type built for containment

DROP INDEX idx_books_metadata_btree;
CREATE INDEX idx_books_metadata ON books USING GIN(metadata);
EXPLAIN ANALYZE SELECT * FROM books WHERE metadata @> '{"rating": 4.5}';

DROP INDEX
CREATE INDEX
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=46.81..4090.54 rows=4040 width=118) (actual time=2.730..8.954 rows=4062 loops=1)
   Recheck Cond: (metadata @> '{"rating": 4.5}'::jsonb)
   Heap Blocks: exact=2530
   ->  Bitmap Index Scan on idx_books_metadata  (cost=0.00..45.80 rows=4040 width=0) (actual time=2.495..2.496 rows=4062 loops=1)
         Index Cond: (metadata @> '{"rating": 4.5}'::jsonb)
 Planning Time: 1.090 ms
 Execution Time: 9.202 ms
(7 rows)

37.3ms down to 9.2ms — roughly 4x, using USING GIN(metadata) instead of the default B-tree. GIN (Generalized Inverted Index) stores an entry per JSON key/value pair pointing back to the rows containing it, which is what makes a containment check fast: look up the specific key/value, not the whole document.

Practice

1. Query for an array element inside the JSONB tags field, and notice the GIN index doesn't even get used.

EXPLAIN ANALYZE SELECT * FROM books WHERE metadata @> '{"tags": ["tag3"]}';

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..6338.00 rows=38384 width=118) (actual time=0.030..60.986 rows=45714 loops=1)
   Filter: (metadata @> '{"tags": ["tag3"]}'::jsonb)
   Rows Removed by Filter: 154286
 Planning Time: 3.116 ms
 Execution Time: 62.971 ms
(5 rows)

The GIN index exists and supports this exact operator — and the planner still chose a Seq Scan. tag3 matches roughly 23% of rows, which is the same low-selectivity problem from the indexing-strategy lesson: the planner estimated a full scan was cheaper than the index-then-recheck dance for a match this common, GIN or not. A capable index type doesn’t override a bad selectivity estimate.

2. Use the ? existence operator to find documents that merely have a key, regardless of value.

EXPLAIN ANALYZE SELECT * FROM books WHERE metadata ? 'rating';

                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..6338.00 rows=199980 width=118) (actual time=0.006..31.849 rows=200000 loops=1)
   Filter: (metadata ? 'rating'::text)
 Planning Time: 0.387 ms
 Execution Time: 36.262 ms
(4 rows)

Every row has a rating key (it’s part of every generated document), so this matches all 200,000 rows. Zero selectivity gain possible, so the planner doesn’t even attempt the index — the same theme as the previous item, taken to its logical extreme.

3. Extract a JSONB field into an expression index for a range comparison GIN can't do directly.

CREATE INDEX idx_books_rating ON books(((metadata->>'rating')::numeric));
EXPLAIN ANALYZE SELECT * FROM books WHERE (metadata->>'rating')::numeric > 4.8;

CREATE INDEX
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=1257.09..6428.43 rows=66667 width=118) (actual time=0.831..5.028 rows=5985 loops=1)
   Recheck Cond: (((metadata ->> 'rating'::text))::numeric > 4.8)
   Heap Blocks: exact=3050
   ->  Bitmap Index Scan on idx_books_rating  (cost=0.00..1240.42 rows=66667 width=0) (actual time=0.565..0.565 rows=5985 loops=1)
         Index Cond: (((metadata ->> 'rating'::text))::numeric > 4.8)
 Planning Time: 0.375 ms
 Execution Time: 5.220 ms
(7 rows)

A regular B-tree expression index on the extracted, cast value — this is the answer to the FAQ above about range queries: pull the field out with ->> and cast it, then index that expression instead of trying to make GIN do a job it isn’t built for.

Next: VACUUM and bloat — what MVCC leaves behind, and why deleting rows doesn’t shrink a table.

The bug: a B-tree index on jsonb, expecting it to help

The fix: GIN, the index type built for containment

Practice

Frequently Asked Questions