Hur BigQuery från Google har demokratiserat datanalys. Del 1

SQL är varken C++ eller JavaScript. Därför beräknas logiska uttryck på ett annat sätt, och det här är långt ifrån detsamma:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Under optimeringen av exekveringsplanen för en fråga kan PostgreSQL på ett godtyckligt sätt "omorganisera" ekvivalenta villkor, inte beräkna vissa av dem för individuella poster, hänvisa till villkoret för den tillämpade indexen... Kort sagt, det enklaste sättet att tänka på det är att du på förhand inte kan styra i vilken ordning de kommer att (och om de alls kommer att) beräknas likvärdiga villkor.

Därför, om du ändå vill styra prioriteten, måste du strukturellt göra dessa villkor ojämlika med hjälp av villkors uttryck och för Kubernetes, som helt tar kontroll över distributionen, hanteringen och den automatiska återställningen av sådana datalagringslösningar som Ceph, EdgeFS, Minio, Cassandra, CockroachDB..

Data och arbete med dem är grunden för vårt Sbis-komplex, därför är det väldigt 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 kan uppstå i beräkning av uttryck, och där det är värt att förbättra deras effektivitet.

#0: RTFM

Start exempel från dokumentationen:

När beräkningsordningen är viktig kan den fixeras med hjälp av konstruktionen CASE. Till exempel, ett sätt att undvika division med noll i en sats DÄR är opålitligt:

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

Säker alternativ:

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

Konstruktionen som används på detta sätt CASE skyddar uttrycket mot optimering, så den bör endast användas vid behov.

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

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

Det ser ut som om allt ser bra ut, men... Ingen lovar att den inre VÄLJ inte kommer att exekveras om det första villkoret är falskt. Låt oss rätta till det med hjälp av inre IF:

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

Nu ska vi titta noggrant — hela koden för triggerfunktionen har blivit "inkapslad" i IF. Och det betyder att ingenting hindrar oss från att ta ut detta villkor ur proceduren med hjälp av WHEN-villkor:

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

Denna metod gör att vi garanterat kan spara serverresurser om villkoret är falskt.

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

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

I den ogynnsamma situationen kan vi få att båda EXISTS blir "sanna", men de kommer också att exekveras.

Men om vi vet att en av dem är "sann" mycket oftare (eller "falsk" — för OCH-kedjor) — går det inte att "höja dess prioritet" på något sätt för att den andra inte ska köras i onödan?

Det visar sig att det går — den algoritmiska metoden är nära relaterad till ämnet för artikeln PostgreSQL Antipatters: sällsynta poster når sällan mitten av JOIN.

Låt oss helt enkelt "stoppa in under CASE" båda dessa villkor:

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

I detta fall har vi inte definierat ELSE-värdet, det vill säga i fallet av falskhet för båda villkoren CASE kommer att returnera NULL, vilket tolkas som FALSK på DÄR-villkor.

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

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

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

Vi spenderade två dagar på att analysera orsakerna till denna "märkliga" trigger — låt oss titta på varför.

Källkod:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplex"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentFörLön"')::oid)
     AND (   OLD."DokumentVårOrganisation" <> NEW."DokumentVårOrganisation"
          OR OLD."Raderad" <> NEW."Raderad"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Tid" <> NEW."Tid"
          OR OLD."SkapadAv" <> NEW."SkapadAv" ) ) THEN ...

Problem nr 1: ojämlikhet beaktar inte NULL

Låt oss föreställa oss att alla OLD-fält hade värdet NULL. Vad blir resultatet?

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

Och från perspektivet av att utvärdera villkoret NULL är ekvivalent FALSK, som nämnts tidigare.

Lösning: använd operatorn IS DISTINCT FROM från ROW-operatören genom att jämföra hela poster samtidigt:

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

Problem nr 2: olika implementationer av samma funktionalitet

Låt oss jämföra:

NEW."Dokument_" = (select '"Komplex"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentFörLön"')::oid)

Varför finns det onödiga nästlade VÄLJ? А функция to_regclass? А по-разному-то почему?..

Låt oss rätta till:

NEW."Dokument_" = '"Komplex"'::regclass::oid
NEW."Dokument_" = '"DokumentFörLön"'::regclass::oid

Problem nr 3: prioritet för booleska operationer

Låt oss formatera koden:

{... IS NULL} OR
{... Komplex} OR
{... DokumentFörLön} AND
( {... ojämlikheter} )

Oops… Faktiskt, det blev så att om något av de två första villkoren är sanna, så blir hela villkoret SANN, utan att ta hänsyn till ojämlikheterna. Och det här är definitivt inte vad vi ville.

Låt oss rätta till:

(
  {... IS NULL} OR
  {... Komplex} OR
  {... DokumentFörLön}
) AND
( {... ojämlikheterna} )

Problem nr 4 (litet): komplicerat OR-villkor för ett enda fält

Egentligen uppstod problemen i nr 3 just för att det var tre villkor. Men istället kan vi klara oss med ett enda, med hjälp av mekanismen coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplex"', '"DokumentFörLön"')

Så vi kan både NULL fånga", och behöver inte krångla med ELLER komplexa med parenteser.

Sammanfattningsvis

Låt oss fastställa vad vi har kommit fram till:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Paket"', '"LönDokument"') AND
  (
    OLD."DokumentVårOrganisation"
  , OLD."Borttagen"
  , OLD."Datum"
  , OLD."Tid"
  , OLD."SkapadAv"
  ) IS DISTINCT FROM (
    NEW."DokumentVårOrganisation"
  , NEW."Borttagen"
  , NEW."Datum"
  , NEW."Tid"
  , NEW."SkapadAv"
  )
) THEN ...

Och om vi tar hänsyn till att denna triggerfunktion endast kan tillämpas i UPPDATERA-trigger på grund av förekomsten av OLD/NEW i det överordnade villkoret, så kan detta villkor faktiskt flyttas till WHEN-villkoret, som visats i #1…

Källa: habr.com