The Index Nobody Uses

An engineer adds a GIN index on a jsonb column and the query that motivated it is unchanged:

CREATE INDEX events_payload_gin ON events USING GIN (payload);

EXPLAIN SELECT id FROM events WHERE payload ->> 'status' = 'failed';
--  Seq Scan on events  (cost=0.00..2841002.00 rows=142000 width=8)
--    Filter: ((payload ->> 'status'::text) = 'failed'::text)

Why is the index never consulted?

New GIN entries sit in the pending list and are invisible to the planner until VACUUM ANALYZE merges them into the main index.

The default jsonb_ops class serves only @>, ?, ?|, ?&, @? and @@. ->> is a function producing text, then = on the result, which no jsonb GIN operator class covers.

GIN indexes are only consulted for highly selective predicates, and an estimate of 142,000 rows is past the point where the planner stops considering one.

It needs the jsonb_path_ops operator class, which is the one that supports value extraction with -> and ->>.