После того как вы создали GIN индекс для текстового столбца таблицы, возникает вопрос о его использовании.
CREATE INDEX efix_t_path_path_gin_ops ON a.t_path USING gin (path gin_trgm_ops)
Какие именно операторы и выражения приводят к обращению к этому индексу, а не к какому-то другому?
Возможные варианты обращения в предложении WHERE команды SELECT:
Главная задача «выражений» и «операторов» в предложении WHERE — это выдавать логическое значение true или false, чтобы отбирать записи в таблицах и их соединениях.
GIN индекс НЕ БУДЕТ работать с выражением like, если в строковом шаблоне (справа от like) НЕ БУДЕТ символов процента (%) или нижнего подчёркивания (_).
Напомню, что «процент» (%) — это любая последовательность каких-то символов, а «нижнее подчёркивание» (_) — это любой один какой-то символ.
Иными словами запрос:
explain select * from a.t_path where "path" like 'wood';
будет использовать B-TREE индекс с оператором равенства
В этом случае выражение like фактически будет заменено на равно =.
explain select * from a.t_path where "path" = 'wood';
В плане выполнения запроса пункт «Index Cond» по прежнему будет равен «(path = ‘wood’::text)«, но операция приведения «Filter: (path ~~ ‘wood’::text)» уже будет пропущена.
Что делать?
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 из стандарта 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_';
Планы для нижних подчёркиваний:
Аналогично процентам. Будет использован GIN индекс.
Тут всё практически как в обычном like
explain
select * from a.t_path where "path" ilike 'wood';
Различие в том, что ilike пытается обратиться к GIN индексу даже в случае прямого равенства.
Лучше сразу использовать выражение 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.
Двойная тильда ~~ — это по сути выражения like.
POSIX с учётом регистра обращается к GIN индексу:
explain
select * from a.t_path where "path" ~ 'wood';
Будут отобраны все возможные записи с конкретным регистром символов как в шаблоне.
GIN индекс НЕ БУДЕТ работать с функцией «regexp_like()«, которая без переданных флагов по сути является аналогом одиночной тильды ~.
explain select * from a.t_path where regexp_like("path", 'wood');
Не будет работать и B-TREE индекс, если такой имеется. Для функций нужны специальные индексы с условиями WHERE при объявлении.
Мы получим последовательное сканирование таблицы. Это всегда медленно.
Выборки с использованием GIN индекса без учёта регистра
explain
select * from a.t_path where "path" ~* 'wood';
Очень похоже на одинарную тильду.
Для операторов равенства = GIN индексы могут быть не так эффективны, как индексы-B-деревья (B-TREE).
Если нужно отбирать записи таблиц по точному вхождению в строку, то лучше всего НЕ использовать 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%';
Без процентов мы пытаемся найти точное вхождение с учётом регистра и длины искомой строки от шаблона.
Чтобы всё описанное выше работало, важно установить расширение pg_trgm. По умолчанию «из коробки» данное расширение не устанавливается в СУБД.
CREATE EXTENSION pg_trgm;
При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…
-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…
Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…
Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…
У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…
Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…