PostgreSQL Antipatterns: вычисление условий в SQL

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…