🥇PostgreSQL Antipatterns: calcolo delle condizioni in SQL

SQL non è C++ e nemmeno JavaScript. Pertanto, il calcolo delle espressioni logiche avviene in modo diverso, e questo non è affatto lo stesso:

WHERE fncondX() E fncondY()

= fncondX() && fncondY()

Durante l'ottimizzazione del piano di esecuzione della query PostgreSQL può "riordinare" le condizioni equivalenti in modi arbitrari, non calcolare alcune di esse per singoli record, attribuire alla condizione dell'indice utilizzato... Insomma, è più facile considerare che non puoi gestire in quale ordine verranno (e se saranno) calcolate le condizioni equivalenti. Pertanto, se desideri comunque gestire la priorità, è necessario strutturalmente

rendere queste condizioni diseguali utilizzando espressioni con condizione I dati e il lavoro con essi sono la base e operatori.

I dati e il loro utilizzo sono fondamentali del nostro complesso SBIS,quindi è molto importante per noi garantire che le operazioni su di essi vengano eseguite non solo correttamente, ma anche in modo efficiente. Diamo un'occhiata a esempi concreti in cui potrebbero verificarsi errori nel calcolo delle espressioni e dove è opportuno migliorare la loro efficienza.

#0: RTFM

Inizio esempio dalla documentazione:

Quando l'ordine di calcolo è importante, può essere fissato utilizzando la costruzione CASE. Ad esempio, questo è un modo per evitare la divisione per zero in una clausola DOVE 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;
Questa costruzione CASE protege l'espressione dall'ottimizzazione, quindi va utilizzata solo se necessario.

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

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

Sembra che vada tutto bene, ma… Nessuno garantisce che l'annidato SELECT non venga eseguito se la prima condizione è falsa. Correggiamo con l'uso di annidati IF:

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

Ora guardiamo attentamente — tutto il corpo della funzione trigger è stato "avvolto" in IF. Questo significa che nulla ci impedisce di spostare questa condizione fuori dalla procedura usando WHEN-condizione:

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

Questo approccio consente di risparmiare risorse del server in modo garantito se la condizione è falsa.

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

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

In un caso sfortunato, potremmo trovarci con entrambi EXISTS che saranno "veri", ma entrambi verranno eseguiti..

Ma se sappiamo con certezza che uno di essi è "vero" molto più spesso (o "falso" — per la AND-catena) — non possiamo alzare la sua priorità in modo che il secondo non venga eseguito inutilmente?

A quanto pare, è possibile: un approccio algoritmico è vicino all'argomento dell'articolo PostgreSQL Antipatterns: una registrazione rara raggiunge il centro del JOIN.

Mettiamo semplicemente 'sotto CASE' entrambe queste condizioni:

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

In questo caso non abbiamo definito ELSE-valore, ovvero nel caso in cui entrambe le condizioni siano false CASE restituirà NULL, che viene interpretato come FALSE in DOVE-condizione.

Questo esempio può essere combinato in un altro modo — a gusto e colore:

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

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

Abbiamo impiegato due giorni per analizzare le cause del comportamento 'strano' 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: l'ineguaglianza non considera NULL

Immaginiamo che tutti OLD-i campi avessero valore NULL. Cosa succede?

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

E dal punto di vista dell'elaborazione della condizione NULL è equivalente FALSE, come accennato sopra.

Soluzione: utilizza l'operatore IS DISTINCT FROM da ROW-operatore, confrontando interi record contemporaneamente:

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

Problema n. 2: implementazione diversa della stessa funzionalità

Confrontiamo:

NEW."Documento_" = (select '"Insieme"'::regclass::oid)
NEW."Documento_" = (select to_regclass('"DocumentoPerStipendio"')::oid)

Perché qui ci sono annidamenti superflui SELECT? А функция to_regclass? А по-разному-то почему?..

Correggiamo:

NEW."Documento_" = '"Insieme"'::regclass::oid
NEW."Documento_" = '"DocumentoPerStipendio"'::regclass::oid

Problema n. 3: priorità delle operazioni boolean

Formattiamo l'originale:

{... IS NULL} OR
{... Insieme} OR
{... DocumentoPerStipendio} AND
( {... disuguaglianze} )

Ops... Infatti, si è scoperto che nel caso di verità di una delle due prime condizioni, l'intera condizione diventa TRUE, senza considerare le disuguaglianze. E questo non è affatto ciò che volevamo.

Correggiamo:

(
  {... IS NULL} OR
  {... Insieme} OR
  {... DocumentoPerStipendio}
) AND
( {... disuguaglianze} )

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

In effetti, i problemi n. 3 sono sorti proprio perché c'erano tre condizioni. Ma invece di esse, è possibile evitarle usando il meccanismo coalesce ... IN:

coalesce(NEW."Documento_"::text, '') IN ('', '"Insieme"', '"DocumentoPerStipendio"')

In questo modo NULL «cattureremo» e non sarà necessario costruire condizioni OR complicate con le parentesi.

Totale

Fissiamo ciò che abbiamo ottenuto:

IF (
  coalesce(NEW."Documento_"::text, '') IN ('', '"Compilazione"', '"DocumentoPerStipendi"') AND
  (
    OLD."DocumentoNostraOrganizzazione"
  , OLD."Cancellato"
  , OLD."Data"
  , OLD."Ora"
  , OLD."PersonaCreatore"
  ) IS DISTINCT FROM (
    NEW."DocumentoNostraOrganizzazione"
  , NEW."Cancellato"
  , NEW."Data"
  , NEW."Ora"
  , NEW."PersonaCreatore"
  )
) THEN ...

E considerando che questa funzione trigger può essere applicata solo in UPDATE-trigger a causa della presenza di OLD/NEW nella condizione di alto livello, è possibile estrarre questa condizione in WHEN-una condizione, come mostrato in #1…

Fonte: habr.com