Почему индексы 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');