🥇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

PostgreSQL Antipatterns: обчислення умов у SQL