Почему индексы PostgreSQL Text-Search GiST настолько медленнее, чем индексы GIN?
Я тестирую функции Text-Search PostgreSQL, используя сентябрьский дамп данных из StackOverflow в качестве примерных данных.: -)
Наивный подход к использованию предикатов LIKE
или регулярного выражения POSIX для поиска 1,2 миллиона строк занимает около 90-105 секунд (на моем Macbook), чтобы выполнить полное сканирование таблицы, ключевое слово.
SELECT * FROM Posts WHERE body LIKE '%postgresql%';
SELECT * FROM Posts WHERE body ~ 'postgresql';
Неиндексированный, специальный текстовый поиск требует 8 минут:
SELECT * FROM Posts WHERE to_tsvector(body) @@ to_tsquery('postgresql');
Создание индекса GIN занимает около 40 минут:
ALTER TABLE Posts ADD COLUMN PostText TSVECTOR;
UPDATE Posts SET PostText = to_tsvector(body);
CREATE INDEX PostText_GIN ON Posts USING GIN(PostText);
(Я понимаю, что я мог бы сделать это за один шаг, указав его как индекс выражения.)
Впоследствии запрос, поддерживаемый индексом GIN, выполняется намного быстрее - это занимает около 40 миллисекунд:
SELECT * FROM Posts WHERE PostText @@ 'postgresql';
Однако, когда я создаю индекс GiST, результаты совершенно разные. Для создания индекса требуется менее 2 минуты:
CREATE INDEX PostText_GIN ON Posts USING GIST(PostText);
Затем запрос с использованием оператора текстового поиска @@
принимает 90-100 секунд. Таким образом, индексы GiST улучшают необработанный запрос TS с 8 минут до 1,5 минут. Но это не улучшает выполнение полного сканирования таблицы с помощью LIKE
. Это бесполезно в среде веб-программирования.
Я пропустил что-то важное для использования индексов GiST? Нужно ли индексировать предварительно кэшированные в памяти или что-то еще? Я использую обычную установку PostgreSQL из MacPorts без настройки.
Каков рекомендуемый способ использования индексов GiST? Или все, кто делает TS с PostgreSQL, пропускают индексы GiST и используют только индексы GIN?
PS: Я знаю об альтернативах, таких как Sphinx Search и Lucene. Я просто пытаюсь узнать о функциях, предоставляемых самим PostgreSQL.
Ответы
Ответ 1
попробовать
CREATE INDEX PostText_GIST ON Posts USING GIST(PostText varchar_pattern_ops);
который создает индекс, подходящий для запросов префикса. См. Документы PostgreSQL в Операторские классы и семейства операторов. Оператор @@имеет смысл только для терминальных векторов; индекс GiST (с varchar_pattern_ops) даст отличные результаты с помощью LIKE.
Ответ 2
Документы имеют хороший обзор различий производительности между индексами GiST и GIN, если вам интересно: GiST и типы индексов GIN.
Ответ 3
btw: если это еще не было удовлетворено вашим удовлетворением, часть, в которой вы делали
SELECT * FROM Posts WHERE PostText @@ 'postgresql';
должно быть
SELECT * FROM Posts WHERE PostText @@ to_tsquery('postgresql');