Сравнение индексов в PostgreSQL для поиска по тексту (часть 2)

В первой части статьи мы рассмотрели, как производится поиск по тексту, используя триграммы (pg_trgm) и полнотекстовый поиск (Full Text Search) и решили, что будем сравнивать следующие индексы для ускорения поиска:

GiST для триграмм с сигнатурами длиной 12, 120 и 1200 байт.
GIN для триграмм.
GiST для полнотекстового поиска с сигнатурами длиной 12, 120 и 1200 байт.
GIN для полнотекстового поиска.
RUM для полнотекстового поиска, используя rum_tsvector_ops.
RUM для полнотекстового поиска, используя rum_tsvector_hash_ops.

Подготовка

После сравнения индексов нам необходимо ответить на следующие вопросы:

Какой индекс стоит выбрать для триграмм и для полнотекстового поиска?
Стоит ли использовать триграммы на длинных строках? Если нет, то при какой длине строки лучше отказаться от использования триграмм?
Какую длину сигнатуры выбрать при использовании GiST индекса?
Сколько места на диске сэкономит rum_tsvector_hash_ops и действительно ли его не стоит использовать для естественных языков?

Чтобы определить как влияет длина документа на размер индекса и скорость поиска, для каждого индекса создадим по 3 таблицы:

short_messages с длиной сообщений около 25 символов.
medium_messages с длиной около 250 символов.
long_messages с длиной около 2500 символов.

Индексы будем сравнивать на одних и тех же данных, поэтому в начале создадим 3 таблицы, заполним их данными и скопируем эти таблицы для тестирования каждого индекса.

CREATE TABLE short_messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);
CREATE TABLE medium_messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);
CREATE TABLE long_messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);

В идеале было бы наполнить их реальными сообщениями, но т.к. их нет под рукой, будем их генерировать. Чтобы сгенерированные слова были похожи на реальные и часть лексем были короче самих слов, будем составлять их по слогам. Однако, если для каждой строки будут генерироваться новые слова, то количество уникальных лексем будет неправдоподобно большим, что сильно увеличит размер GIN индекса и повысит вероятность ложных срабатываний у GiST индекса, что замедлит поиск по нему. Поэтому для начала создадим таблицу words, из которой будем выбирать слова для составления сообщений.

CREATE TABLE words (id int, word text);

Судя по этой статистике, в среднем длина 1 английского слова 5-6 символов. Возьмем с этой страницы наиболее популярные слоги и составим из них слова. Каждое слово будет состоять из 3 слогов. На The Economist была статья, в которой говорилось, что большинство взрослых носителей языка знает 20000-35000 слов.

Наполним нашу таблицу 20000 сгенерированными словами:

INSERT INTO words SELECT i, word
FROM generate_series(1, 20000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(syllable, '')
	FROM (
		SELECT (string_to_array('th,he,an,er,in,re,nd,ou,en,on,ed,to,it,at,ha,ve,as,or,hi,ar,te,es,ver,hat,thi,tha,ent,ion,ith,ire,wit,eve,oul,uld,tio,ter,hen,era,hin,sho,ted,ome', ','))[floor(random() * 42) + 1]
		FROM generate_series(1, 3)
	) as f(syllable)
	WHERE i = i
) as f(word);

Получились следующие «слова»:

SELECT word FROM words ORDER BY random() LIMIT 10;

   word    
-----------
 totetha
 hiometio
 atoulera
 eratoen
 anoreve
 terhatted
 ontiothi
 reatas
 astioent
 ndwittio
(10 rows)

Теперь наполним каждую нашу таблицу 1 миллионом сообщений:

INSERT INTO short_messages SELECT i, body
FROM generate_series(1, 1000000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(word, ' ') FROM (
		SELECT word FROM words ORDER BY random() LIMIT 3 -- ~25 letters
	) as words
	WHERE i = i
) as f(body);

INSERT INTO medium_messages SELECT i, body
FROM generate_series(1, 1000000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(word, ' ') FROM (
		SELECT word FROM words ORDER BY random() LIMIT 30 -- ~250 letters
	) as words
	WHERE i = i
) as f(body);

INSERT INTO long_messages SELECT i, body
FROM generate_series(1, 1000000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(word, ' ') FROM (
		SELECT word FROM words ORDER BY random() LIMIT 300 -- ~2500 letters
	) as words
	WHERE i = i
) as f(body);

Для удобства были созданы дубликаты таблиц short_messages, medium_messages и long_messages для каждого случая. Например:

-- GiST для триграмм с сигнатурой длиной 12 байт
CREATE TABLE sm_trgm_gist_12 AS (SELECT * FROM short_messages);
CREATE TABLE mm_trgm_gist_12 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_trgm_gist_12 AS (SELECT * FROM long_messages);

-- GiST для триграмм с сигнатурой длиной 120 байт
CREATE TABLE sm_trgm_gist_120 AS (SELECT * FROM short_messages);
CREATE TABLE mm_trgm_gist_120 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_trgm_gist_120 AS (SELECT * FROM long_messages);

-- ...

Для создания индексов были использованы следующие запросы:

/* short_messages */
-- GiST для триграмм с сигнатурами длиной 12, 120 и 1200 байт
CREATE INDEX sm_trgm_gist_12_idx ON sm_trgm_gist_12 USING gist (body gist_trgm_ops(siglen=12));
CREATE INDEX sm_trgm_gist_120_idx ON sm_trgm_gist_120 USING gist (body gist_trgm_ops(siglen=120));
CREATE INDEX sm_trgm_gist_1200_idx ON sm_trgm_gist_1200 USING gist (body gist_trgm_ops(siglen=1200));

-- GIN для триграмм
CREATE INDEX sm_trgm_gin_idx ON sm_trgm_gin USING gin (body gin_trgm_ops);

-- GiST для полнотекстового поиска с сигнатурами длиной 12, 120 и 1200 байт
CREATE INDEX sm_fts_gist_12_idx ON sm_fts_gist_12 USING gist (body_tsv tsvector_ops(siglen=12));
CREATE INDEX sm_fts_gist_120_idx ON sm_fts_gist_120 USING gist (body_tsv tsvector_ops(siglen=120));
CREATE INDEX sm_fts_gist_1200_idx ON sm_fts_gist_1200 USING gist (body_tsv tsvector_ops(siglen=1200));

-- GIN для полнотекстового поиска
CREATE INDEX sm_fts_gin_idx ON sm_fts_gin USING gin (body_tsv);

-- RUM для полнотекстового поиска, используя rum_tsvector_ops
CREATE INDEX sm_fts_rum_idx ON sm_fts_rum USING rum (body_tsv);

-- RUM для полнотекстового поиска, используя rum_tsvector_hash_ops
CREATE INDEX sm_fts_rum_hash_idx ON sm_fts_rum_hash USING rum (body_tsv rum_tsvector_hash_ops);

/* medium_messages */
-- ...

/* long_messages */
-- ...

Полный код для создания таблиц

CREATE TABLE words (id int, word text);
CREATE TABLE short_messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);
CREATE TABLE medium_messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);
CREATE TABLE long_messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);

INSERT INTO words SELECT i, word
FROM generate_series(1, 20000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(syllable, '')
	FROM (
		SELECT (string_to_array('th,he,an,er,in,re,nd,ou,en,on,ed,to,it,at,ha,ve,as,or,hi,ar,te,es,ver,hat,thi,tha,ent,ion,ith,ire,wit,eve,oul,uld,tio,ter,hen,era,hin,sho,ted,ome', ','))[floor(random() * 42) + 1]
		FROM generate_series(1, 3)
	) as f(syllable)
	WHERE i = i
) as f(word);

INSERT INTO short_messages SELECT i, body
FROM generate_series(1, 1000000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(word, ' ') FROM (
		SELECT word FROM words ORDER BY random() LIMIT 3 -- ~25 letters
	) as words
	WHERE i = i
) as f(body);

INSERT INTO medium_messages SELECT i, body
FROM generate_series(1, 1000000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(word, ' ') FROM (
		SELECT word FROM words ORDER BY random() LIMIT 30 -- ~250 letters
	) as words
	WHERE i = i
) as f(body);

INSERT INTO long_messages SELECT i, body
FROM generate_series(1, 1000000) as i
CROSS JOIN LATERAL (
	SELECT string_agg(word, ' ') FROM (
		SELECT word FROM words ORDER BY random() LIMIT 300 -- ~2500 letters
	) as words
	WHERE i = i
) as f(body);

CREATE TABLE sm_trgm_gist_12 AS (SELECT * FROM short_messages);
CREATE TABLE mm_trgm_gist_12 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_trgm_gist_12 AS (SELECT * FROM long_messages);

CREATE TABLE sm_trgm_gist_120 AS (SELECT * FROM short_messages);
CREATE TABLE mm_trgm_gist_120 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_trgm_gist_120 AS (SELECT * FROM long_messages);

CREATE TABLE sm_trgm_gist_1200 AS (SELECT * FROM short_messages);
CREATE TABLE mm_trgm_gist_1200 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_trgm_gist_1200 AS (SELECT * FROM long_messages);

CREATE TABLE sm_trgm_gin AS (SELECT * FROM short_messages);
CREATE TABLE mm_trgm_gin AS (SELECT * FROM medium_messages);
CREATE TABLE lm_trgm_gin AS (SELECT * FROM long_messages);

CREATE TABLE sm_fts_gist_12 AS (SELECT * FROM short_messages);
CREATE TABLE mm_fts_gist_12 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_fts_gist_12 AS (SELECT * FROM long_messages);

CREATE TABLE sm_fts_gist_120 AS (SELECT * FROM short_messages);
CREATE TABLE mm_fts_gist_120 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_fts_gist_120 AS (SELECT * FROM long_messages);

CREATE TABLE sm_fts_gist_1200 AS (SELECT * FROM short_messages);
CREATE TABLE mm_fts_gist_1200 AS (SELECT * FROM medium_messages);
CREATE TABLE lm_fts_gist_1200 AS (SELECT * FROM long_messages);

CREATE TABLE sm_fts_gin AS (SELECT * FROM short_messages);
CREATE TABLE mm_fts_gin AS (SELECT * FROM medium_messages);
CREATE TABLE lm_fts_gin AS (SELECT * FROM long_messages);

CREATE TABLE sm_fts_rum AS (SELECT * FROM short_messages);
CREATE TABLE mm_fts_rum AS (SELECT * FROM medium_messages);
CREATE TABLE lm_fts_rum AS (SELECT * FROM long_messages);

CREATE TABLE sm_fts_rum_hash AS (SELECT * FROM short_messages);
CREATE TABLE mm_fts_rum_hash AS (SELECT * FROM medium_messages);
CREATE TABLE lm_fts_rum_hash AS (SELECT * FROM long_messages);

CREATE INDEX sm_trgm_gist_12_idx ON sm_trgm_gist_12 USING gist (body gist_trgm_ops(siglen=12));
CREATE INDEX mm_trgm_gist_12_idx ON mm_trgm_gist_12 USING gist (body gist_trgm_ops(siglen=12));
CREATE INDEX lm_trgm_gist_12_idx ON lm_trgm_gist_12 USING gist (body gist_trgm_ops(siglen=12));

CREATE INDEX sm_trgm_gist_120_idx ON sm_trgm_gist_120 USING gist (body gist_trgm_ops(siglen=120));
CREATE INDEX mm_trgm_gist_120_idx ON mm_trgm_gist_120 USING gist (body gist_trgm_ops(siglen=120));
CREATE INDEX lm_trgm_gist_120_idx ON lm_trgm_gist_120 USING gist (body gist_trgm_ops(siglen=120));

CREATE INDEX sm_trgm_gist_1200_idx ON sm_trgm_gist_1200 USING gist (body gist_trgm_ops(siglen=1200));
CREATE INDEX mm_trgm_gist_1200_idx ON mm_trgm_gist_1200 USING gist (body gist_trgm_ops(siglen=1200));
CREATE INDEX lm_trgm_gist_1200_idx ON lm_trgm_gist_1200 USING gist (body gist_trgm_ops(siglen=1200));

CREATE INDEX sm_trgm_gin_idx ON sm_trgm_gin USING gin (body gin_trgm_ops);
CREATE INDEX mm_trgm_gin_idx ON mm_trgm_gin USING gin (body gin_trgm_ops);
CREATE INDEX lm_trgm_gin_idx ON lm_trgm_gin USING gin (body gin_trgm_ops);

CREATE INDEX sm_fts_gist_12_idx ON sm_fts_gist_12 USING gist (body_tsv tsvector_ops(siglen=12));
CREATE INDEX mm_fts_gist_12_idx ON mm_fts_gist_12 USING gist (body_tsv tsvector_ops(siglen=12));
CREATE INDEX lm_fts_gist_12_idx ON lm_fts_gist_12 USING gist (body_tsv tsvector_ops(siglen=12));

CREATE INDEX sm_fts_gist_120_idx ON sm_fts_gist_120 USING gist (body_tsv tsvector_ops(siglen=120));
CREATE INDEX mm_fts_gist_120_idx ON mm_fts_gist_120 USING gist (body_tsv tsvector_ops(siglen=120));
CREATE INDEX lm_fts_gist_120_idx ON lm_fts_gist_120 USING gist (body_tsv tsvector_ops(siglen=120));

CREATE INDEX sm_fts_gist_1200_idx ON sm_fts_gist_1200 USING gist (body_tsv tsvector_ops(siglen=1200));
CREATE INDEX mm_fts_gist_1200_idx ON mm_fts_gist_1200 USING gist (body_tsv tsvector_ops(siglen=1200));
CREATE INDEX lm_fts_gist_1200_idx ON lm_fts_gist_1200 USING gist (body_tsv tsvector_ops(siglen=1200));

CREATE INDEX sm_fts_gin_idx ON sm_fts_gin USING gin (body_tsv);
CREATE INDEX mm_fts_gin_idx ON mm_fts_gin USING gin (body_tsv);
CREATE INDEX lm_fts_gin_idx ON lm_fts_gin USING gin (body_tsv);

CREATE INDEX sm_fts_rum_idx ON sm_fts_rum USING rum (body_tsv);
CREATE INDEX mm_fts_rum_idx ON mm_fts_rum USING rum (body_tsv);
CREATE INDEX lm_fts_rum_idx ON lm_fts_rum USING rum (body_tsv);

CREATE INDEX sm_fts_rum_hash_idx ON sm_fts_rum_hash USING rum (body_tsv rum_tsvector_hash_ops);
CREATE INDEX mm_fts_rum_hash_idx ON mm_fts_rum_hash USING rum (body_tsv rum_tsvector_hash_ops);
CREATE INDEX lm_fts_rum_hash_idx ON lm_fts_rum_hash USING rum (body_tsv rum_tsvector_hash_ops);

Запросы для сравнения индексов

Скорость выполнения запросов для триграмм и полнотекстового поиска будем сравнивать отдельно.

Давайте увеличим параметр work_mem с 4 MB (по умолчанию) до 50 MB, чтобы при использовании GIN индекса вся bitmap-карта строилась до строк и помещалась в памяти (не будет lossy heap blocks). В этом случае поиск будет производиться быстрее. Экспериментальным путем было выяснено, что этого размера достаточно для наших таблиц.

В тестах будет использоваться PostgreSQL v13.4.

Запросы для триграмм

Для проверки скорости работы индексов, которые используют триграммы, будем выполнять 3 разных запроса: первые два с ILIKE для нахождения всех сообщений, которые содержат в себе поисковый запрос (один с сортировкой по релевантности, другой без), а третий c %>> (strict_word_similarity) для поиска сообщений, которые содержат в себе подстроку, похожую на поисковый запрос (с сортировкой по релевантности). Для strict_word_similarity будем использовать стандартное пороговое значение 0.5. Оператор %>> без сортировки не имеет смысла тестировать, т.к. первые результаты могут быть совсем слабо релевантные.

Все запросы будут выбирать первые 60 строк. Напомню, что GIN индекс не умеет возвращать первые результаты и возвращать их в нужном порядке. Следовательно, в начале будут возвращаться все записи, которые соответствуют критериям поиска, затем будет происходить сортировка и выбор первых 60 наиболее релевантных значений.

Пример запроса для триграмм с ILIKE без сортировки:

SELECT body FROM short_messages WHERE body ILIKE '%abcd%' LIMIT 60;

и с сортировкой:

SELECT body, 'abcd' <<<-> body AS dist FROM short_messages WHERE body ILIKE '%abcd%' ORDER BY dist LIMIT 60;

Пример запроса для триграмм с %>> с сортировкой:

SELECT body, 'abcd' <<<-> body AS dist FROM short_messages WHERE body %>> 'abcd' ORDER BY dist LIMIT 60;

Запросы для полнотекстового поиска

Разница между запросами с использованием GiST, GIN и RUM будет только в том, что для первых двух индексов для ранжирования результатов будем использовать функцию ts_rank_cd, а для RUM будем использовать оператор <=> (используется своя функция ts_score). Возвращать будем первые 60 результатов, которые наиболее релевантны поисковому запросу.

Пример запроса для полнотекстового поиска, используя GiST и GIN индексы:

SELECT body, ts_rank_cd(body_tsv, to_tsquery('abcd')) AS rank FROM short_messages WHERE body_tsv @@ to_tsquery('abcd') ORDER BY rank DESC LIMIT 60;

Пример запроса для полнотекстового поиска, используя RUM индекс:

SELECT body, body_tsv <=> to_tsquery('abcd') AS rank FROM short_messages WHERE body_tsv @@ to_tsquery('abcd') ORDER BY rank DESC LIMIT 60;

Будем сравнивать следующие 3 разновидности tsquery:

A – существует хотя бы одно слово.
A & B – существуют оба слова в любых местах.
A <-> B – существуют оба слова, которые идут друг за другом.

Скорость выполнения запросов

Напомню, что в таблице short_messages сообщения имеют длину ~25 символов, medium_messages – ~250 символов, long_messages – ~2500 символов. Скорость указана в миллисекундах.

Триграммы c ILIKE (без сортировки)

+--------------------+----------------+-----------------+---------------+
|       Индекс       | short_messages | medium_messages | long_messages |
+--------------------+----------------+-----------------+---------------+
| GiST (siglen=12)   |             11 |              11 |           172 |
| GiST (siglen=120)  |              9 |              14 |           183 |
| GiST (siglen=1200) |             10 |              11 |         29019 |
| GIN                |             11 |              12 |           495 |
+--------------------+----------------+-----------------+---------------+

Триграммы c ILIKE (с сортировкой)

+--------------------+----------------+-----------------+---------------+
|       Индекс       | short_messages | medium_messages | long_messages |
+--------------------+----------------+-----------------+---------------+
| GiST (siglen=12)   |          12965 |          171744 |       1232381 |
| GiST (siglen=120)  |          11015 |          176907 |       1306757 |
| GiST (siglen=1200) |           8355 |          191420 |       1478560 |
| GIN                |           1881 |           11879 |        442356 |
+--------------------+----------------+-----------------+---------------+

Триграммы c %>> (с сортировкой)

+--------------------+----------------+-----------------+---------------+
|       Индекс       | short_messages | medium_messages | long_messages |
+--------------------+----------------+-----------------+---------------+
| GiST (siglen=12)   |          19160 |          190739 |       1527176 |
| GiST (siglen=120)  |          16420 |          177249 |       1814276 |
| GiST (siglen=1200) |          16393 |          175266 |       2051626 |
| GIN                |            959 |           43536 |        761483 |
+--------------------+----------------+-----------------+---------------+

Полнотекстовый поиск (A)

+-----------------------------+----------------+-----------------+---------------+
|           Индекс            | short_messages | medium_messages | long_messages |
+-----------------------------+----------------+-----------------+---------------+
| GiST (siglen=12)            |             59 |           17201 |        110395 |
| GiST (siglen=120)           |             40 |           11912 |        133825 |
| GiST (siglen=1200)          |             26 |            2828 |        121076 |
| GIN                         |             12 |            1272 |         11601 |
| RUM (rum_tsvector_ops)      |              7 |            1374 |         20193 |
| RUM (rum_tsvector_hash_ops) |              7 |            1268 |         20372 |
+-----------------------------+----------------+-----------------+---------------+

Полнотекстовый поиск (A & B)

+-----------------------------+----------------+-----------------+---------------+
|           Индекс            | short_messages | medium_messages | long_messages |
+-----------------------------+----------------+-----------------+---------------+
| GiST (siglen=12)            |             25 |           16413 |       1155500 |
| GiST (siglen=120)           |             12 |            9457 |        160801 |
| GiST (siglen=1200)          |              4 |            2902 |         67691 |
| GIN                         |              6 |              15 |          1107 |
| RUM (rum_tsvector_ops)      |              5 |              16 |          1431 |
| RUM (rum_tsvector_hash_ops) |              4 |              17 |          1382 |
+-----------------------------+----------------+-----------------+---------------+

Полнотекстовый поиск (A <-> B)

+-----------------------------+----------------+-----------------+---------------+
|           Индекс            | short_messages | medium_messages | long_messages |
+-----------------------------+----------------+-----------------+---------------+
| GiST (siglen=12)            |           1463 |           17395 |       1119352 |
| GiST (siglen=120)           |            751 |            9226 |        151751 |
| GiST (siglen=1200)          |            427 |            5537 |         68095 |
| GIN                         |              8 |              13 |          1047 |
| RUM (rum_tsvector_ops)      |              9 |              13 |            96 |
| RUM (rum_tsvector_hash_ops) |              7 |              14 |            99 |
+-----------------------------+----------------+-----------------+---------------+

Размер индексов

Размер указан в мегабайтах.

Триграммы

+--------------------+----------------+-----------------+---------------+
|       Индекс       | short_messages | medium_messages | long_messages |
+--------------------+----------------+-----------------+---------------+
| GiST (siglen=12)   |            133 |             806 |          3200 |
| GiST (siglen=120)  |            111 |             613 |          4714 |
| GiST (siglen=1200) |            137 |             761 |          7373 |
| GIN                |             39 |             227 |           719 |
+--------------------+----------------+-----------------+---------------+

Полнотекстовый поиск

+-----------------------------+----------------+-----------------+---------------+
|           Индекс            | short_messages | medium_messages | long_messages |
+-----------------------------+----------------+-----------------+---------------+
| GiST (siglen=12)            |             50 |             211 |            77 |
| GiST (siglen=120)           |             41 |             162 |           198 |
| GiST (siglen=1200)          |             49 |             190 |          1619 |
| GIN                         |             16 |             218 |           949 |
| RUM (rum_tsvector_ops)      |             16 |             249 |          3114 |
| RUM (rum_tsvector_hash_ops) |             16 |             248 |          3114 |
+-----------------------------+----------------+-----------------+---------------+

Размер таблиц:

short_messages – 108 MB
medium_messages – 782 MB
long_messages – 7228 MB

Выводы

Ниже будут использоваться примеры кода с использованием таблицы messages. Для триграмм эта таблица выглядит следующим образом:

CREATE TABLE messages (id int, body text);

для полнотекстового поиска:

CREATE TABLE messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);

Триграммы

При использовании триграмм скорость поиска гораздо выше при использовании ILIKE без сортировки (не забывайте указывать минимум 3 символа). Cортировка и использование функций similarity сильно замедляют поиск.

При короткой длине строк (до ~250 символов) скорость поиска у GiST и GIN индексов примерно одинаковая. При этом GIN индекс сильно меньше, чем GiST индекс (в нашем тесте в 3.5 раза). Поэтому для коротких строк (напр, до 250 символов) стоит выбрать GIN индекс.

-- Без сортировки по релевантности для коротких строк (до ~250 символов)
CREATE INDEX messages_body_idx ON messages USING gin (body gin_trgm_ops);
SELECT body FROM messages WHERE body ILIKE '%abcd%' LIMIT 60;

Для более длинных строк (напр, ~2500 символов) GiST индекс будет работать гораздо быстрее (в нашем тесте в 3 раза), но он будет занимать больше места (в нашем тесте в 4.5 раза). Длину сигнатуры стоит выбрать 12 байт (по умолчанию).

-- Без сортировки по релевантности для длинных строк (напр, ~2500 символов)
-- Лучше использовать RUM с <-> (см вывод ниже)
CREATE INDEX messages_body_idx ON messages USING gist (body gist_trgm_ops);
SELECT body FROM messages WHERE body ILIKE '%abcd%' LIMIT 60;

Сортировку лучше не использовать, т.к. это очень дорогостоящая операция. Если она крайне необходима, то лучше выбрать GIN индекс, но при этом сильно ограничить длину строк (например, до 50 символов). В нашем тесте GIN индекс быстрее от 2 до 15 раз в зависимости от длины строк. То же самое при использовании функций similarity.

Полнотекстовый поиск

GIN и RUM индексы при полнотекстовом поиске оказались одинаковыми по производительности на коротких строках (до ~250 символов). На длинных строках (напр, ~2500 символов) RUM выигрывает, когда используется несколько слов с использованием оператора <->, который означает, что они должны следовать друг за другом. При использовании поисковой фразы, состоящей из 1 слова GIN оказывается быстрее, чем RUM (в наших тестах примерно в 2 раза; протестировано на разных словах).

При дополнительном тестировании без сортировки по релевантности, RUM индекс начал работать так же быстро, как и GIN, даже с поисковыми запросами, состоящими лишь из 1 слова. В итоге, RUM оказывается более производительным на длинных строках, при использовании оператора <-> (поисковый запрос можно преобразовать через phraseto_tsquery) без сортировки по релевантности.

-- Без сортировки по релевантности
CREATE INDEX messages_body_idx ON messages USING rum (body_tsv);
SELECT body FROM messages WHERE body_tsv @@ phraseto_tsquery('apple orange') LIMIT 60;

При использовании класса операторов rum_tsvector_hash_ops не наблюдается никакой экономии места на диске при использовании естественного языка, о чем и упоминали разработчики этого индекса. Поэтому лучше использовать rum_tsvector_ops (используется по умолчанию), чтобы в индексе хранились сами лексемы, а не их хеш и не требовалось лишних перепроверок, что будет замедлять процесс поиска.

Также нужно учитывать, что RUM индекс занимает больше места на диске, чем GIN индекс, т.к. в нем, помимо лексем, хранятся и их позиции. Например, при строках длиной ~2500 символов RUM индекс получился в 3 раза больше, чем GIN.

Если требуется сортировка по релевантности, то GIN индекс оказывается выгоднее, т.к. для поисковых запросов, состоящих из 1 слова он работает быстрее, чем RUM (в нашем тесте в 2 раза).

-- С сортировкой по релевантности
-- Лучше без сортировки
CREATE INDEX messages_body_idx ON messages USING gin (body_tsv);
SELECT body, ts_rank_cd(body_tsv, plainto_tsquery('apple')) AS rank FROM messages WHERE body_tsv @@ to_tsquery('apple') ORDER BY rank DESC LIMIT 60;

GiST индекс при полнотекстовом поиске оказался гораздо более медленным (в нашем тесте от 10 до 70 раз), чем GIN и RUM.

Что выбрать: триграммы или полнотекстовый поиск?

При дополнительном тесте RUM c <-> (без сортировки по релевантности) скорость поиска по таблицам short_messages, medium_messages и long_messages была одинаковой (~65 мс), независимо от того, что длина строк разная (от ~25 до ~2500 символов).

На таблицах short_messages и medium_messages с длинами сообщений ~25 и ~250 символов соответственно GIN индекс с использованием триграмм c ILIKE произвел поиск за 11-12 мс (у GiST индекса то же время, но его лучше не использовать в данном случае, т.к. он занимает гораздо больше места на диске), что быстрее, чем RUM. На таблице long_messages с длиной сообщений ~2500 символов GiST индекс с использованием триграмм c ILIKE произвел поиск за 172 мс, а GIN за 495 мс, что дольше, чем RUM.

В итоге, если важна скорость выполнения запросов и не так важно преимущество в распознавании разных форм слов (т.к. каждое слово при полнотекстовом поиске превращается в лексему), то:

для коротких строк (напр, до 250 символов) лучше использовать GIN индекс с использованием триграмм с ILIKE без сортировки с длиной сигнатуры 12 байт (по умолчанию). Например, поиск по названию компаний.
для более длинных строк (напр, ~2500 символов) лучше использовать RUM индекс с использованием полнотекстового поиска c <-> без сортировки. Например, поиск по сообщениям или содержаниям документов.

Если важно, чтобы при поиске учитывались разные формы слов, то можно для любой длины строк использовать RUM индекс с использованием полнотекстового поиска c <-> без сортировки.

Шпаргалка

-- Для коротких строк (напр, до 250 символов). Напр, поиск по названию компаний.
-- GIN индекс с использованием триграмм с ILIKE без сортировки с длиной сигнатуры 12 байт (по умолчанию).
CREATE TABLE messages (id int, body text);
CREATE INDEX messages_body_idx ON messages USING gin (body gin_trgm_ops);
SELECT body FROM messages WHERE body ILIKE '%abcd%' LIMIT 60;

-- Для более длинных строк (напр, ~2500 символов). Напр, поиск по сообщениям или содержаниям документов.
-- RUM индекс с использованием полнотекстового поиска c <-> без сортировки.
CREATE TABLE messages (id int, body text, body_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED);
CREATE INDEX messages_body_idx ON messages USING rum (body_tsv);
SELECT body FROM messages WHERE body_tsv @@ phraseto_tsquery('apple orange') LIMIT 60;

Сравнение индексов в PostgreSQL для поиска по тексту (часть 2)

Подготовка

Полный код для создания таблиц

Запросы для сравнения индексов

Запросы для триграмм

Запросы для полнотекстового поиска

Скорость выполнения запросов

Триграммы c ILIKE (без сортировки)

Триграммы c ILIKE (с сортировкой)

Триграммы c %>> (с сортировкой)

Полнотекстовый поиск (A)

Полнотекстовый поиск (A & B)

Полнотекстовый поиск (A <-> B)

Размер индексов

Триграммы

Полнотекстовый поиск

Выводы

Триграммы

Полнотекстовый поиск

Что выбрать: триграммы или полнотекстовый поиск?

Шпаргалка

Похожие статьи

Как получить N строк для каждой группы в SQL

Как лучше хранить диапазоны дат в PostgreSQL

Как выбрать случайные строки в SQL