Функции в сравнениях (WHERE) в SELECT-запросах

При наисании SELECT-запросов к базе часто бывает соблазн выбрать что-то за конкретную дату или месяц, либо, например, сравнить две строки без учета регистра — это те случаи, когда часто неоправданно используются Postgresql-функции в условиях запроса.

Примеры:

SELECT *
FROM orders
WHERE date_trunc('day', update_date) = NOW() - INTERVAL '1 day';

или

SELECT id
FROM products
WHERE lower(product_name) = ‘teapot 123’

Допустим, у нас в соответсвующих таблицах есть индексы на поля update_date и product_name.

Что плохого в таких запросах

Поскольку в данном случае в сравнении участвует функция, то индекс, созданный для поля, не будет использоваться. Соответственно, будет последовательный поиск по таблице с последующей фильтрацией, а это очень медленно.

Что делать

Есть 2 варианта:

1. Переписать запрос так, чтобы он не использовал функцию
   для запроса с датой, это можно сделать, например, так:

   SELECT *
   FROM orders
   WHERE update_date>= CURRENT_DATE - INTERVAL '1 day'
   AND update_date< CURRENT_DATE;

    

2. Создать индекс не для поля, а для используемой в запросе функции

   CREATE INDEX idx_orders_update_date
   ON orders (date_trunc('day', update_date));

   и

   CREATE INDEX idx_products_product_name
   ON products (to_lower (product_name));

    

Какой из методов выбрать, решать вам. В некоторых случаях переписать вопрос без использования функций невозможно, в других — неписание запроса с функциями является удобной и широко распространённой практикой в компании, так что от неё никто не хочет отказываться — тогда выходом станет написание индекса для функции.