A GIN that never saw the listing query
WHERE title->>'ru' ILIKE scanned fifty thousand JSONB rows. The GIN was for tags. The catalog paid for extract.
The catalog lived on PostgreSQL because JSONB and GIN looked like a free search index. The public grid filtered published SKUs and searched the Russian title with title->>'ru' ILIKE. EXPLAIN said Seq Scan, rows 50000. CPU sat on extract. The GIN on payload never entered the plan.
GIN matches containment. The listing asked for text. Vue SSR still received the full JSONB document and picked one locale in PHP. The buffer cache held documents the page would throw away.
The problem was treating GIN as a title index. Shop visitors paid for a seq scan over fifty thousand JSONB rows. I needed a stored text column, a btree on the grid, and extract off the hot path.
The generated column
title_ru is a stored generated column. The listing SELECT is id, slug, price, title_ru. A btree on (is_published, sort_order, id) covers the grid. Title search, when it exists, uses that text column, not an extract in WHERE. Tags keep a GIN and @>.
What PHP stopped doing
json_decode left the listing. The worker sends one locale string to Vue SSR. Admin still edits JSONB. The public path looks like the MySQL shop on this site: compact rows, covering index, JSON off the hot path.
ILIKE on a generated text column can use a trigram index if search stays. The listing filter does not. Published plus sort_order is enough for the grid. Search is a second query, not a hitch on every page.
What I took from this
GIN is for containment. title->> ILIKE is a seq scan until the text lives in its own column.
JSONB is a write format. The public grid is typed columns. The MySQL filesort case on this site is the same lesson in another engine.
EXPLAIN is cheaper than adding cache. The GIN was healthy and unused. The query never asked for it.