ProHoster > Blog > Administratioun > PostgreSQL Antipatterns: Conditioun Evaluatioun an SQL

PostgreSQL Antipatterns: Conditioun Evaluatioun an SQL

SQL ass net C ++, an och net JavaScript. Dofir ass d'Evaluatioun vu logesche Ausdréck anescht, an dëst ass guer net déiselwecht Saach:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Wärend den Ausféierungsplang vun enger PostgreSQL Ufro optiméiert kann déi gläichwäerteg Konditiounen arbiträr "rearrangéieren"., berechent keng vun hinnen fir eenzel Rekorder, kuckt op den Zoustand vum ugewandten Index ... Kuerz gesot, de einfachste Wee ass unzehuelen datt Dir kann net managen d'Uerdnung an där se wäerte sinn (an ob se iwwerhaapt berechent ginn) gläich Konditiounen.

Dofir, wann Dir nach Prioritéit verwalten wëllt, musst Dir strukturell maachen dës Konditiounen ongläich mat bedingt Ausdréck и Opérateuren.

PostgreSQL Antipatterns: Conditioun Evaluatioun an SQL
Daten a mat hinnen schaffen ass d'Basis vun eisem VLSI Komplex, also ass et ganz wichteg fir eis datt Operatiounen op hinnen net nëmme korrekt, awer och effizient gemaach ginn. Loosst eis op konkret Beispiller kucken, wou Feeler an der Ausdrockbewäertung kënne gemaach ginn, a wou et derwäert ass hir Effizienz ze verbesseren.

#0: RTFM

Ugefaangen Beispill aus Dokumentatioun:

Wann d'Uerdnung vun der Evaluatioun wichteg ass, kann et mam Konstrukt fixéiert ginn CASE. Zum Beispill, dës Manéier Divisioun vun Null an engem Saz ze vermeiden WHERE onzouverlässeg:

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

Sécher Optioun:

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

D'Konstruktioun benotzt CASE schützt den Ausdrock vun der Optimisatioun, sou datt et nëmmen benotzt gëtt wann néideg.

# 1: Ausléiser Konditioun

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

Alles schéngt gutt ze kucken, awer ... Keen versprécht, datt d'investéiert SELECT gëtt net ausgefouert wann déi éischt Konditioun falsch ass. Fixéiert et mat nestéiert IF:

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

Loosst eis elo suergfälteg kucken - de ganze Kierper vun der Ausléiserfunktioun huet sech als "gewéckelt" erausgestallt IF. An dëst bedeit datt näischt eis verhënnert datt dës Konditioun aus der Prozedur benotzt gëtt WHEN-Konditiounen:

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

Dës Approche erlaabt Iech Serverressourcen mat enger Garantie ze spueren wann d'Konditioun falsch ass.

# 2: ODER / AN Kette

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

Soss kann et kritt ginn, datt souwuel EXISTS wäert richteg sinn, mä béid wäerten ausgefouert ginn.

Awer wa mir sécher wëssen datt ee vun hinnen "richteg" ass vill méi dacks (oder "falsch" - fir AND-chains) - ass et méiglech iergendwéi seng Prioritéit ze erhéijen, sou datt déi zweet net nach eng Kéier ausgefouert gëtt?

Et stellt sech eraus datt et méiglech ass - d'algorithmesch Approche ass no beim Thema vum Artikel PostgreSQL Antipatterns: Selten Entrée erreecht d'Mëtt vun engem JOIN.

Loosst eis just "ënner CASE drécken" béid vun dëse Konditiounen:

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

An dësem Fall hu mir net definéiert ELSE-wäert, dat ass, wa béid Konditioune falsch sinn CASE wäert zréck NULL, wat interpretéiert gëtt als FALSE в WHERE- Konditiounen.

Dëst Beispill kann op eng aner Manéier kombinéiert ginn - fir ze schmaachen a Faarf:

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

#3: wéi [net] Konditiounen ze schreiwen

Mir hunn zwee Deeg verbruecht fir d'Grënn fir déi "komesch" Ausléisung vun dësem Ausléiser ze analyséieren - loosst eis kucken firwat.

Quell:

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: Ongläichheet zielt net NULL

Loosst eis unhuelen datt alles OLD-Felder wichteg NULL. Wat wäert geschéien?

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

An aus der Siicht vun der Ausaarbechtung vun de Konditiounen NULL gläichwäerteg FALSE, wéi uewen ernimmt.

Decisioun: benotzen Bedreiwer IS DISTINCT FROM от ROW-Operator, vergläicht ganz records op eemol:

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

Problem Nummer 2: verschidden Ëmsetzung vun der selwechter Funktionalitéit

Vergleichen:

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

Firwat ginn et extra Investitiounen SELECT? Eng Funktioun to_regclass? Firwat ass et anescht ...

Loosst eis fixen:

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

Problem # 3: bool Virrang

Loosst eis d'Quell formatéieren:

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

Oops ... Tatsächlech huet et sech erausgestallt datt am Fall vun der Wourecht vun enger vun den éischten zwee Konditiounen de ganzen Zoustand an TRUE, Ongläichheeten ignoréieren. An dat ass guer net wat mir wollten.

Loosst eis fixen:

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

Problem # 4 (kleng): komplex ODER Conditioun fir ee Feld

Eigentlech hate mir Problemer an der 3 genee well et dräi Konditioune waren. Mä amplaz vun hinnen, kënnt Dir mat engem kréien duerch de Mechanismus benotzt coalesce ... IN:

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

Mir och NULL "fangen", a komplex OR Dir musst Iech net mat Klammern zéien.

Total

Loosst eis fixéieren wat mir kruten:

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

A well dës Ausléiserfunktioun nëmme ka benotzt ginn UPDATEausléisen wéinst der Präsenz OLD/NEW am ieweschte Niveau Zoustand, da kann dës Conditioun allgemeng erausgeholl ginn WHEN-Conditioun wéi am # 1 gewisen ...

Source: will.com

Setzt e Commentaire