ProHoster > Blog > administration > PostgreSQL Antipatterns: Tilstandsevaluering i SQL

PostgreSQL Antipatterns: Tilstandsevaluering i SQL

SQL er ikke C++, det er heller ikke JavaScript. Derfor er vurderingen af ​​logiske udtryk anderledes, og det er slet ikke det samme:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Mens du optimerer eksekveringsplanen for en PostgreSQL-forespørgsel kan vilkårligt "omarrangere" de tilsvarende betingelser, ikke beregne nogen af ​​dem for individuelle poster, se tilstanden for det anvendte indeks ... Kort sagt er den nemmeste måde at antage, at du ikke kan klare den rækkefølge, de vil være (og om de overhovedet vil blive beregnet) lige betingelser.

Derfor, hvis du stadig ønsker at styre prioritet, skal du strukturelt gøre disse forhold ulige med betinget udtryk и operatører.

PostgreSQL Antipatterns: Tilstandsevaluering i SQL
Data og arbejdet med dem er grundlaget af vores VLSI-kompleks, så det er meget vigtigt for os, at operationer på dem udføres ikke kun korrekt, men også effektivt. Lad os se på konkrete eksempler, hvor fejl i udtryksevaluering kan begås, og hvor det er værd at forbedre deres effektivitet.

#0: RTFM

Starter eksempel fra dokumentation:

Når rækkefølgen af ​​evalueringen er vigtig, kan den rettes med konstruktionen CASE. For eksempel på denne måde at undgå division med nul i en sætning WHERE upålidelig:

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

Sikker mulighed:

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

Den anvendte konstruktion CASE beskytter udtrykket mod optimering, så det bør kun bruges, når det er nødvendigt.

#1: triggertilstand

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

Alt ser ud til at se godt ud, men... Ingen lover, at de investerede SELECT vil ikke blive udført, hvis den første betingelse er falsk. Fix det med indlejret IF:

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

Lad os nu se omhyggeligt - hele kroppen af ​​triggerfunktionen viste sig at være "viklet" ind IF. Og det betyder, at intet forhindrer os i at fjerne denne betingelse fra proceduren ved hjælp af WHEN-betingelser:

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

Denne tilgang giver dig mulighed for at spare serverressourcer med garanti, hvis betingelsen er falsk.

#2: ELLER/OG kæde

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

Ellers kan det opnås, at begge dele EXISTS vil være sandt, men begge vil blive udført.

Men hvis vi ved med sikkerhed, at en af ​​dem er "sand" meget oftere (eller "falsk" - for AND-kæder) - er det muligt på en eller anden måde at "øge dens prioritet", så den anden ikke udføres igen?

Det viser sig, at det er muligt - den algoritmiske tilgang er tæt på artiklens emne PostgreSQL-antimønstre: Sjælden indtastning når midten af ​​en JOIN.

Lad os bare "skubbe under CASE" begge disse betingelser:

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

I dette tilfælde definerede vi ikke ELSE-værdi, det vil sige, hvis begge betingelser er falske CASE kommer tilbage NULL, hvilket tolkes som FALSE в WHERE- betingelser.

Dette eksempel kan kombineres på en anden måde - efter smag og farve:

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

#3: hvordan man [ikke] skriver betingelser

Vi brugte to dage på at analysere årsagerne til den "mærkelige" udløsning af denne trigger - lad os 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: Ulighed tager ikke højde for NULL

Lad os antage, at alt OLD-felter betød noget NULL. Hvad vil der ske?

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

Og ud fra et synspunkt om at udarbejde betingelserne NULL tilsvarende FALSE, som nævnt ovenfor.

beslutning: brug operatør IS DISTINCT FROM fra ROW-operatør, der sammenligner hele poster på én gang:

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

Problem nummer 2: forskellig implementering af samme funktionalitet

Lad os sammenligne:

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

Hvorfor er der ekstra investeringer SELECT? En funktion to_regclass? Hvorfor er det anderledes...

Lad os rette:

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

Problem #3: bool forrang

Lad os formatere kilden:

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

Ups ... Faktisk viste det sig, at i tilfælde af sandheden af ​​en af ​​de første to tilstande, bliver hele tilstanden til TRUE, uden hensyntagen til uligheder. Og det er slet ikke det, vi ønskede.

Lad os rette:

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

Opgave #4 (lille): kompleks ELLER betingelse for ét felt

Egentlig havde vi problemer i nr. 3, netop fordi der var tre forhold. Men i stedet for dem kan du klare dig med en ved hjælp af mekanismen coalesce ... IN:

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

Det er vi også NULL "fangst", og kompleks OR Du skal ikke bøvle med parenteser.

I alt

Lad os rette det, vi har:

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

Og givet at denne triggerfunktion kun kan bruges i UPDATEtrigger på grund af tilstedeværelsen OLD/NEW i det øverste niveau, så kan denne tilstand generelt tages ud i WHEN-tilstand som vist i #1...

Kilde: www.habr.com

Tilføj en kommentar