ProHoster > blog > amministrazione > Antipattern PostgreSQL: valutazione delle condizioni in SQL

Antipattern PostgreSQL: valutazione delle condizioni in SQL

SQL non è C++ e non JavaScript. Pertanto, il calcolo delle espressioni logiche avviene in modo diverso e non è affatto la stessa cosa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Nel processo di ottimizzazione del piano di esecuzione delle query PostgreSQL possono “riorganizzare” arbitrariamente condizioni equivalenti, non calcolarne alcuni per singoli record, rapportarli alle condizioni dell'indice applicato... In breve, il modo più semplice è supporre che tu non posso controllare in quale ordine verranno calcolati (e se verranno calcolati) equo condizioni.

Pertanto, se vuoi comunque gestire la priorità, devi strutturarla rendere queste condizioni ineguali utilizzando i condizionali espressioni и operatori.

Antipattern PostgreSQL: valutazione delle condizioni in SQL
I dati e lavorare con essi sono la base il nostro complesso VLSI, quindi per noi è molto importante che le operazioni su di essi vengano eseguite non solo correttamente, ma anche in modo efficiente. Diamo un'occhiata ad esempi specifici in cui si possono commettere errori nel calcolo delle espressioni e in cui vale la pena migliorarne l'efficienza.

N. 0: RTFM

Di partenza esempio dalla documentazione:

Quando l'ordine di valutazione è importante, può essere catturato utilizzando il costrutto CASE. Ad esempio, questo è un modo per evitare la divisione per zero in una frase WHERE inaffidabile:

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

Opzione sicura:

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

Il design utilizzato in questo modo CASE protegge l'espressione dall'ottimizzazione, quindi dovrebbe essere utilizzata solo quando necessario.

N. 1: condizione di attivazione

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

Tutto sembra andare bene, ma... Nessuno promette l'investimento SELECT non verrà eseguito se la prima condizione è falsa. Risolviamolo con nidificato IF:

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

Ora guardiamo attentamente: l'intero corpo della funzione di trigger è "avvolto". IF. Ciò significa che nulla ci impedisce di rimuovere questa condizione dalla procedura utilizzando WHEN-condizioni:

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

Questo approccio garantisce il risparmio delle risorse del server quando la condizione è falsa.

N. 2: catena OR/AND

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

Altrimenti, puoi finire con entrambi EXISTS sarà “vero”, ma entrambi saranno soddisfatti.

Ma se sappiamo con certezza che uno di essi è “vero” molto più spesso (o “falso” - per AND-chains) - è possibile in qualche modo "aumentare la sua priorità" in modo che la seconda non venga eseguita ancora una volta?

Si scopre che è possibile: l'approccio algoritmico è vicino all'argomento dell'articolo Antipattern PostgreSQL: un record raro raggiungerà la metà di un JOIN.

Inseriamo semplicemente entrambe queste condizioni in CASE:

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

In questo caso non abbiamo definito ELSE-value, cioè se entrambe le condizioni sono false CASE ritornerà NULL, che viene interpretato come FALSE в WHERE-condizioni.

Questo esempio può essere combinato in altri modi, a seconda del gusto e del colore:

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

#3: come [non] scrivere le condizioni

Abbiamo passato due giorni ad analizzare le ragioni dello “strano” funzionamento di questo trigger – vediamo perché.

fonte:

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 n. 1: la disuguaglianza non rispetta NULL

Immaginiamo che tutto OLD-i campi avevano un significato NULL. Cosa accadrà?

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

E dal punto di vista dell'elaborazione delle condizioni NULL equivalente FALSE, come menzionato sopra.

Soluzione: usa l'operatore IS DISTINCT FROM от ROW-operatore, confrontando interi record contemporaneamente:

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

Problema n. 2: diverse implementazioni della stessa funzionalità

Mettiamo a confronto:

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

Perché ci sono investimenti extra qui? SELECT? Una funzione to_regclass? Perché è diverso?..

Correggiamo:

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

Problema n. 3: priorità delle operazioni bool

Formattiamo il sorgente:

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

Ops... In effetti, si è scoperto che se una qualsiasi delle prime due condizioni è vera, l'intera condizione diventa TRUE, senza tener conto delle disuguaglianze. E questo non è affatto quello che volevamo.

Correggiamo:

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

Problema n. 4 (piccolo): condizione OR complessa per un campo

In realtà abbiamo avuto problemi al n. 3 proprio perché c'erano tre condizioni. Ma invece di loro puoi farcela con uno, usando il meccanismo coalesce ... IN:

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

Quindi noi NULL "prenderemo", e difficile OR Non è necessario recintare con staffe.

In totale

Registriamo ciò che abbiamo ottenuto:

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

E se si considera che questa funzione di trigger può essere utilizzata solo in UPDATE-trigger a causa della disponibilità OLD/NEW nella condizione di livello superiore, in genere è possibile collocarla in questa condizione WHEN-condizione, come mostrato al punto 1...

Fonte: habr.com

Aggiungi un commento