ProHoster > blogg > administration > PostgreSQL Antimönster: Tillståndsutvärdering i SQL

PostgreSQL Antimönster: Tillståndsutvärdering i SQL

SQL är inte C++, det är inte heller JavaScript. Därför är utvärderingen av logiska uttryck annorlunda, och det här är inte alls samma sak:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Medan du optimerar exekveringsplanen för en PostgreSQL-fråga kan godtyckligt "ordna om" motsvarande villkor, beräkna inte någon av dem för enskilda poster, se tillståndet för det tillämpade indexet ... Kort sagt, det enklaste sättet är att anta att du orkar inte i vilken ordning de kommer att vara (och om de kommer att beräknas överhuvudtaget) likvärdig betingelser.

Därför, om du fortfarande vill hantera prioritet, måste du strukturellt göra dessa villkor ojämlika med villkorlig uttryck и operatörer.

PostgreSQL Antimönster: Tillståndsutvärdering i SQL
Data och att arbeta med dem är grunden av vårt VLSI-komplex, så det är mycket viktigt för oss att operationer på dem utförs inte bara korrekt utan också effektivt. Låt oss titta på konkreta exempel där fel i uttrycksutvärdering kan göras och där det är värt att förbättra deras effektivitet.

#0: RTFM

Startande exempel från dokumentation:

När utvärderingsordningen är viktig kan den fixas med konstruktionen CASE. Till exempel på detta sätt för att undvika division med noll i en mening WHERE opålitliga:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Säkert alternativ:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Konstruktionen som används CASE skyddar uttrycket från optimering, så det bör endast användas när det är nödvändigt.

#1: triggervillkor

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Allt verkar se bra ut, men... Ingen lovar att de investerade SELECT kommer inte att köras om det första villkoret är falskt. Fixa det med kapslade IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Låt oss nu titta noga - hela kroppen av triggerfunktionen visade sig vara "inlindad". IF. Och detta betyder att ingenting hindrar oss från att ta bort detta tillstånd från proceduren med WHEN-betingelser:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Detta tillvägagångssätt låter dig spara serverresurser med en garanti om villkoret är falskt.

#2: ELLER/OCH-kedja

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Annars kan man få att båda EXISTS kommer att vara sant, men båda kommer att verkställas.

Men om vi vet säkert att en av dem är "sant" mycket oftare (eller "falsk" - för AND-kedjor) - är det möjligt att på något sätt "öka dess prioritet" så att den andra inte exekveras igen?

Det visar sig att det är möjligt - det algoritmiska tillvägagångssättet ligger nära ämnet för artikeln PostgreSQL-antimönster: Sällsynt post når mitten av en JOIN.

Låt oss bara "skjuta under CASE" båda dessa villkor:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

I det här fallet definierade vi inte ELSE-värde, det vill säga om båda villkoren är falska CASE kommer tillbaka NULL, vilket tolkas som FALSE в WHERE- betingelser.

Detta exempel kan kombineras på ett annat sätt - efter smak och färg:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: hur man [inte] skriver villkor

Vi ägnade två dagar åt att analysera orsakerna till den "konstiga" triggningen av denna trigger - låt oss se varför.

Källa:

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 ...

Problem #1: Ojämlikhet står inte för NULL

Låt oss anta att allt OLD-fälten spelade roll NULL. Vad kommer att hända?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

Och ur synvinkel att arbeta fram villkoren NULL likvärdig FALSE, som nämnts ovan.

beslutet: använd operator IS DISTINCT FROM från ROW-operatör, jämför hela poster på en gång:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Problem nummer 2: olika implementering av samma funktionalitet

Låt oss jämföra:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Varför finns det extra investeringar SELECT? En funktion to_regclass? Varför är det annorlunda...

Låt oss fixa:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Problem #3: bool prioritet

Låt oss formatera källan:

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

Oj ... Faktum är att det visade sig att i fallet med sanningen i något av de två första tillstånden, så förvandlas hela tillståndet till TRUE, utan hänsyn till ojämlikheter. Och det här är inte alls vad vi ville.

Låt oss fixa:

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

Problem #4 (litet): komplext ELLER-villkor för ett fält

Egentligen hade vi problem i nr 3 just för att det fanns tre förutsättningar. Men istället för dem kan du klara dig med en, med hjälp av mekanismen coalesce ... IN:

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

Så är vi NULL "fånga", och komplex OR Du behöver inte krångla med parenteser.

Totalt

Låt oss fixa det vi har:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Och med tanke på att denna triggerfunktion endast kan användas i UPDATEtrigger på grund av närvaron OLD/NEW i det övre tillståndet kan detta tillstånd i allmänhet tas ut i WHEN-skick som visas i #1...

Källa: will.com

Lägg en kommentar