Comments 22
Спасибо за ваши статьи!
Скажите, пожалуйста, есть ли практическая разница для приложений в том, чтобы иметь отдельное значение doc_tsv вместо построения индекса gin(to_tsvector(doc)) и в запросах использовать to_tsvector(doc) @@ to_tsquery('...'). Такой индекс ведь используется при запросе, и не расходуется дополнительное место под doc_tsv?
На здоровье!
Начнем с того, что просто так индекс по выражению построить не получится:
fts=# create index on mail_messages using gin(to_tsvector(body_plain));
ERROR: functions in index expression must be marked IMMUTABLEФункции в выражении должны иметь класс изменчивости immutable, что означает, что функция всегда выдает один и тот же результат при одинаковых входных параметрах. Но функция to_tsvector зависит не только от параметров, но и от настроек полнотекстового поиска (в частности, от конфигурационного параметра default_text_search_config). Если настройки изменить, то индекс внезапно превратится в тыкву.
Тут как бы можно возразить: ведь и с отдельным полем doc_tsv поиск перестанет работать, как задумывалось, если менять настройки на лету. Поэтому можно всех (главное, чтобы не себя) обмануть, обернув to_tsvector другой функцией, объявленной как immutable:
fts=# create function to_tsvector_immutable(t text) returns tsvector as $$
select to_tsvector(t);
$$ immutable language sql;
CREATE FUNCTION
fts=# create index on mail_messages using gin(to_tsvector_immutable(body_plain));
CREATE INDEXТеперь можно и про практическую разницу:
Первое. Индекс по выражению подразумевает отдельную статистику. Поэтому с таким индексом планировщик, возможно, будет строить более точные планы. Это плюс.
Второе. Ощутимой разницы в производительности между первым и вторым подходом не будет не будет, но только до тех пор, пока результат можно точно вычислить по индексу. Например:
fts=# explain (analyze, costs off)
select * from mail_messages
where tsv @@ to_tsquery('hello & hackers');
QUERY PLAN
-----------------------------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual time=3.634..5.840 rows=1776 loops=1)
Recheck Cond: (tsv @@ to_tsquery('hello & hackers'::text))
Heap Blocks: exact=1503
-> Bitmap Index Scan on mail_messages_tsv_idx (actual time=3.204..3.204 rows=1776 loops=1)
Index Cond: (tsv @@ to_tsquery('hello & hackers'::text))
Planning time: 0.297 ms
Execution time: 5.999 ms
(7 rows)и почти то же самое (execution time), плюс-минус флюктуации:
fts=# explain (analyze, costs off)
select * from mail_messages
where to_tsvector_immutable(body_plain) @@ to_tsquery('hello & hackers');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual time=3.745..5.576 rows=1776 loops=1)
Recheck Cond: (to_tsvector_immutable(body_plain) @@ to_tsquery('hello & hackers'::text))
Heap Blocks: exact=1503
-> Bitmap Index Scan on mail_messages_to_tsvector_immutable_idx (actual time=3.464..3.464 rows=1776 loops=1)
Index Cond: (to_tsvector_immutable(body_plain) @@ to_tsquery('hello & hackers'::text))
Planning time: 0.377 ms
Execution time: 5.706 ms
(7 rows)Но если дело дойдет до перепроверки по документу, то во втором случае придется вычислять выражение to_tsvector_immutable(body_plain), что, естественно, дорого.
Пример, когда это необходимо — фразовый поиск. Мы можем найти документы, в которых слова hello и hackers не просто оба встречаются, но и находятся рядом друг с другом:
fts=# explain (analyze, costs off)
select * from mail_messages
where tsv @@ to_tsquery('hello <-> hackers');
QUERY PLAN
-----------------------------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual time=5.504..42.441 rows=259 loops=1)
Recheck Cond: (tsv @@ to_tsquery('hello <-> hackers'::text))
Rows Removed by Index Recheck: 1517
Heap Blocks: exact=1503
-> Bitmap Index Scan on mail_messages_tsv_idx (actual time=4.640..4.640 rows=1776 loops=1)
Index Cond: (tsv @@ to_tsquery('hello <-> hackers'::text))
Planning time: 0.380 ms
Execution time: 42.540 ms
(8 rows)А вот второй вариант безнадежно проигрывает:
fts=# explain (analyze, costs off)
select * from mail_messages
where to_tsvector_immutable(body_plain) @@ to_tsquery('hello <-> hackers');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual time=40.877..1211.439 rows=259 loops=1)
Recheck Cond: (to_tsvector_immutable(body_plain) @@ to_tsquery('hello <-> hackers'::text))
Rows Removed by Index Recheck: 1517
Heap Blocks: exact=1503
-> Bitmap Index Scan on mail_messages_to_tsvector_immutable_idx (actual time=4.677..4.677 rows=1776 loops=1)
Index Cond: (to_tsvector_immutable(body_plain) @@ to_tsquery('hello <-> hackers'::text))
Planning time: 0.415 ms
Execution time: 1211.517 ms
(8 rows)Это, конечно, минус.
(На самом деле, тут нужен RUM, но об этом в следующий раз.)
Ну и третье: при втором подходе не требуется места для хранения «лишнего» поля. Это снова плюс.
Спасибо за подробное объяснение! :)
Я индекс немного по-другому строил, по-этому не столкнулся с must be marked IMMUTABLE:
test=# CREATE INDEX ON test USING GIN(to_tsvector('english'::regconfig, doc));
CREATE INDEXУ такого подхода нет минусов кроме того, что поиск только для заранее заданного языка будет использовать индекс?
Потому что перепроверка выполняется по tsvector-у; в первом случае он уже присутствует в таблице в отдельном поле tsv (и в этом случае body_plain нам не нужен), а во втором — его сначала приходится реконструировать вызовом to_tsvector(body_plain).
а во втором случае при поиске по фразе вы получили ожидаемое замедление в случае генерации tsv на лету.
Вопрос: что не так с вариантом hello & hackers, почему вариант с хранением результата не вынес в одну калитку вариант с вычислением на лету?
У меня есть предположение, что тут где-то вмешался кеш, если бы вы вывели не только потраченное время, но еще и ожидаемую оценку, это могло бы проясниться.
А в варианте hello & hackers не нужна перепроверка, вся информация есть в индексе (и не важно, как он построен: по полю или по выражению). Поэтому и результат плюс-минус одинаковый.
Наличие в плане Recheck Cond не должно вводить в заблуждение: эта строка отображается всегда, выполняется перепроверка или нет.
Да это ввело меня в заблуждение :(.
Но я так понимаю, если мы используем Gist который Lossy, перепроверка будет происходить всегда, и повторный распарсинг тоже будет происходить всегда?
Также вы в статье привели пример поиска редкого и частого слова в одном запросе, т.е. гипотетически такие сочетания могут привести, к тому что при неудачных сочетаниях лексем проверка начнет требовать повторного разбора вектора. Это может быть незаметно на маленьких body, но если там примерно такого размера посты как этот, разница станет достаточно заметной, даже если таких проверок надо будет проводить немного.
PS Спасибо за хорошую серию. Статью про GIN ждал с интересом.
GiST да, перепроверяет. И только если документы совсем крошечные (меньше примерно 1/16 страницы, то есть около 500 байт при 8K), то tsvector будет храниться внутри индекса.
Насчет частых и редких лексем — хорошее замечание. Еще одна причина вынести tsvector в отдельное поле.
P. S. Пожалуйста! Следующая будет про RUM, это тоже интересно в контексте полнотекстового поиска.
я про нее сделал кратенькую заметочку Yo-ho and a bottle of GIN
В двух словах: GIN требует приведения типов четкого и если он создавался с bigint, то надо в явном виде указывать ::bigint, в то время как b-tree индекс обычный такого не требует.
Вот для этого как раз и нужна магия семейств операторов: объяснить планировщику, что с разными типами можно работать похожим образом. Просто для B-дерева это сделали, а для GIN — почему-то нет.
Но ведь все в наших руках (:
Для полноты картины приведу тут еще раз команды для создания таблицы и расширения:
cards=# CREATE TABLE cards (
title varchar NOT NULL,
title_tsv tsvector NOT NULL,
collection_id bigint NOT NULL,
id serial
);
CREATE TABLE
cards=# create extension btree_gin;
CREATE EXTENSIONОбъявляем свое собственное семейство integer_ops:
cards=# CREATE OPERATOR FAMILY integer_ops using gin;
CREATE OPERATOR FAMILYСоздаем два класса операторов в этом семействе (я ограничусь int4=integer и int8=bigint). Команды я, конечно, не сам придумал, а честно подсмотрел в расширении.
К сожалению, эти классы не получится объявить классами по умолчанию, потому что расширение btree_gin нас опередило, но это беда не большая.
cards=# CREATE OPERATOR CLASS int4_family_ops
FOR TYPE int4 USING gin FAMILY integer_ops
AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 btint4cmp(int4,int4),
FUNCTION 2 gin_extract_value_int4(int4, internal),
FUNCTION 3 gin_extract_query_int4(int4, internal, int2, internal, internal),
FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
FUNCTION 5 gin_compare_prefix_int4(int4,int4,int2, internal),
STORAGE int4;
CREATE OPERATOR CLASS
cards=# CREATE OPERATOR CLASS int8_family_ops
FOR TYPE int8 USING gin FAMILY integer_ops
AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 btint8cmp(int8,int8),
FUNCTION 2 gin_extract_value_int8(int8, internal),
FUNCTION 3 gin_extract_query_int8(int8, internal, int2, internal, internal),
FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
FUNCTION 5 gin_compare_prefix_int8(int8,int8,int2, internal),
STORAGE int8;
CREATE OPERATOR CLASSТеперь нам надо добавить в семейство дополнительные операторы сравнения, которые умеют работать не только к (int4,int4) или (int8,int8), но и с (int4,int8) и (int8,int4):
cards=# ALTER OPERATOR FAMILY integer_ops USING gin add
OPERATOR 1 <(int4,int8),
OPERATOR 2 <=(int4,int8),
OPERATOR 3 =(int4,int8),
OPERATOR 4 >=(int4,int8),
OPERATOR 5 >(int4,int8);
ALTER OPERATOR FAMILY
cards=# ALTER OPERATOR FAMILY integer_ops USING gin add
OPERATOR 1 <(int8,int4),
OPERATOR 2 <=(int8,int4),
OPERATOR 3 =(int8,int4),
OPERATOR 4 >=(int8,int4),
OPERATOR 5 >(int8,int4);
ALTER OPERATOR FAMILYНу и все. Создаем индекс (указав наш класс операторов):
cards=# CREATE INDEX index_examples_fts_with_collection ON cards USING
GIN( title_tsv, collection_id int8_family_ops);
CREATE INDEXИ проверяем. Я отключил последовательное сканирование, потому что Постгрес его предпочитает, когда в таблице пусто.
cards=# SET enable_seqscan = off;
SET
cards=# EXPLAIN (COSTS OFF) SELECT id, title FROM "cards"
WHERE (title_tsv @@ to_tsquery( 'english', '(o:*|o)')) AND collection_id = 624
LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Limit
-> Bitmap Heap Scan on cards
Recheck Cond: ((title_tsv @@ '''o'':* | ''o'''::tsquery) AND (collection_id = 624))
-> Bitmap Index Scan on index_examples_fts_with_collection
Index Cond: ((title_tsv @@ '''o'':* | ''o'''::tsquery) AND (collection_id = 624))
(5 rows)Вуаля!
LIKE an apple-pie ORDER BY или второй способ поломать работу с индексами.
В вашем примере слома не произойдет, он касается varchar_pattern_ops :)) потому что там операторы ~<~, ~>~ и пр.
С GIN-ом можно попробовать закрутить до предела gin_fuzzy_search_limit, хотя сортировать все равно придется.
А еще лучше использовать RUM, он умеет отдавать в нужном порядке построчно. Не от балды десять слов, а, например, в порядке частоты встречаемости (если такая информация есть в таблице, конечно). Об этом на днях будет следующая статья.
Можно попросить чуть подробнее расписать про « Интересно, что GIN поддерживает создание многоколоночных индексов. При этом, в отличие от обычного B-дерева, в нем будут храниться не составные ключи, а по-прежнему отдельные элементы, но только с указанием номера столбца.»?
Например, есть btree_gin индекс по двум столбцам (user_id, some_array). У user_id относительно низкая cardinality (скажем, тысячи), а вот у элементов some_array cardinality высокая (много уникальных), плюс на каждого юзера документов по миллиону (для простоты). Правильно ли я понимаю, что это плохая история, и для каждого user_id будет огромный список из миллиона элементов, который в запросах «where user_id=X and some_array && ‘{1234}’» будет бесполезно выгребаться (в то время как по 1234 результатов пара десятков)?
Может, можно как-то включить режим составного ключа, когда ключи формируются вида user_id-1234 для каждого элемента массива some_array?
Что-то я пропустил в свое время ваш вопрос, прошу прощения.
В такой ситуации, как вы описываете, лучше сделать индекс только по some_array и не смущать планировщик.
А если вы создадите индекс по двум столбцам, то одно из двух. Либо планировщик сообразит, что проще найти несколько документов по some_array, а потом перепроверить user_id без индекса (как описано в разделе «Частые и редкие лексемы»), либо не сообразит - тогда, конечно, будет плоховато. Но это надо проверять на конкретных данных.
Огромное спасибо за серию статей по индексам! Потрясающе написано.
Подскажите, а почему на второй картинке мы спускаемся в лексему "нек"? Как GIN понимает, что чтобы найти "кудряв", надо опуститься именно в "нек"?
И еще возник вопрос по поиску по JSONB. Если структура большинства JSON объектов одинаковая (напр, как в примере во всех объектах есть days_of_week), то получается при поиске по ключу-значению (напр, '{"days_of_week": [5]}') будут всегда в начале выбираться все записи (т.к. у них всех есть ключ days_of_week), а потом функция согласованности будет выбирать те, которые имеют нужное значение? Получается, что поиск по ключу-значению будет всегда медленным, если ключ содерживатся почти во всех записях, верно?
Да, вы правильно понимаете, с оговоркой на подраздел «Частые и редкие лексемы» - они могут обрабатываться по-разному.
Но специально для этого случае есть класс операторов jsonb_path_ops, и я только сейчас понял, что ничего про него не написал. Он складывает в индекс не отдельные ключи и значения, а целиком путь от корня JSON до значения.
Вот место в документации, где про это написано.
Индексы в PostgreSQL — 7