🥇PostgreSQL Антипатерни: изчисляване на условия в SQL

SQL не е C++ и не JavaScript. Следователно, изчислението на логически изрази се извършва по различен начин и това определено не е съвсем същото:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

В процеса на оптимизация на плана за изпълнение на заявката PostgreSQL може произволно да "пренарежда" еквивалентни условия, да не изчислява някои от тях за отделни записи, да ги отнася към условието на приложен индекс... Проще казано, най-добре е да сметнете, че предварително не можете да управлявате реда, в който ще бъдат (и ще бъдат ли изобщо) изчислени равноправни условия.

Затова, ако желаете да управлявате приоритета, трябва структурно да направите тези условия неравноправни чрез условни изрази. и за Kubernetes, които напълно поемат контрол над разгръщането, управлението и автоматичното възстановяване на решения за съхранение на данни, като Ceph, EdgeFS, Minio, Cassandra, CockroachDB..

Данните и работата с тях са основата на нашия комплекс СБИС, затова е много важно за нас, операциите с тях да се извършват не само коректно, но и ефективно. Нека да разгледаме конкретни примери, където могат да се допускат грешки в изчисленията на изрази, и къде си струва да се подобри тяхната ефективност.

#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 ще бъдат "истинни", но и двете ще се изпълнят..

Но ако знаем със сигурност, че едно от тях е "истинно" много по-често (или "лъжливо" — за И-веригите) — може ли somehow да се „повиши неговият приоритет“, така че вторият да не се изпълнява излишно?

Оказва се, че може — алгоритмичният подход е близък до темата на статията PostgreSQL Антипатерни: рядко записът достига до средата на 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: приоритет на логическите операции

Форматираме изходния код:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенствата} )

Упс… Всъщност, стана така, че в случай на истинност на някое от двете първи условия, цялото условие се обръща в TRUE, без да се отчитат неравенствата. А това изобщо не е това, което искахме.

Коригираме:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенствата} )

Проблем №4 (малък): сложно OR-условие за едно поле

Всъщност, проблемите в №3 се появиха именно заради това, че условията бяха три. Но вместо тях може да минем с едно, с помощта на механизма coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Така ние NULL «хващаме», и сложни , свързвайки се в сложни условия, води до това, че оптимизаторът, разполагащ с подходящи некластеризирани индекси по необходимите полета, в крайна сметка все пак започва да прави сканиране по кластерен индекс ( без да се налага да използваме скоби.

Общо

Записваме това, което получихме:

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