Поиск по индексам выражений
Я создаю индекс выражения, который подстроит поле источника, чтобы избежать переполнения ограничения в 2172 символа для B-деревьев:
CREATE INDEX record_changes_log_detail_old_value_ix_btree
ON record_changes_log_detail
USING btree ((substring(old_value,1,1024)::text) text_pattern_ops);
Для записи:
-- Postgres 11.4 на RDS, 11.5 на macOS дома.
-- Таблица record_changes_log_detail имеет около 8 МБ в моей тестовой настройке.
-- Поле old_value имеет тип citext.
-- Значения в диапазон длины от 1 символа до более чем 5000. Большинство короткие.
Этот поиск использует указанный выше индекс:
select * from record_changes_log_detail
where substring(old_value,1,1024) = 'Gold Kerrison Neuro';
Этот поиск не использует индекс:
where old_value = 'Gold Kerrison Neuro';
Я нашел это удивительным и настоящим обломом. Если я правильно понимаю комментарий jjanes к другому вопросу, планировщик распознает, что индекс применяется только тогда, когда в вашем операторе запроса используется точно такое же выражение. Другими словами, любой, кто пишет запрос, должен знать подробности определения индекса, иначе индекс не будет использоваться.
Я предполагал, что при построении индекса выражения сокращенный/извлеченный/и т.д. значение было сохранено и что планировщик проверит его. Есть ли способ подсказать планировщику, кроме повторения всего выражения? В индексе есть правильные данные, но планировщик, похоже, пропускает их.
Я добавляю немного деталей на основе ответа Эрвина Брандштеттера:
У меня множество подобных ситуаций, поэтому я углубляюсь в детали. В этом случае из моих ~8 миллионов строк только 6 имеют значения длиннее 2172 символов, а 99,93% значений составляют 100 символов или меньше.
На что я надеюсь, так это на подход, который легко подхватить кому-то еще. Поле тени вполне может быть ответом, поскольку необходимость знать точные детали построения индекса кажется мне совершенно неправильной видимостью. Теневое поле не страдает от этой проблемы, если вы знаете, как его использовать. Я мог бы либо заполнить его LEFT (old_field, 128), либо какой-либо другой длиной, либо texthash (old_field), как вы упомянули. Я буду экспериментировать с этим. Мои данные настолько искажены до коротких значений, что хеширование, похоже, приводит к высокой частоте коллизий.
Что бы это ни стоило, команда и я исходим из системы, в которой текстовые поля молча обрезаются до 1024 символов при индексации в B-дереве. Это полностью прозрачно для пользователя, и поиск сверяется с индексом. Яблоки и свечи зажигания, я знаю. Дело в том, что я не ожидаю, что Postgres будет ИИ, но я прихожу с неточными априорными данными. Так что спасибо вам и всем остальным за то, что помогли мне узнать больше о том, как на самом деле работает Postgres.
Следовать за
На этот вопрос был дан ответ, но я хочу добавить некоторые дополнительные сведения об архивах. Я многому научился многому из старых ответов, некоторые из них очень старые. Итак, немного информации на будущее. Я пробовал четыре решения:
- B-дерево на части поля citext.
- B-дерево по хешу поля citext.
- Хэш-индекс поля citext.
- Триграммный GIN-индекс поля citext.
Поскольку, похоже, нет никакого способа получить запросы типа LIKE к citext, где текст может быть слишком длинным, цель состоит в том, чтобы создать индекс для =. Любой из трех вышеперечисленных будет работать нормально, но они немного отличаются. Вот код установки для теста:
DROP INDEX IF EXISTS record_changes_log_detail_old_value_ix_btree;
CREATE INDEX record_changes_log_detail_old_value_ix_btree
ON record_changes_log_detail
USING btree ((left(old_value,1024)::citext) citext_pattern_ops);
DROP INDEX IF EXISTS record_changes_log_detail_old_value_hash_ix_btree;
CREATE INDEX record_changes_log_detail_old_value_hash_ix_btree
ON record_changes_log_detail
USING btree (hashtext(old_value));
DROP INDEX IF EXISTS record_changes_log_detail_old_value_ix_hash;
CREATE INDEX record_changes_log_detail_old_value_ix_hash
ON record_changes_log_detail
USING hash (old_value);
DROP INDEX IF EXISTS record_changes_log_detail_old_value_ix_tgrm;
CREATE INDEX record_changes_log_detail_old_value_ix_tgrm
ON record_changes_log_detail
USING gin (old_value gin_trgm_ops);
VACUUM ANALYZE;
Каждый из этих индексов работает для поиска записи, но с разным синтаксисом:
-- Uses the LEFT()::citext index
explain analyze
select * from record_changes_log_detail
where left(old_value,1024)::citext = 'Gold Kerrison Neuro';
-- Uses the HASH index
explain analyze
select * from record_changes_log_detail
where old_value = 'Gold Kerrison Neuro';
-- Uses the HASHTEXT() index
explain analyze
select * from record_changes_log_detail
where hashtext(old_value) = hashtext('Gold Kerrison Neuro');
-- Uses the tri-gram() index
explain analyze
select * from record_changes_log_detail
where old_value::text LIKE '%Gold Kerrison Neuro%';
Хэш-индекс обеспечивает лучший синтаксис, потому что он прозрачен... но хэш-индекс хуже во всех остальных отношениях. Вот поиск по размеру и результаты. Я добавил сюда время построения индекса вручную.
select
'B-tree on LEFT(old_value,1024)::citext' as index_description,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_ix_btree')) as pretty
union all
select
'B-tree on HASHTEXT(old_value)' as index_description,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_hash_ix_btree')) as pretty
union all
select
'Hash index on old_value' as index_description,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_ix_hash')) as pretty
union all
select
'GIN tri-gram index on old_value' as index_description,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_ix_tgrm')) as pretty;
index_description pretty seconds
B-tree on LEFT(old_value,1024)::citext 238 MB 38
B-tree on HASHTEXT(old_value) 166 MB 7
Hash index on old_value 362 MB 3,802
GIN tri-gram index on old_value 106 MB 56
Я бы сказал, что эти данные плохо подходят для хэш-индекса, поэтому, пожалуйста, не принимайте эти результаты за типичные. Тем не менее, время и размер довольно плохие. Явным победителем для = searches является умное предложение Эрвина Брандштеттера по B-дереву хэша. Хороший! Дополнительный синтаксический сахар, необходимый для поиска, здесь не так плох, как для индекса на основе LEFT. В будущем это выиграет от улучшений B-дерева, обещанных в PG 12.
И еще одна хорошая новость: триграммный индекс просто великолепен. Laurenz Albe предложил попробовать, и я счастлив, что сделал это. Мгновенный поиск содержит/подобно, отлично. Это как раз то, что мне нужно. Опять же, я сомневаюсь, что размер индекса типичен... мои данные странные. Для тех, кто использует citext, обратите внимание, что вы должны привести условие поиска к тексту для использования индекса:
select * from record_changes_log_detail
where old_value::text LIKE '%Gold Kerrison Neuro%';
Для тех, кто не знает, триграммы — это экземпляр n-грамм длиной 3. N-граммы иногда называют q-граммами или k-граммами. Как бы то ни было, это одно и то же. Из всех наивных (невероятностных или статистических) алгоритмов нечеткого сопоставления текста это, вероятно, лучший. Надежный для разных наборов данных и языков, гибкий, потрясающий. Так что я очень доволен тем, как хорошо это работает в Postgres.
