A real GIN index, and a query that never touched it

Catalog::SearchQuery is the class behind this app's catalogue search. Until 2 September 2026, it filtered courses and lessons like this:

# app/queries/catalog/search_query.rb, before 2 September 2026
pattern = "%#{ActiveRecord::Base.sanitize_sql_like(term)}%"
title_sql = ->(table) { Arel.sql("COALESCE(#{table}.title->>#{ActiveRecord::Base.connection.quote(locale.to_s)}, #{table}.title->>'en')") }

courses = ::Catalog::Course.active
  .where("#{title_sql.call('courses')} ILIKE ?", pattern)

courses.title is jsonb. Each course's title is a small document, {"en" => "...", "pt-BR" => "..."}, not a plain string column. The migration that added performance indexes to the core tables, 20260301202548_add_performance_indexes_to_core_tables.rb, put a GIN index straight on that column, under a comment naming its purpose:

# ── JSONB GIN Indexes (Search & Filtering) ──────────────────

# Using gin index for PostgreSQL jsonb translatable fields
add_index :courses, :title, using: :gin
add_index :courses, :description, using: :gin
add_index :categories, :name, using: :gin
add_index :lessons, :title, using: :gin

Those are four GIN indexes on the four jsonb columns the app shows text from, labelled for search and filtering. SearchQuery searched courses.title and lessons.title, so on paper the index and the query lined up.

The query ran a sequential scan anyway

The fix removed that index, so the experiment below runs on courses_old: a temporary copy of the development courses table (361 rows) with the original GIN index rebuilt on title. The search, as it ran in English:

EXPLAIN SELECT * FROM courses_old
  WHERE COALESCE(title->>'en', title->>'en') ILIKE '%rails%';

Seq Scan on courses_old  (cost=0.00..22.32 rows=1 width=271)
  Filter: (COALESCE((title ->> 'en'::text), (title ->> 'en'::text)) ~~* '%rails%'::text)

A Seq Scan reads and checks every row, and the GIN index on title never appears in the plan. At a few hundred rows that isn't slow, but "not slow" is a different claim from "the index helped". This plan didn't use it at all.

The same index, a query that uses it

Here's the same column and the same index, queried with Postgres's containment operator instead of a pattern match:

EXPLAIN SELECT * FROM courses_old WHERE title @> '{"en": "Ruby basics"}';

Bitmap Heap Scan on courses_old  (cost=12.98..16.99 rows=1 width=271)
  Recheck Cond: (title @> '{"en": "Ruby basics"}'::jsonb)
  ->  Bitmap Index Scan on courses_old_title  (cost=0.00..12.98 rows=1 width=0)
        Index Cond: (title @> '{"en": "Ruby basics"}'::jsonb)

That's a Bitmap Index Scan on the same index, on the same column. The index was never broken. It was built for a question the app's search never asked.

What this course is about

A GIN index isn't one thing. It's a storage mechanism, and its operator class decides which questions it can answer about a value. "Does this document contain that pair" is one question. "Does this text match this pattern" is a different one, and adding using: :gin to a migration doesn't choose between them. The rest of this course works out why one GIN index served one query and ignored another on the same column, and what the app's fix looked like.

Key takeaways

• Catalog::SearchQuery filters courses.title and lessons.title with ILIKE on text extracted by ->>: a pattern match, not a jsonb operation.
• The original migration added GIN indexes on courses.title, courses.description, categories.name and lessons.title, labelled for search and filtering.
• With the original index rebuilt on a copy of courses, the search query ran a Seq Scan, and the GIN index never appeared in its plan.
• The same index, queried with @> instead of ILIKE, produced a Bitmap Index Scan, so the index itself was never the problem.