Антиоптимизация ILIKE в Postgresql при множественной фильтрации

Про LIKE/ILIKE в postgresql.

Постараемся избежать:

1. обсуждение индексов gin (полнотекстовый)/gist (по сложным структурам)

2. про устройство полнтекствого поиска и как можно быстро искать поговорим потом.

3. заметку, что LIKE в отличие от ILIKE может работать с индексами, правда не с gin а с b-tree и правда только в случае

name LIKE 'peter%'

4. обсуждение расширений, чтобы ILIKE все-таки заставить работать с индексами

5. и даже не будем углубляться в разницу между LIKE и ILIKE - не будем зансочивы утверждая, что в спецификации sql ILIKE никакого нет и просто напомним себе, что ILIKE - это postgres оператор аналог LIKE только к регистру не чувствительный.

Сейчас не об этом. Я про микрооптимизацию, которую порой встречаю при полнтекстовом поиске по нескольким полям.

Вместо:

WHERE field1 LIKE '%value%' OR field2 ILIKE '%value%' OR field3 ILIKE '%value%' OR field4 ILIKE '%value%' OR field5 ILIKE '%value%' OR field6 ILIKE '%value%' OR field7 ILIKE '%value%'

можно переписать, чтобы postgres проходился по одной строке, а не по 7 отдельно, и якобы снизить нагрузку на cpu:

WHERE CONCAT_WS(' ', field1, field2, field3, field4, field5, field6, field7) ILIKE '%value%'

На самом деле нет.

Во-первых, ленивое вычисление. OR быстрее, т.к. при нахождении совпадения не идет дальше, но с null надо будет обрабатывать через COALESCE,

Во-вторых, ложное срабатывание в случае CONCAT_WS - мы получаем строку, которая может совпать и которой реально нет в бд.

В-третьих, да нет здесь никакой оптимизации. Упираемся в seq scan в обоих случаях. Не умеет Postgres b-tree индексы в случае с %value% в отличие от LIKE c 'value%'.

А как же быть:

1. Если хотим ILIKE c 'value%' и индекс скан, то нам надо использовать функциональный индекс и отказаться от ILIKE

CREATE INDEX idx_field1_lower ON your_table (LOWER(field1));

а и условия получается такое

WHERE LOWER(field1) LIKE LOWER('value%')

но тут надо быть осторожным, т.к. обычная локаль на подобии ru_RU.UTF-8 или en_US.UTF-8 может не использовать b-tree индекс для LIKE в нашем случае, а значит надо укзать text_patter_ops явно при создании индекса.

CREATE INDEX idx_field1_lower_pattern

ON your_table (LOWER(field1) text_pattern_ops);

2. Если после первого пункта мы все равно хотим ILIKE, то триграммный индекс в помощь

CREATE EXTENSION IF NOT EXISTS pg_trgm; — расширение

CREATE INDEX idx_all_fields_trgm ON your_table

USING gin ((field1 || ' ' || field2 || ' ' || field3 || ' ' || field4 || ' ' || field5 || ' ' || field6 || ' ' || field7) gin_trgm_ops);

3. Если у нас все же поиск целым словам, а жажда ILIKE нас отпустила, то мы получаем полнотекстовый поиск и соответсвенно полнотекстовый индекс.

CREATE INDEX idx_fts ON your_table

USING gin (to_tsvector('russian', coalesce(field1,'') || ' ' || coalesce(field2,'') ... ));

— и мы можем делать вот так

WHERE to_tsvector('russian', coalesce(field1,'') || ' ' || coalesce(field2,'') ...) @@ to_tsquery('russian', 'value');

З.Ы.: все-таки картинки ИИ генерит отстой