JSONB, Trigram and Why an Index Is Ignored

Build GIN indexes for JSONB and trigram search on a real PostgreSQL stand, then systematically diagnose seven reasons the planner ignores an index — and learn to fix each one.

Most indexes work — until they don't. This course starts with a real stand: three tables, 720k rows, and a fixed random seed so every plan you read matches what is documented here. We cover two index types that standard B-tree cannot handle: GIN for jsonb containment queries and GIN with pg_trgm for contains-style LIKE search. Then we walk seven distinct reasons the planner quietly ignores an existing index — a cast on the column, a type mismatch, low selectivity, stale statistics, a composite index with the wrong key order, a partial index whose predicate the query doesn't satisfy, and a table so small that a sequential scan is just faster. Each reason gets its own EXPLAIN, its own diagnosis, and its own fix. The course ends with a living catalog file you keep and grow on real projects.

What you'll build

• Create a GIN index on a jsonb column and explain which operators it supports
• Name one case where a GIN index on jsonb does not help
• Install pg_trgm and create a GIN index for LIKE '%text%' search
• Tell prefix search (LIKE 'text%') apart from contains search (LIKE '%text%') in terms of index strategy
• List seven reasons the planner ignores an index and diagnose each with EXPLAIN ANALYZE
• Fix a function-on-column, type-mismatch, and low-selectivity case
• Use BUFFERS to verify an index is being read and not just the heap
• Maintain queries/index-catalog.sql as a living pattern-to-index reference

Contents

1. A stand built for index experiments
2. Wait for it to be ready
3. One command to bring it up
4. Start the server
5. A short way into the database
6. The events table
7. The documents table
8. The products table
9. Create the tables
10. Seed the events, repeatably
11. Seed the documents
12. Seed the products
13. Load the data
14. Fresh statistics, single-threaded plans
15. Confirm what we are sitting on
16. Searching inside JSONB without an index
17. A sequential scan through half a million rows
18. Build a GIN index on the JSONB column
19. The same query, now with a GIN index
20. A Bitmap Index Scan on the GIN index
21. One operator that GIN cannot use
22. Back to a sequential scan
23. A contains LIKE with no index
24. Sequential scan on documents
25. Install pg_trgm and build the trigram index
26. The same LIKE, now with a trigram index
27. A Bitmap Index Scan through the trigram index
28. A B-tree index for prefix search
29. Prefix versus contains in one file
30. Two patterns, two indexes
31. Reason 1: a function wraps the indexed column
32. The index is there but the cast breaks the match
33. Reason 2: comparing the wrong type
34. Sequential scan on the primary key table
35. Reason 3: the index exists but selectivity is too low
36. Too many matching rows for the index to help
37. Reason 4: statistics are stale after a large insert
38. The estimate is wildly wrong
39. ANALYZE fixes the estimate
40. Reason 5: composite index with the wrong key order
41. The second column alone cannot use the index
42. Reason 6: partial index whose predicate the query does not satisfy
43. A partial index only works when its predicate is implied
44. Reason 7: the table is too small to benefit from an index
45. Sequential scan on a 100-row table
46. A catalog you grow on real projects