ProHoster > Bloc > Administració > Antipatterns de PostgreSQL: avaluació de condicions en SQL

Antipatterns de PostgreSQL: avaluació de condicions en SQL

SQL no és C++, ni JavaScript. Per tant, el càlcul d'expressions lògiques es produeix de manera diferent, i això no és gens el mateix:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

En procés d'optimització del pla d'execució de consultes PostgreSQL pot "reordenar" arbitràriament condicions equivalents, no calculeu alguns d'ells per a registres individuals, relacioneu-los amb les condicions de l'índex aplicat... En definitiva, la manera més senzilla és suposar que no pot controlar en quin ordre es faran (i si es calcularan) igual condicions.

Per tant, si encara voleu gestionar la prioritat, cal estructurar-la fer que aquestes condicions siguin desiguals utilitzant condicionals expressions и operadors.

Antipatterns de PostgreSQL: avaluació de condicions en SQL
Les dades i el treball amb ells són la base el nostre complex VLSI, per tant, és molt important per a nosaltres que les operacions sobre ells es facin no només correctament, sinó també eficientment. Vegem exemples concrets on es poden cometre errors en el càlcul d'expressions i on val la pena millorar-ne l'eficiència.

#0: RTFM

Començant exemple de la documentació:

Quan l'ordre d'avaluació és important, es pot capturar mitjançant el constructe CASE. Per exemple, aquesta és una manera d'evitar la divisió per zero en una frase WHERE poc fiable:

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

Opció segura:

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

El disseny utilitzat d'aquesta manera CASE protegeix l'expressió de l'optimització, de manera que només s'ha d'utilitzar quan sigui necessari.

#1: condició d'activació

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

Sembla que tot sembla bé, però... Ningú promet que la inversió SELECT no s'executarà si la primera condició és falsa. Arreglem-ho amb niat IF:

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

Ara mirem amb atenció: tot el cos de la funció de disparador està "embolicat". IF. Això vol dir que res ens impedeix eliminar aquesta condició del procediment d'ús WHEN- condicions:

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

Aquest enfocament està garantit per estalviar recursos del servidor quan la condició és falsa.

#2: cadena O/I

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

En cas contrari, podeu acabar amb tots dos EXISTS serà "veritat", però tots dos es compliran.

Però si sabem del cert que un d'ells és "vertader" molt més sovint (o "fals" - per AND-cadenes) - és possible d'alguna manera "augmentar la seva prioritat" perquè la segona no es torni a executar?

Resulta que és possible: l'enfocament algorítmic és proper al tema de l'article Antipatterns de PostgreSQL: un registre rar arribarà a la meitat d'un JOIN.

Anem a "empènyer" aquestes dues condicions a CASE:

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

En aquest cas no hem definit ELSE-valor, és a dir, si ambdues condicions són falses CASE tornarà NULL, que s'interpreta com FALSE в WHERE-condicions.

Aquest exemple es pot combinar d'altres maneres, segons el gust i el color:

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

#3: com [no] escriure condicions

Vam passar dos dies analitzant els motius de l'operació "estranya" d'aquest disparador; vegem per què.

Font:

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

Problema #1: la desigualtat no respecta NULL

Imaginem que tot OLD-Els camps tenien significat NULL. Què passarà?

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

I des del punt de vista de treballar les condicions NULL equivalent FALSE, com s'ha esmentat anteriorment.

decisió: utilitzar l'operador IS DISTINCT FROM d' ROW-operador, comparant registres sencers alhora:

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

Problema #2: diferents implementacions de la mateixa funcionalitat

Compareu:

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

Per què hi ha una inversió addicional aquí? SELECT? Una funció to_regclass? Per què és diferent?...

Arreglem:

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

Problema #3: prioritat de les operacions bool

Formatem la font:

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

Vaja... De fet, va resultar que si alguna de les dues primeres condicions és certa, tota la condició es converteix en TRUE, sense tenir en compte les desigualtats. I això no és gens el que volíem.

Arreglem:

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

Problema #4 (petit): condició OR complexa per a un camp

De fet, vam tenir problemes al número 3 precisament perquè hi havia tres condicions. Però en comptes d'ells, podeu fer-ho amb un, utilitzant el mecanisme coalesce ... IN:

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

Així que nosaltres NULL “Agafarem”, i difícil OR No cal tancar amb suports.

En total

Enregistrem el que hem aconseguit:

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

I si teniu en compte que aquesta funció d'activació només es pot utilitzar en UPDATE-disparador per disponibilitat OLD/NEW a la condició de nivell superior, generalment es pot col·locar aquesta condició WHEN-condició, tal com es mostra al número 1...

Font: www.habr.com

Afegeix comentari