PostgreSQL Antipatterns: beregning av betingelser i SQL

SQL er ikke C++ og ikke JavaScript. Derfor skjer beregningen av logiske uttrykk annerledes, og dette er absolutt ikke det samme:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

I prosessen med å optimalisere spørringsplanen for PostgreSQL kan den på vilkårlig måte "omorganisere" ekvivalente betingelser, ikke beregne noen av dem for enkelte poster, tilordne dem til betingelsen av den brukte indeksen... Kort sagt, det er enklest å anta at du på forhånd ikke kan styre den rekkefølgen de vil (og om de i det hele tatt vil) bli beregnet likeverdige betingelser.

Så hvis du allikevel ønsker å styre prioriteten, må du strukturere disse betingelsene som ulik ved hjelp av betingede uttrykk og operatører.

Data og arbeid med dem er grunnlaget for vårt SBiS-kompleks, så det er veldig viktig for oss at operasjoner med dem ikke bare utføres korrekt, men også effektivt. La oss se på konkrete eksempler hvor feil kan oppstå i beregningen av uttrykk, og hvor vi kan forbedre effektiviteten.

#0: RTFM

Start eksempel fra dokumentasjonen:

Når rekkefølgen for beregning er viktig, kan den låses med konstruksjonen CASE. For eksempel, en slik måte å unngå deling med null i setningen Den maksimale/minimale hurtig oppfriskningen etter sletting eller blandede DML har ikke samme oppførsel som innsetting-tilfelle. Den sletter og beregner på nytt maks/min verdiene for de berørte gruppene. Du må være oppmerksom på dens ytelseseffekt. er upålitelig:

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

En sikker variant:

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

Konstruksjonen brukt slik CASE beskytter uttrykket mot optimalisering, så det bør kun brukes ved behov.

#1: условие в триггере

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

Ser ut som alt ser bra ut, men… Ingen lover at den innvendige SELECT ikke vil bli utført når det første betingelsen er falsk. La oss rette det med innvendige IF:

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

Nå ser vi nøye — hele kroppen til triggerfunksjonen har blitt "pakket" i IF. Og det betyr at ingenting hindrer oss fra å ta dette betingelsen ut av prosedyren med hjelp av WHEN-betingelsen BEGIN IF EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; ... CREATE TRIGGER ... WHEN cond(NEW.fld);:

Denne tilnærmingen sikrer at serverressursene spares når betingelsen er falsk.

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

#2: OR/AND-цепочка

I en uheldig situasjon kan det skje at begge

vil være "sanne", men EXISTS begge vil også bli utført Men hvis vi vet at en av dem ofte er "sann" (eller "falsk" — for.

Men hvis vi vet sikkert at én av dem oftere er "ekte" (eller "falsk" - for OG-kjeder) — kan vi ikke på en eller annen måte «øke prioriteten», slik at den andre ikke kjøres unødvendig flere ganger?

Det viser seg at vi kan — den algoritmiske tilnærmingen er nært beslektet med temaet i artikkelen. PostgreSQL Antipatterns: sjeldne oppføringer vil nå midten av JOIN..

La oss rett og slett «putte begge disse betingelsene under CASE»:

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

I dette tilfellet har vi ikke definert ELSE-verdien, altså i tilfellet at begge betingelsene er falske. CASE vil returneres NULL, som tolkes som FALSE i Den maksimale/minimale hurtig oppfriskningen etter sletting eller blandede DML har ikke samme oppførsel som innsetting-tilfelle. Den sletter og beregner på nytt maks/min verdiene for de berørte gruppene. Du må være oppmerksom på dens ytelseseffekt.-betingelsen.

Dette eksemplet kan kombineres annerledes — etter smak og farge:

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

#3: как [не] надо писать условия

Vi brukte to dager på å analysere årsakene til at denne triggeren «fenomener» — la oss se på hvorfor.

Kildekoden:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplett"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentForLønn"')::oid)
     AND (   OLD."DokumentVårOrganisasjon"  NEW."DokumentVårOrganisasjon"
          OR OLD."Slettet"  NEW."Slettet"
          OR OLD."Dato"  NEW."Dato"
          OR OLD."Tid"  NEW."Tid"
          OR OLD."PersonOpprettet"  NEW."PersonOpprettet" ) ) THEN ...

Problemet nr. 1: ulikheten tar ikke hensyn til NULL

La oss anta at alle OLD-feltene hadde verdi NULL. Hva vil skje?

SELECT NULL  1 OR NULL  2;
-- NULL

Og med tanke på betingelsens behandling NULL er ekvivalent med FALSE, som nevnt tidligere.

Solution: bruk operatøren IS DISTINCT FROM fra ROW-operatøren, og sammenlign hele oppføringer på en gang:

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

Problemet nr. 2: forskjellig implementasjon av identisk funksjonalitet

La oss sammenligne:

NEW."Dokument_" = (select '"Komplett"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentForLønn"')::oid)

Hvorfor har vi unødvendige nestede SELECT? А функция to_regclass? А по-разному-то почему?..

La oss rette opp:

NEW."Dokument_" = '"Komplett"'::regclass::oid
NEW."Dokument_" = '"DokumentForLønn"'::regclass::oid

Problemet nr. 3: prioritet av boolske operasjoner

La oss formatere kildekoden:

{... IS NULL} OR
{... Komplett} OR
{... DokumentForLønn} AND
( {... ulikheter} )

Ups… Faktisk, har det vist seg at hvis noen av de to første betingelsene er sanne, blir hele betingelsen snudd til TRUE, uten hensyn til ulikhetene. Og dette er absolutt ikke det vi ønsket.

La oss rette opp:

(
  {... IS NULL} OR
  {... Komplett} OR
  {... DokumentForLønn}
) AND
( {... ulikheter} )

Problemet nr. 4 (lite): komplisert OR-betingelse for ett felt

Faktisk oppsto problemene i nr. 3 nettopp fordi vi hadde tre betingelser. Men i stedet kan vi klare oss med én, ved hjelp av mekanismen coalesce ... IN:

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

Så vi vil NULL «fange», og komplekse Eller med parenteser trenger vi ikke å lage.

Totalt

La oss fastslå hva vi har oppnådd:

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

Og hvis vi tar hensyn til at denne triggerfunksjonen kun kan anvendes i UPDATE-triggeren på grunn av tilstedeværelsen OLD/Nytt i betingelsen på toppnivå, kan denne betingelsen helt utarbeides i WHEN-betingelsen, som vist i #1 …

Kilde: habr.com