PostgreSQL Antipatterns: Оценка на състоянието в SQL

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

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

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

Следователно, ако все пак искате да управлявате приоритета, трябва да го направите структурно правят тези условия неравностойни с условно изрази и оператори.

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

#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: ИЛИ/И верига

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 (малка): комплексно ИЛИ условие за едно поле

Всъщност в номер 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...

Източник: www.habr.com