GIN index misuse on JSONB

GIN index misuse on JSONB
Type: incident simulation · Topic: Query Performance · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-01/07-gin-index-misuse

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the query filters a JSONB column with @> (containment), but there
was no index that supports that operator. A plain B-tree on a jsonb column is
useless for @>, so the planner did a full Seq Scan on every search.

How it was found: pg_stat_statements surfaced the slow query; EXPLAIN showed a
Seq Scan with a Filter on the @> predicate.

The fix: CREATE INDEX idx_events_payload_gin
ON events USING gin (payload jsonb_path_ops);
GIN indexes are built for containment/existence operators on JSONB. jsonb_path_ops
is a compact opclass specialised for @>; the plan switches to a Bitmap Index Scan.

Lesson: an index only helps if it matches the operator and query shape. "Add a
GIN index" isn't enough — pick the right access method (GIN for JSONB @>) and
the right operator class. A B-tree on JSONB looks like a fix but the planner
ignores it.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The slow query filters JSONB with the containment operator @>. A normal B-tree index on a jsonb column can't serve @> — EXPLAIN shows a Seq Scan.
2. JSONB containment needs a GIN index. The operator class matters: jsonb_path_ops is compact and optimised for @>.
3. CREATE INDEX ... ON events USING gin (payload jsonb_path_ops); then EXPLAIN shows a Bitmap Index Scan over the GIN index.