ProHoster > Blog > Bestjoer > PostgreSQL Antipatterns: Betingst evaluaasje yn SQL

PostgreSQL Antipatterns: Betingst evaluaasje yn SQL

SQL is gjin C ++, en net JavaScript. Dêrom komt de berekkening fan logyske útdrukkingen oars foar, en dit is hielendal net itselde:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Yn it proses fan it optimalisearjen fan it PostgreSQL-query-útfierplan kin willekeurich "rearrange" lykweardige betingsten, berekkenje guon fan har net foar yndividuele records, relatearje se oan de betingsten fan 'e tapaste yndeks ... Koartsein, de maklikste manier is om oan te nimmen dat jo kin net kontrolearje yn hokker folchoarder se sille (en oft se überhaupt berekkene wurde) lyk betingsten.

Dêrom, as jo noch prioriteit wolle beheare, moatte jo it strukturearje meitsje dizze betingsten ûngelikense mei help fan betingsten útdrukkingen и operators.

PostgreSQL Antipatterns: Betingst evaluaasje yn SQL
Gegevens en it wurkjen mei harren binne de basis ús VLSI kompleks, dêrom is it heul wichtich foar ús dat operaasjes op har net allinich korrekt, mar ek effisjint wurde útfierd. Litte wy nei spesifike foarbylden sjen wêr't flaters kinne wurde makke by it berekkenjen fan útdrukkingen, en wêr't it wurdich is om har effisjinsje te ferbetterjen.

#0: RTFM

Starting foarbyld út dokumintaasje:

As de folchoarder fan evaluaasje wichtich is, kin it fêstlein wurde mei it konstruksje CASE. Dit is bygelyks in manier om divyzje troch nul yn in sin te foarkommen WHERE ûnbetrouber:

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

Feilige opsje:

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

It ûntwerp brûkt op dizze manier CASE beskermet de útdrukking tsjin optimalisaasje, dus it moat allinich brûkt wurde as it nedich is.

#1: trigger betingst

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

Alles liket goed te sjen, mar ... Gjinien belooft dat de ynvestearring SELECT sil net útfierd wurde as de earste betingst falsk is. Litte wy it reparearje mei nested IF:

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

Litte wy no foarsichtich sjen - it heule lichem fan 'e triggerfunksje is "ynpakt". IF. Dit betsjut dat neat ús foarkomt om dizze betingst te ferwiderjen fan 'e proseduere dy't brûkt wurdt WHEN-betingsten:

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

Dizze oanpak is garandearre om serverboarnen te bewarjen as de betingst falsk is.

# 2: OR / EN ketting

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

Oars kinne jo einigje mei beide EXISTS sil wêze "wier", mar beide sille folbrocht wurde.

Mar as wy witte wis dat ien fan harren is "wier" folle faker (of "false" - foar AND-chains) - is it mooglik om op ien of oare manier "syn prioriteit te fergrutsjen", sadat de twadde net ien kear wer útfierd wurdt?

It docht bliken dat it mooglik is - de algoritmyske oanpak is ticht by it ûnderwerp fan it artikel PostgreSQL Antipatterns: in seldsum rekord sil it midden fan in JOIN berikke.

Litte wy dizze beide betingsten gewoan "skowe" ûnder CASE:

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

Yn dit gefal hawwe wy net definieare ELSE-wearde, dat is, as beide betingsten falsk binne CASE sil weromkomme NULL, dat wurdt ynterpretearre as FALSE в WHERE-betingsten.

Dit foarbyld kin op oare manieren kombineare wurde - ôfhinklik fan smaak en kleur:

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

#3: hoe [net] betingsten te skriuwen

Wy hawwe twa dagen bestege oan it analysearjen fan de redenen foar de "frjemde" operaasje fan dizze trigger - lit ús sjen wêrom.

Boarne:

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

Probleem #1: ûngelikens respektearret NULL net

Lit ús yntinke dat alles OLD-fjilden hiene betsjutting NULL. Wat barre sil?

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

En út it eachpunt fan it útwurkjen fan de betingsten NULL lykweardich FALSE, lykas hjirboppe neamd.

beslút: brûke operator IS DISTINCT FROM от ROW-operator, it fergelykjen fan folsleine records tagelyk:

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

Probleem #2: ferskate ymplemintaasjes fan deselde funksjonaliteit

Litte wy fergelykje:

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

Wêrom is hjir ekstra ynvestearrings? SELECT? In funksje to_regclass? Wêrom is it oars?..

Litte wy it reparearje:

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

Probleem # 3: prioriteit fan bool operaasjes

Litte wy de boarne formatearje:

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

Oeps ... Yn feite, it die bliken dat as ien fan de earste twa betingsten wier is, de hiele betingst feroaret yn TRUE, sûnder rekken te hâlden mei ûngelikens. En dit is hielendal net wat wy woenen.

Litte wy it reparearje:

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

Probleem # 4 (lyts): kompleks OR betingst foar ien fjild

Eins hiene wy ​​problemen yn nûmer 3 krekt om't der trije betingsten wiene. Mar ynstee fan harren kinne jo krije troch mei ien, mei help fan it meganisme coalesce ... IN:

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

Wy dus NULL "wy sille fange", en dreech OR D'r is gjin need om te fence mei heakjes.

Totaal

Litte wy opnimme wat wy krigen:

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

En as jo beskôgje dat dizze triggerfunksje allinich kin wurde brûkt yn UPDATE-trigger fanwege beskikberens OLD/NEW yn 'e tastân op it boppeste nivo, dan kin dizze tastân oer it generaal yn pleatst wurde WHEN-kondysje, lykas werjûn yn #1 ...

Boarne: www.habr.com

Add a comment