Обобщённые инвертированные индексы (Generalized Inverted Indexes / GIN)
(продолжение предыдущего поста)

Generalized Inverted Indexes (Обобщённые инвертированные индексы или GIN) — мощный инструмент СУБД, в частности, в Postgres.

Они отлично подходят для инвертирования типичного сценария использования индексов.

Вместо сопоставления «строка с ID 2 содержит значение „become a database expert“» вы переворачиваете это так: «токен „database“ сопоставляется со строками с ID 1, 2 и 3, а токен „expert“ — со строкой с ID 2».

Индексам GIN передаётся набор значений для каждой строки, которую вы хотите проиндексировать. Каждое уникальное значение становится ключом в индексе и сопоставляется с набором CTID (идентификаторов строковых кортежей), содержащих это значение.

У такого подхода есть несколько вариантов применения, один из которых — полнотекстовый поиск! В MySQL есть специальный тип индекса FULLTEXT, тогда как в Postgres для создания аналогичной функциональности используются более универсальные индексы GIN. Как это сделать?

(а) Добавьте в таблицу столбец tsvector:

CREATE TABLE post (
    author TEXT,
    publish_date DATE,
    content TEXT NOT NULL,
    search_vector tsvector);

(б) Заполните его лексемами (нормализованными словами):

UPDATE post
    SET search_vector =
    to_tsvector('english', content);

(в) Создайте индекс, используя лексемы в качестве ключей:

CREATE INDEX post_search_idx
    ON post USING GIN(search_vector);

(г) Выполняйте запросы!

SELECT * FROM post
    WHERE search_vector @@
    to_tsquery('english', 'database & expert');