Варианты производительности запросов PostgreSQL LIKE

Я видел довольно большое изменение времени отклика относительно LIKE запросы к конкретной таблице в моей базе данных. Иногда я получаю результаты в течение 200-400 мс (очень приемлемо), но в других случаях может потребоваться до 30 секунд, чтобы вернуть результаты.

Я это понимаю LIKE Запросы очень ресурсоемки, но я просто не понимаю, почему такая большая разница во времени отклика. Я построил индекс btree на owner1 поле, но я не думаю, что это помогает с LIKE запросы. У кого-нибудь есть идеи?

Пример SQL:

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

Я также попробовал:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

А также:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

С похожими результатами.
Количество строк в таблице: около 95 000

Источник

7 ответов

Решение

ФТС не поддерживает LIKE

Ранее принятый ответ был неверным. Полнотекстовый поиск с его полнотекстовыми индексами не для LIKE оператор вообще, он имеет свои собственные операторы и не работает для произвольных строк. Он работает на словах на основе словарей и stemming. Он поддерживает сопоставление префиксов для слов, но не с LIKE оператор:

Триграммы для индексов LIKE

Установите дополнительный модуль pg_trgm который предоставляет классы операторов для индексов триграмм GIN и GiST для поддержки всех LIKE а также ILIKE паттерны, а не только привязанные слева:

Пример индекса:

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

Или же:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

Пример запроса:

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

Триграммы? Как насчет более коротких строк?

Слова с менее чем 3 буквами в индексированных значениях по-прежнему работают. Руководство:

Считается, что каждое слово имеет два пробела с префиксом и один пробел с суффиксом при определении набора триграмм, содержащихся в строке.

И шаблоны поиска с менее чем 3 буквы? Руководство:

Для обоих LIKE и поиск по регулярному выражению, помните, что шаблон без извлекаемых триграмм выродится в сканирование полного индекса.

Это означает, что сканирование индекса / растрового индекса все еще работает (планы запросов для подготовленного оператора не будут нарушены), но это просто не принесет вам большей производительности. Как правило, это не большая потеря, поскольку 1- или 2-буквенные строки едва ли являются выборочными (более нескольких процентов совпадений базовой таблицы), и поддержка индекса не повысила бы производительность с самого начала, поскольку полное сканирование таблицы выполняется быстрее.


text_pattern_ops для сопоставления префиксов

Для шаблонов с левым якорем (без подстановочных знаков) вы получите оптимальное решение с подходящим классом операторов для индекса btree: text_pattern_ops или же varchar_pattern_ops, Обе встроенные функции стандартного Postgres, дополнительный модуль не требуется. Аналогичная производительность, но гораздо меньший показатель.

Пример индекса:

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

Пример запроса:

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no leading wildcard

Или, если вы должны работать с базой данных с языком 'C' (фактически без языка), тогда все будет отсортировано в соответствии с порядком байтов в любом случае, и простой индекс btree с классом оператора по умолчанию сделает эту работу.

Более подробная информация, объяснение, примеры и ссылки в этих связанных ответах на dba.SE:

Источник

Возможно, быстрые - это привязанные шаблоны с учетом регистра, которые могут использовать индексы. т. е. в начале строки совпадения нет подстановочного знака, поэтому исполнитель может использовать сканирование диапазона индекса. ( соответствующий комментарий в документации здесь). Lower и ilike также утратят вашу способность использовать индекс, если вы специально не создадите индекс для этой цели (см. функциональные индексы).

Если вы хотите найти строку в середине поля, вы должны изучить полнотекстовые или триграммные индексы. Первый из них находится в ядре Postgres, другой доступен в модулях contrib.

Источник

Недавно у меня была похожая проблема с таблицей, содержащей 200000 записей, и мне нужно делать повторные запросы LIKE. В моем случае искомая строка была исправлена. Другие поля менялись. Потому что я смог переписать

SELECT owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%');

как

CREATE INDEX ix_parcels ON parcels(position(lower('someones name') in lower(owner1)));

SELECT owner1 FROM parcels
WHERE position(lower('someones name') in lower(owner1)) > 0;

Я был в восторге, когда запросы вернулись быстро и проверил, что индекс используется с EXPLAIN ANALYZE:

 Bitmap Heap Scan on parcels  (cost=7.66..25.59 rows=453 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   Recheck Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
   ->  Bitmap Index Scan on ix_parcels  (cost=0.00..7.55 rows=453 width=0) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
 Planning time: 0.075 ms
 Execution time: 0.025 ms
Источник

Выполните приведенный ниже запрос, чтобы улучшить производительность запроса LIKE в postgresql. создайте такой индекс для больших таблиц:

CREATE INDEX <indexname> ON <tablename> USING btree (<fieldname> text_pattern_ops)
Источник

Когда вы когда-либо используете предложение в столбце с функциями, например, LIKE, ILIKE, upper, lower и т. Д. Тогда postgres не будет принимать во внимание ваш обычный индекс. Он будет выполнять полное сканирование таблицы, просматривая каждую строку, и поэтому он будет медленным.

Правильный способ - создать новый индекс в соответствии с вашим запросом. Например, если я хочу сопоставить столбец без учета регистра, а мой столбец - varchar. Тогда вы можете сделать это вот так.

create index ix_tblname_col_upper on tblname (UPPER(col) varchar_pattern_ops);

Точно так же, если ваш столбец представляет собой текст, вы делаете что-то вроде этого

create index ix_tblname_col_upper on tblname (UPPER(col) text_pattern_ops);

Точно так же вы можете изменить верхнюю функцию на любую другую функцию, которую хотите.

Источник

Вы можете установить Wildspeed, другой тип индекса в PostgreSQL. Wildspeed работает с символами%word%, без проблем. Недостатком является размер индекса, он может быть большим, очень большим.

Источник

Ваши подобные запросы, вероятно, не могут использовать индексы, которые вы создали, потому что:

1) ваш критерий LIKE начинается с символа подстановки.

2) вы использовали функцию с вашими критериями LIKE.

Источник

Для чего стоит Django ORM имеет тенденцию использовать UPPER(text) для всех LIKE запросы, чтобы сделать его нечувствительным к регистру,

Добавление индекса на UPPER(column::text) значительно ускорил мою систему, в отличие от любой другой вещи.

Что касается ведущих%, да, что не будет использовать индекс. Смотрите этот блог для отличного объяснения:

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

Источник
Другие вопросы по тегам