postgres: индексирование int и text col для ускорения аналогичного оператора [duplicate]

FTS не поддерживает LIKE

Ранее принятый ответ был неправильным. Полнотекстовый поиск с его полными текстовыми индексами вовсе не для оператора LIKE, он имеет свои собственные операторы и не работает для произвольных строк. Он работает с словами на основе словарей и заканчивается. Он поддерживает сопоставление префикса для слов , но не с оператором LIKE:

• Получить частичное совпадение с индексом GIN Столбец TSVECTOR

Триграммные индексы для LIKE

Установите дополнительный модуль pg_trgm , который предоставляет классы операторов для Индексы триграмм GIN и GiST для поддержки всех шаблонов LIKE и ILIKE, а не только левых:

Пример индекса:

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

Или:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

• Разница между значениями GiST и GIN

Пример запроса:

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

триграмм? Что относительно коротких строк?

Слова с менее чем тремя буквами в индексированных значениях все еще работают. Руководство:

Каждое слово считается префиксом двух пробелов, а при определении набора триграмм, содержащихся в строке, должно быть пробел.

И шаблоны поиска с менее чем тремя буквами? Руководство:

Для поиска LIKE и регулярных выражений следует иметь в виду, что шаблон без экстрагируемых триграмм будет дегенерировать до сканирования полного индекса.

Значение, что сканирование индекса / растрового индекса все еще работает (планы запросов для подготовленного оператора не будут прерываться), он просто не будет покупать вам лучшую производительность. Как правило, никаких больших потерь нет, так как строки с 1 или 2 буквами вряд ли избирательны (более чем на несколько процентов от базовых табличных совпадений), а поддержка индексов не улучшит производительность, потому что полное сканирование таблицы выполняется быстрее.

text_pattern_ops для сопоставления префикса

Для только левых привязанных шаблонов (без ведущего шаблона) вы получаете оптимальное значение с помощью подходящего класса операторов ] для индекса btree: text_pattern_ops или varchar_pattern_ops. Как встроенные функции стандартных Postgres, так и дополнительный модуль. Пример:

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

Пример запроса:

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no leading wildcard

Или , [ если вы должны запускать свою базу данных с помощью 'C' locale (фактически no locale), тогда все сортируется в соответствии с порядком байтов в любом случае и простой индекс btree с оператором по умолчанию класс выполняет эту работу.

Подробнее, пояснения, примеры и ссылки в этих связанных ответах на dba.SE:

• Совпадение шаблонов с LIKE, ПОДОБНЫМИ или регулярные выражения в PostgreSQL
• Как реализовано LIKE?
• Поиск похожих строк с PostgreSQL быстро