ProHoster > блог > Администрација > PostgreSQL антишеми: оценување на условите во SQL

PostgreSQL антишеми: оценување на условите во SQL

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

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Во процесот на оптимизирање на планот за извршување на барањето PostgreSQL може произволно да ги „преуреди“ еквивалентните услови, не пресметувајте некои од нив за поединечни записи, поврзете ги со условите на применетиот индекс... Накратко, најлесно е да се претпостави дека вие не може да контролира по кој редослед ќе (и дали воопшто ќе се пресметуваат) еднакви Услови.

Затоа, ако сè уште сакате да управувате со приоритетот, треба да го структурирате направи овие услови нееднакви користејќи условни изрази и оператори.

PostgreSQL антишеми: оценување на условите во SQL
Податоците и работата со нив се основата нашиот комплекс 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.

Ајде само да ги „буткаме“ двата од овие услови под СЛУЧАЈ:

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

Додадете коментар