PostgreSQL как вариации производительности запросов

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

The ранее принятый ответ был неверным. Полнотекстовый Поиск С его полнотекстовыми индексами это не на LIKE оператор вообще, он имеет свои собственные операторы и не работает для произвольных строк. Он работает на слова на основе словарей и вытекающие. Это тут поддержка префикс соответствия для слов, а не LIKE оператор:

• получить частичное совпадение с индексированным джином столбцом TSVECTOR

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

установите дополнительный модуль pg_trgm, который предоставляет классы для оператора джин и Гист триграммы индексы в поддержку все 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 index

запрос:

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

триграмм? А как насчет более коротких строк?

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

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

и шаблоны поиска с менее чем 3 буквами? инструкции:

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

это означает, что сканирование индекса / растрового индекса все еще работает (планы запросов для подготовленного оператора не будут ломаться), он просто не купит вас лучше спектакль. Как правило, нет больших потерь, так как 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 (эффективно нет locale), то все сортируется в соответствии с порядком байтов в любом случае и простой индекс btree по умолчанию класс оператора выполняет эту работу.

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

• сопоставление шаблонов с подобными, похожими или регулярными выражениями в PostgreSQL
• как это реализовано?
• поиск похожих строк с PostgreSQL быстро