Влияние на производительность пустого LIKE в подготовленном утверждении

Question

Я установил GiST pg_trgm индекс на name столбец files Таблица.

Упрощенный запрос подготовленного оператора выглядит следующим образом:

SELECT * FROM files WHERE name LIKE $1;

$1 парам будет состоять из % + пользовательский запрос + %, Поскольку входные данные также могут быть пустой строкой, это может привести к %%,

Делает "пустой" LIKE (%%) привести к снижению производительности? Должен ли я создать новый запрос в этом случае, или это не имеет значения?

Answer 1

Postgres 9.2 или выше, как правило, достаточно умен, чтобы понять, что условие

WHERE name LIKE '%%'

не является избирательным и прибегает к последовательному сканированию, игнорируя индекс GiST - даже с подготовленными операторами. Вы платите небольшую цену за бесполезные условия.

В Postgres 9.1 или более ранней версии я бы создал отдельный запрос для особого случая.

Сравните раздел заметок для PREPARE Заявление в руководстве для версий 9.1, 9.2 и 9.3.

Проверьте себя

Подготовьте заявление и запустите EXPLAIN ANALYZE тестировать:

PREPARE plan1 (text) AS
SELECT  * FROM file
WHERE   name LIKE $1;

EXPLAIN ANALYZE EXECUTE plan1('%123%');

EXPLAIN ANALYZE EXECUTE plan1('%%');

Планы обычно кэшируются на время сеанса.

Альтернативный запрос

Независимо от используемой версии, если вы всегда выполняете полнотекстовый поиск (подстановочные знаки слева и справа), этот запрос должен быть быстрее для подготовленного оператора:

SELECT * FROM files WHERE name LIKE ('%' || $1 || '%');

И передать шаблон без добавления подстановочных знаков (%), конечно. Таким образом, Postgres знает, что нужно ожидать шаблон, заключенный в шаблоны, во время планирования.

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