GIN Index Misuse on JSONB

A production incident, replayed by hand: the JSONB events search is slow even though somebody already added an index on the payload column. Bring up the same database, catch the query with pg_stat_statements, see why the planner ignores a B-tree on JSONB containment, and fix it with a GIN index and the right operator class.

The analytics search over an events table is painfully slow — and the twist is that an index on the payload column already exists. A previous responder added it, declared victory, and the plan still shows a full scan. This book replays the incident on a database you bring up yourself: the schema, the data and the useless index are all in plain SQL files, so nothing is hidden. You reproduce the pain with a stopwatch, catch the offender with pg_stat_statements, read the plan that pretends the index isn't there, and learn why a B-tree fundamentally cannot serve the JSONB containment operator. The fix is a GIN index with the jsonb_path_ops operator class — and you prove it by watching the plan switch to a Bitmap Index Scan. The diagnosis path is the real one you'd use on call.

What you'll build

• Reproduce a latency incident on a local PostgreSQL stand
• Find the most expensive query with pg_stat_statements
• Spot an index the planner never uses and explain why
• Understand why B-tree cannot serve JSONB containment (@>)
• Pick the right access method and operator class: GIN with jsonb_path_ops
• Verify a fix by watching the plan switch to a Bitmap Index Scan

Contents

1. The stand
2. Bring it up
3. The schema
4. Apply the schema
5. The data
6. Load it
7. A clean slate for statistics
8. Reproduce the pain
9. Let the traffic flow
10. Ask the database what hurts
11. The index that does nothing
12. Convict it with a plan
13. Design the fix
14. Apply it
15. Watch the plan flip
16. Close the ticket