🥇PostgreSQL Antipatterns: Tilstandsevaluering i SQL

SQL er ikke C++, det er heller ikke JavaScript. Derfor er evalueringen av logiske uttrykk annerledes, og dette er ikke det samme i det hele tatt:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Mens du optimaliserer utførelsesplanen for en PostgreSQL-spørring kan vilkårlig "omorganisere" de tilsvarende forholdene, ikke beregne noen av dem for individuelle poster, se tilstanden til den anvendte indeksen ... Kort sagt, den enkleste måten er å anta at du klarer ikke rekkefølgen de vil være (og om de vil bli beregnet i det hele tatt) lik forhold.

Derfor, hvis du fortsatt ønsker å administrere prioritet, må du strukturelt gjøre disse forholdene ulikt med betinget uttrykkene и operatører.

Data og arbeid med dem er grunnlaget av vårt VLSI-kompleks, så det er veldig viktig for oss at operasjoner på dem utføres ikke bare riktig, men også effektivt. La oss se på konkrete eksempler hvor feil i uttrykksevaluering kan gjøres, og hvor det er verdt å forbedre deres effektivitet.

#0: RTFM

Starter eksempel fra dokumentasjon:

Når rekkefølgen på evalueringen er viktig, kan den fikses med konstruksjonen CASE. For eksempel på denne måten å unngå divisjon med null i en setning WHERE upålitelig:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Sikkert alternativ:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Konstruksjonen som brukes CASE beskytter uttrykket mot optimalisering, så det bør kun brukes når det er nødvendig.

#1: triggertilstand

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

Alt ser bra ut, men... Ingen lover at de investerte SELECT vil ikke bli utført hvis den første betingelsen er falsk. Fiks det med nestet IF:

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

La oss nå se nøye - hele kroppen til utløserfunksjonen viste seg å være "pakket inn". IF. Og dette betyr at ingenting hindrer oss i å fjerne denne tilstanden fra prosedyren ved hjelp av WHEN-forhold:

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

Denne tilnærmingen lar deg spare serverressurser med en garanti hvis tilstanden er falsk.

#2: ELLER/OG-kjede

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

Ellers kan det oppnås at begge deler EXISTS vil være sant, men begge vil bli utført.

Men hvis vi vet sikkert at en av dem er "sann" mye oftere (eller "falsk" - for AND-kjeder) - er det mulig å "øke prioritet" på en eller annen måte slik at den andre ikke blir utført igjen?

Det viser seg at det er mulig - den algoritmiske tilnærmingen er nær emnet for artikkelen PostgreSQL-antimønstre: Sjelden oppføring når midten av en JOIN.

La oss bare "skubbe under CASE" begge disse forholdene:

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

I dette tilfellet definerte vi ikke ELSE-verdi, det vil si hvis begge forholdene er falske CASE vil returnere NULL, som tolkes som FALSE в WHERE- forhold.

Dette eksemplet kan kombineres på en annen måte - etter smak og farge:

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

#3: hvordan [ikke] skrive betingelser

Vi brukte to dager på å analysere årsakene til den "merkelige" utløsningen av denne triggeren - la oss se hvorfor.

Kilde:

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: Ulikhet står ikke for NULL

La oss anta at alt OLD-felt betydde noe NULL. Hva vil skje?

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

Og med tanke på å utarbeide forholdene NULL tilsvarende FALSE, som nevnt ovenfor.

beslutning: bruk operatør IS DISTINCT FROM fra ROW-operatør, sammenligner hele poster samtidig:

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

Problem nummer 2: forskjellig implementering av samme funksjonalitet

La oss sammenligne:

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

Hvorfor er det ekstra investeringer SELECT? En funksjon to_regclass? Hvorfor er det annerledes...

La oss fikse:

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

Problem #3: bool forrang

La oss formatere kilden:

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

Oops ... Faktisk viste det seg at når det gjelder sannheten til noen av de to første forholdene, blir hele tilstanden til TRUE, se bort fra ulikheter. Og dette er slett ikke det vi ønsket.

La oss fikse:

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

Oppgave #4 (liten): kompleks ELLER-betingelse for ett felt

Egentlig hadde vi problemer i nr. 3 nettopp fordi det var tre forhold. Men i stedet for dem kan du klare deg med en, ved å bruke mekanismen coalesce ... IN:

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

Det er vi også NULL "fangst", og kompleks OR Du trenger ikke mase med parenteser.

Totalt

La oss fikse det vi har:

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

Og gitt at denne triggerfunksjonen bare kan brukes i UPDATEtrigger på grunn av tilstedeværelsen OLD/NEW i tilstanden på øverste nivå, kan denne tilstanden generelt tas ut WHEN-tilstand som vist i #1...

Kilde: www.habr.com

PostgreSQL Antipatterns: Tilstandsevaluering i SQL