Categories: PostgreSQL

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

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

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

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

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

  1. like
  2. ilike
  3. ~
  4. ~*
  5. =
  6. 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 фактически будет заменено на равно =.

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

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

Оператор равенства вместо выражения 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 из стандарта 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

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

 

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

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

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

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

Выражение 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 индекс НЕ БУДЕТ работать с функцией «regexp_like()«, которая без переданных флагов по сути является аналогом одиночной тильды ~.

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

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

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

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

 

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

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

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

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

Оператор тильда звёздочка работает с 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

 

Итог

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

CREATE EXTENSION pg_trgm;
admin

Share
Published by
admin

Recent Posts

PostgreSQL | Как получить первый элемент из ячейки типа record?

При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…

1 год ago

PostgreSQL | Как отфильтровать JSONB-массив объектов по логическому значению ключа?

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…

1 год ago

PostgreSQL | Как отбросить нули в типе numeric?

Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…

1 год ago

JavaScript | Как получить следующий день?

Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…

1 год ago

PostgreSQL | Агрегация JSONB-объектов в один объект

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…

1 год ago

PostgreSQL | Группировка по 10 записей

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…

1 год ago