SQL — это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе, и вот это — совсем не одно и то же:
WHERE fncondX() AND fncondY()
= fncondX() && fncondY()
В процессе оптимизации плана исполнения запроса PostgreSQL может произвольным образом «переставлять» эквивалентные условия, не вычислять какие-то из них для отдельных записей, относить к условию применяемого индекса… Короче, проще всего считать, что вы заранее не можете управлять тем, в каком порядке будут (и будут ли вообще) вычисляться равноправные условия.
Поэтому если управлять приоритетом все-таки хочется, надо структурно сделать эти условия неравными с помощью условных выражений и операторов.
Данные и работа с ними — основа нашего комплекса СБИС, поэтому нам очень важно, чтобы операции над ними выполнялись не только корректно, но и эффективно. Давайте посмотрим на конкретных примерах, где могут быть допущены ошибки вычисления выражений, а где стоит улучшить их эффективность.
#0: RTFM
Стартовый пример из документации:
Когда порядок вычисления важен, его можно зафиксировать с помощью конструкции
CASE
. Например, такой способ избежать деления на ноль в предложенииWHERE
ненадёжен:SELECT ... WHERE x > 0 AND y/x > 1.5;
Безопасный вариант:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Применяемая так конструкция
CASE
защищает выражение от оптимизации, поэтому использовать её нужно только при необходимости.
#1: условие в триггере
BEGIN
IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
...
END IF;
RETURN NEW;
END;
Вроде все выглядит хорошо, но… Никто не обещает, что вложенный SELECT
не будет выполняться при ложности первого условия. Поправим с помощью вложенных IF
:
BEGIN
IF cond(NEW.fld) THEN
IF EXISTS(SELECT ...) THEN
...
END IF;
END IF;
RETURN NEW;
END;
Теперь посмотрим внимательно — все тело триггерной функции оказалось «завернуто» в IF
. А это значит, что нам ничто не мешает вынести это условие из процедуры с помощью WHEN
-условия:
BEGIN
IF EXISTS(SELECT ...) THEN
...
END IF;
RETURN NEW;
END;
...
CREATE TRIGGER ...
WHEN cond(NEW.fld);
Такой подход позволяет гарантированно сэкономить ресурсы сервера при ложности условия.
#2: OR/AND-цепочка
SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)
В неприятном случае можно получить, что оба EXISTS
будут «истинными», но оба и выполнятся.
Но если мы точно знаем, что один из них бывает «истинным» много чаще (или «ложным» — для AND
-цепочки) — нельзя ли как-то «повысить его приоритет», чтобы второй не выполнялся лишний раз?
Оказывается, можно — алгоритмически подход близок к теме статьи PostgreSQL Antipatterns: редкая запись долетит до середины JOIN.
Давайте просто «засунем под CASE» оба эти условия:
SELECT ...
WHERE
CASE
WHEN EXISTS(... A) THEN TRUE
WHEN EXISTS(... B) THEN TRUE
END
В данном случае мы не определяли ELSE
-значение, то есть в случае ложности обоих условий CASE
вернет NULL
, что трактуется как FALSE
в WHERE
-условии.
Данный пример можно скомбинировать и иначе — на вкус и цвет:
SELECT ...
WHERE
CASE
WHEN NOT EXISTS(... A) THEN EXISTS(... B)
ELSE TRUE
END
#3: как [не] надо писать условия
На разбор причин «странной» сработки этого триггера мы потратили два дня — давайте посмотрим, почему.
Исходник:
IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
AND ( OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
OR OLD."Удален" <> NEW."Удален"
OR OLD."Дата" <> NEW."Дата"
OR OLD."Время" <> NEW."Время"
OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...
Проблема №1: неравенство не учитывает NULL
Представим, что все OLD
-поля имели значение NULL
. Что получится?
SELECT NULL <> 1 OR NULL <> 2;
-- NULL
А с точки зрения отработки условия NULL
эквивалентен FALSE
, как было упомянуто выше.
Решение: используйте оператор IS DISTINCT FROM
от ROW
-оператора, сравнивая сразу целые записи:
SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE
Проблема №2: разная реализация одинакового функционала
Сравним:
NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
Зачем тут лишние вложенные SELECT
? А функция to_regclass
? А по-разному-то почему?..
Исправим:
NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid
Проблема №3: приоритет bool-операций
Отформатируем исходник:
{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )
Упс… По факту, получилось, что в случае истинности любого из двух первых условий, все условие целиком обращается в TRUE
, без учета неравенств. А это совсем не то, чего мы хотели.
Исправим:
(
{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате}
) AND
( {... неравенства} )
Проблема №4 (маленькая): сложное OR-условие для одного поля
Собственно, проблемы в №3 у нас возникли ровно потому, что условий было три. Но вместо них можно обойтись одним, с помощью механизма coalesce ... IN
:
coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')
Так мы и NULL
«поймаем», и сложных OR
со скобками городить не придется.
Итого
Зафиксируем то, что у нас получилось:
IF (
coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
(
OLD."ДокументНашаОрганизация"
, OLD."Удален"
, OLD."Дата"
, OLD."Время"
, OLD."ЛицоСоздал"
) IS DISTINCT FROM (
NEW."ДокументНашаОрганизация"
, NEW."Удален"
, NEW."Дата"
, NEW."Время"
, NEW."ЛицоСоздал"
)
) THEN ...
А если учесть, что эта триггерная функция может применяться только в UPDATE
-триггере из-за наличия OLD/NEW
в условии верхнего уровня, то это условие можно вообще вынести в WHEN
-условие, как было показано в #1…
Автор: Боровиков Кирилл