Как использовать много операторов LIKE и индексировать
В моем запросе я хочу найти строки, соответствующие одному из многих операторов LIKE. Я знаю 3 способа сделать это, но только один из них может использовать index.
Начнем с таблицы:
CREATE TABLE dir (
id BIGSERIAL PRIMARY KEY,
path TEXT NOT NULL
);
CREATE INDEX path_idx ON dir(path TEXT_pattern_ops);
После вставки образцов данных я могу:
EXPLAIN ANALYZE
SELECT id, path FROM dir
WHERE path LIKE 'A%'
OR path LIKE 'B%'
OR path LIKE 'C%';
Вышеуказанный запрос правильно использует индекс.
Второй способ:
EXPLAIN ANALYZE
SELECT id, path FROM dir
WHERE path LIKE ANY(ARRAY['A%', 'B%', 'C%']::TEXT[]);
Этот запрос не будет использовать index. Последний подход, который я знаю:
CREATE TABLE patterns (pattern) AS VALUES
('A%'),
('B%'),
('C%');
EXPLAIN ANALYZE
SELECT id, path FROM dir
JOIN patterns ON (dir.path LIKE patterns.pattern);
Этот запрос, как и предыдущий, не будет использовать index.
Вот скрипт SQL для тех, кто хочет поиграть с этими запросами: http://sqlfiddle.com/#!17/24031/2
Проблема: Запрос с path LIKE X OR path LIKE Y полностью нечитаем для многих шаблонов (количество шаблонов может варьироваться от нескольких до сотен или маленьких тысяч), и я боюсь, что большой запрос может медленно анализироваться или даже достигать предела длины запроса в 1 ГБ (некоторые шаблоны могут иметь очень длинные префиксы).
Вопрос: есть ли какой-либо метод oder, возвращающий те же результаты, для которых не требовалось бы помещать все шаблоны непосредственно в запрос (как в этом варианте с соединением)?
Что касается префиксов, можем ли мы спросить о вашей бизнес-проблеме, которая требует таких длинных префиксов? Например, если префикс состоит из 1000 символов, то почему он не может быть просто 500 символов и делать то же самое?
В моей исходной таблице dir проблемы содержатся пути к каталогам в файловой системе. С помощью этого запроса я хочу найти все подкаталоги, но только для некоторых каталогов, удовлетворяющих заданным критериям. Итак, что мне нужно сделать прямо сейчас, это создать запрос для получения этих каталогов из БД, создать SQL с помощью SqlAlchemy: путь LIKE X для каждого созданного каталога. Мне это кажется ненужными шагами, и единственное, что мешает мне изменить, это то, что все другие подходы не используют index.
Я бы заглянул в ltree. потребуются дополнительные усилия, чтобы «перевести» путь к ltree, но у вас будут удобные операторы и поддержка индекса
К сожалению, ltree имеет очень ограниченный набор символов. В моем случае столбец пути может содержать любой символ UTF-8.
Думаю, Лоренц ответила на ваш вопрос. sqlfiddle.com/#!17/e1113/1 сканирование индекса растрового изображения с неявно указанным массивом - пожалуйста, примите его, если это так
Ответы 1
Вы можете создать индекс триграмм, который будет поддерживать ваш запрос.
Для этого вам понадобится расширение pg_trgm; запустите следующее от имени суперпользователя:
CREATE EXTENSION pg_trgm;
Затем вы можете создать индекс GIN:
CREATE INDEX ON dir USING gin (path gin_trgm_ops);
Этот индекс можно использовать со вторым и третьим подходами, поэтому он должен помочь вам.
С короткими шаблонами, подобными показанному в ваших примерах, индекс будет не очень эффективным.
Вы также можете использовать индекс GiST, который, вероятно, будет меньше, но медленнее для поиска.
Обратите внимание, что вы можете использовать этот индекс также с шаблонами, которые начинаются с %.
Я полагаю, OP спрашивает, может ли он заставить анализатор запросов использовать индекс, когда шаблон неявно исходит из объединенной таблицы. вроде - если положить массив в таблицу и появляется последовательное сканирование join array_table on path LIKE ANY(pattern_array);. поэтому он спрашивает, можете ли вы скрыть список шаблонов в запросе и использовать index. Если я правильно понял OP, конечно :)
Правда; Я перефразировал ответ (и в этом случае можно использовать индекс).
Спасибо. Я был бы признателен за комментарий, почему присоединение к любому (массиву) работает, а присоединение к подобному шаблону - нет. Если, конечно, у вас будет минутка. Я уверен, что многим это будет интересно, особенно мне
Я очень удивлен, что это работает. Почему существует разница в том, какой тип индекса создается? Почему в моем случае (индекс btree) планировщик не выбирает использовать индекс, а с индексом trgm он его использует. Мне это совершенно противоречит интуиции.
Я считаю, что это правильный ответ на мой вопрос. К сожалению, мой предыдущий опыт работы с индексом GIST был очень плохим - оказалось, что во время автоочистки индекс просматривается в логическом порядке, который переводится в случайные обращения к диску, что делает его непригодным для использования в моем случае (размер индекса составляет почти 1 ТБ). Вы знаете, что это также относится к индексу GIN?
индексы джина без потерь
Заставляет ли «без потерь» использовать схему последовательного доступа к диску во время вакуума?
Я не уверен, но индексы GIN больше похожи на индексы B-дерева. Попробуйте!
Я использую Postgres 9.6, но при необходимости могу обновить его.