PostgreSQL | Как использовать GIN индекс в запросах SELECT?

После того как вы создали GIN индекс для текстового столбца таблицы, возникает вопрос о его использовании.

CREATE INDEX efix_t_path_path_gin_ops ON a.t_path USING gin (path gin_trgm_ops)

Какие именно операторы и выражения приводят к обращению к этому индексу, а не к какому-то другому?

Возможные варианты обращения в предложении WHERE команды SELECT:

like
ilike
~
~*
=
similar to

Главная задача «выражений» и «операторов» в предложении WHERE — это выдавать логическое значение true или false, чтобы отбирать записи в таблицах и их соединениях.

Использование выражения like в запросе для обращения к GIN индексу

GIN индекс НЕ БУДЕТ работать с выражением like, если в строковом шаблоне (справа от like) НЕ БУДЕТ символов процента (%) или нижнего подчёркивания (_).

Напомню, что «процент» (%) — это любая последовательность каких-то символов, а «нижнее подчёркивание» (_) — это любой один какой-то символ.

Иными словами запрос:

explain
select * from a.t_path where "path" like 'wood';

будет использовать B-TREE индекс с оператором равенства

Выражение like как равенство использует B-TREE индекс - PostgreSQL — Выражение like как равенство использует B-TREE индекс — PostgreSQL

В этом случае выражение like фактически будет заменено на равно =.

explain
select * from a.t_path where "path" = 'wood';

В плане выполнения запроса пункт «Index Cond» по прежнему будет равен «(path = ‘wood’::text)«, но операция приведения «Filter: (path ~~ ‘wood’::text)» уже будет пропущена.

Оператор равенства вместо выражения like - PostgreSQL — Оператор равенства вместо выражения like — PostgreSQL

Что делать?

GIN индекс БУДЕТ работать с выражением like, если в строковом шаблоне (справа от like) БУДУТ символы процента (%) или нижнего подчёркивания (_).

Запросы для процентов:

explain
select * from a.t_path where "path" like '%wood';

explain
select * from a.t_path where "path" like 'wood%';

explain
select * from a.t_path where "path" like '%wood%';

Планы для процентов:

Выражение like с процентом слева в шаблоне - PostgreSQL — Выражение like с процентом слева в шаблоне — PostgreSQL

Выражение like с процентом слева и справа в шаблоне - PostgreSQL — Выражение like с процентом слева и справа в шаблоне — PostgreSQL

Выражение like с процентом справа в шаблоне - PostgreSQL — Выражение like с процентом справа в шаблоне — PostgreSQL

В планах с процентами % мы видим приведение выражения like к оператору «двойная тильда» ~~. Этот оператор введён разработчиками PostgreSQL и он аналогичен оригинальному like из стандарта SQL.

Запросы для нижних подчёркиваний:

explain
select * from a.t_path where "path" like '_wood';

explain
select * from a.t_path where "path" like 'wood_';

explain
select * from a.t_path where "path" like '_wood_';

Планы для нижних подчёркиваний:

Выражение like с нижним подчёркиванием слева в шаблоне - PostgreSQL — Выражение like с нижним подчёркиванием слева в шаблоне — PostgreSQL

Выражение like с нижним подчёркиванием слева и справа в шаблоне - PostgreSQL — Выражение like с нижним подчёркиванием слева и справа в шаблоне — PostgreSQL

Выражение like с нижним подчёркиванием справа в шаблоне - PostgreSQL — Выражение like с нижним подчёркиванием справа в шаблоне — PostgreSQL

Аналогично процентам. Будет использован GIN индекс.

Использование выражения ilike в запросе для обращения к GIN индексу

Тут всё практически как в обычном like

explain
select * from a.t_path where "path" ilike 'wood';

Различие в том, что ilike пытается обратиться к GIN индексу даже в случае прямого равенства.

Выражение ilike по равенству сразу работает с GIN индексом - PostgreSQL — Выражение ilike по равенству сразу работает с GIN индексом — PostgreSQL

Лучше сразу использовать выражение ilike вместо like, чтобы при любом раскладе обращаться к GIN индексу, если вам не важен регистр.

explain
select * from a.t_path where "path" ilike '%wood';

explain
select * from a.t_path where "path" ilike 'wood%';

explain
select * from a.t_path where "path" ilike '%wood%';

explain
select * from a.t_path where "path" ilike '_wood';

explain
select * from a.t_path where "path" ilike 'wood_';

explain
select * from a.t_path where "path" ilike '_wood_';

Выражение ilike будет заменяться PostgreSQL на оператор «тильда тильда звёздочка» ~~*

Использование оператора тильда ~ регулярных выражений POSIX в запросе для обращения к GIN индексу

Важно различать одиночную тильду от двойной!

Одиночная тильда ~ — это регулярные выражения POSIX.

Двойная тильда ~~ — это по сути выражения like.

POSIX с учётом регистра обращается к GIN индексу:

explain
select * from a.t_path where "path" ~ 'wood';

Будут отобраны все возможные записи с конкретным регистром символов как в шаблоне.

Оператор тильда работает с GIN индексом - PostgreSQL — Оператор тильда работает с GIN индексом — PostgreSQL

GIN индекс НЕ БУДЕТ работать с функцией «regexp_like()«, которая без переданных флагов по сути является аналогом одиночной тильды ~.

explain
select * from a.t_path where regexp_like("path", 'wood');

Не будет работать и B-TREE индекс, если такой имеется. Для функций нужны специальные индексы с условиями WHERE при объявлении.

Функция regexp_like аналог одиночной тильды не работает с GIN индексами - PostgreSQL — Функция regexp_like аналог одиночной тильды не работает с GIN индексами — PostgreSQL

Мы получим последовательное сканирование таблицы. Это всегда медленно.

Использование оператора тильда звёздочка ~* регулярных выражений POSIX в запросе для обращения к GIN индексу

Выборки с использованием GIN индекса без учёта регистра

explain
select * from a.t_path where "path" ~* 'wood';

Очень похоже на одинарную тильду.

Оператор тильда звёздочка работает с GIN индексом - PostgreSQL — Оператор тильда звёздочка работает с GIN индексом — PostgreSQL

Использование оператора равенства = в запросе для обращения к GIN индексу

Для операторов равенства = GIN индексы могут быть не так эффективны, как индексы-B-деревья (B-TREE).

Если нужно отбирать записи таблиц по точному вхождению в строку, то лучше всего НЕ использовать GIN индексы. Запросы по нему будут медленными.

Использование регулярных выражений SIMILAR TO в запросе для обращения к GIN индексу

Ситуация очень похожа на выражения like. В PostgreSQL слишком много вариантов регулярных выражений и выражения SIMILAR TO это какой-то гибрид. Вам решать, использовать их в своём проекте или нет. Но важно знать что GIN индекс с ними тоже может работать.

explain
select * from a.t_path where "path" similar to 'wood';

explain
select * from a.t_path where "path" similar to '%wood';

explain
select * from a.t_path where "path" similar to 'wood%';

explain
select * from a.t_path where "path" similar to '%wood%';

Без процентов мы пытаемся найти точное вхождение с учётом регистра и длины искомой строки от шаблона.

Выражение similar to по равенству не работает с GIN индексом - PostgreSQL — Выражение similar to по равенству не работает с GIN индексом — PostgreSQL

Выражение similar to процент слева работает с GIN индексом - PostgreSQL — Выражение similar to процент слева работает с GIN индексом — PostgreSQL

Выражение similar to процент справа работает с GIN индексом - PostgreSQL — Выражение similar to процент справа работает с GIN индексом — PostgreSQL

Выражение similar to процент слева и справа работает с GIN индексом - PostgreSQL — Выражение similar to процент слева и справа работает с GIN индексом — PostgreSQL

Итог

Чтобы всё описанное выше работало, важно установить расширение pg_trgm. По умолчанию «из коробки» данное расширение не устанавливается в СУБД.

CREATE EXTENSION pg_trgm;