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…

Источник: habr.com