Ускоряет ли поиск по триграммному индексу по нескольким столбцам и как правильно сделать такой поиск?

Предположим, у меня есть таблица с несколькими столбцами. Например:

id int
name text
surname text
cars json

пример записи будет

+----+------+---------+------------------------------------+
| id | name | surname |              cars                  |
+----+------+---------+------------------------------------+
|  1 | John | Doe     | {"values":["Ford", "BMW", "Fiat"]} |
+----+------+---------+------------------------------------+

Я хочу выполнить поиск по всем этим данным таблицы на предмет релевантности следующим образом:

select *,
       similarity(
          'Malcolm Joe likes Ferrary, but hates BMW',
          (name || (cars ->> 'values') || surname)
       ) sim
from public.test_table
where similarity(
         'Malcolm Joe likes Ferrary, but hates BMW',
         (name || (cars ->> 'values') || surname)
      ) > 0.05
order by sim desc;

Есть ли способ ускорить этот поиск? Создание триграммного индекса? Если да - как лучше создать? в одном столбце, в каждом столбце, в выражении конкатенации? Также я не понял, какой тип индекса лучше - GIN или GiST. Я читал, что GIN обычно лучше для обычного полнотекстового поиска, но GiST лучше для поиска по триграмме. Это верно?

Я также хотел спросить, есть ли лучший способ написать вышеуказанный запрос?

Если кому-то интересно, почему я выбрал триграмму, а не обычный полнотекстовый поиск - это потому, что строки поиска будут поступать от обработки некоторого пользовательского ввода, поэтому могут быть ошибки или даже случаи, когда английские 'o' или 'c' заменяются кириллическими буквами. мои записи в базе данных или поиск также могут содержать буквенно-цифровые данные, которые также лучше обрабатываются с помощью триграммы.


postgresql pg-trgm
person Sheyko Dmitriy    schedule 11.10.2019    source источник

Ответы (2)


arrow_upward
2
arrow_downward

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

CREATE INDEX ON public.test_table USING gist
   ((name || (cars ->> 'values') || surname) gist_trgm_ops);

Затем запрос следует переписать на:

SELECT *,
       similarity(
          'Malcolm Joe likes Ferrary, but hates BMW',
          (name || (cars ->> 'values') || surname)
       ) sim
FROM public.test_table
WHERE ((name || (cars ->> 'values') || surname)
       <->
       'Malcolm Joe likes Ferrary, but hates BMW')
      < 0.95
ORDER BY (name || (cars ->> 'values') || surname)
         <->   /* trigram distance */
         'Malcolm Joe likes Ferrary, but hates BMW'
LIMIT 50;

Запрос пришлось переписать, потому что индекс поддерживает <->, но не similarity() в ORDER BY выражениях.

Я добавил LIMIT, чтобы подсказывать оптимизатору, при необходимости поставил ограничение.

Я думаю, что в целом индексы GIN лучше работают для больших таблиц, но я не уверен. В любом случае у вас нет выбора с этим запросом, потому что индексы GIN не поддерживают это предложение ORDER BY.

person Laurenz Albe    schedule 11.10.2019
comment
Спасибо. Могу я спросить, почему вы выбрали ‹-› (расстояние), а не сходство ›порог? В моем случае это работает лучше из-за второй части (которая упорядочена по убыванию)? Я новичок в мире полнотекстового поиска, так что order by sim desc кажется равным упорядочению по (имя || (автомобили - ›› 'значения') || фамилия) ‹-› 'поисковое выражение' - person Sheyko Dmitriy; 11.10.2019
comment
Вы правы, он идентичен по семантике. Но выражение функции не может быть проиндексировано, в то время как оператор может. На самом деле, больше не соответствует действительности в PostgreSQL v12, но никто написал функцию поддержки планировщика для similarity. Я улучшил ответ. - person Laurenz Albe; 11.10.2019

arrow_upward
0
arrow_downward

В вашем примере вы хотите создать индекс для выражения (name || (cars ->> 'values') || surname). Однако сам ваш пример не имеет смысла. Это правильный SQL, но зачем вам это делать? Зачем сравнивать английское предложение со строкой, состоящей из чьего-то полного имени, но с каплей JSON, вставленной в ее середину? Это важно, потому что в вашем примере есть только одна строка, поэтому индекс не имеет значения. Поэтому нам нужно экстраполировать ваш пример на большое количество строк, где индекс будет иметь значение. Но поскольку это не имеет никакого реального смысла, как мы можем экстраполировать это разумным образом?

Также я не понял, какой тип индекса лучше - GIN или GiST. Я читал, что GIN обычно лучше для обычного полнотекстового поиска, но GiST лучше для поиска по триграмме. Это верно?

По моему опыту, в целом не так. Индексы триграммы GiST основаны на сигнатуре, где каждая триграмма устанавливает бит в сигнатуре. Но триграмм намного больше, чем битов, поэтому они сильно перегружены. Эти типы индексов работают хорошо только тогда, когда они мало заполнены. (Но заранее трудно сказать, что означает «слабо заполненный», иначе они «попробуют это с вашим реальным набором данных и увидят».) Учитывая их непредсказуемость, я избегаю индексов GiST, если для них нет явной выгоды, что я не смотри здесь.

Учитывая ваш запрос, вы можете использовать любой вид индекса, но вам придется писать его по-другому. Кроме того, сомнительно, что какой-либо из индексов поможет, потому что в

similarity(x,exp) > 0.05

отсечка 0,05 настолько слабая, что несколько строк, вероятно, будут отклонены индексом.

Если бы у вас был более высокий порог, например 0,5, то с индексом GIN это можно было бы сформулировать так:

set pg_trgm.similarity_threshold = 0.5;
select ... from test_table where x % exp order by x <-> exp ;

Это извлечет все достаточно похожие, а затем отсортирует их по расстоянию. Если несколько вещей «достаточно похожи», это дает довольно хорошую производительность (а если нет, вам следует пересмотреть свой выбор pg_trgm.similarity_threshold). Как говорит Лауренс Альбе, с помощью индекса GiST вы можете извлекать строки уже по порядку, а затем останавливаться после достижения LIMIT, но при отсутствии предложения LIMIT это не имеет значения.

person jjanes    schedule 11.10.2019