PostgreSQL Antipatterns: JOIN e OR dannosi

Temete le operazioni, buffers che portano…
Vediamo alcuni approcci universali all'ottimizzazione delle query in PostgreSQL basandoci su un piccolo esempio di query. Utilizzarli o meno è una tua scelta, ma è utile conoscerli.

In alcune versioni successive di PG, la situazione potrebbe cambiare con l'«intelligenza» del pianificatore, ma per 9.4/9.6 appare sostanzialmente invariata, come mostrano gli esempi qui.

Prendiamo una query del tutto reale:

SELECT
  TRUE
FROM
  "Documento" d
INNER JOIN
  "DocumentoEstensione" doc_ex
    USING("@Documento")
INNER JOIN
  "TipoDocumento" t_doc ON
    t_doc."@TipoDocumento" = d."TipoDocumento"
WHERE
  (d."Persona3" = 19091 or d."Dipendente" = 19091) AND
  d."$Bozza" IS NULL AND
  d."Eliminato" IS NOT TRUE AND
  doc_ex."Stato"[1] IS TRUE AND
  t_doc."TipoDocumento" = 'PianoLavori'
LIMIT 1;

sui nomi delle tabelle e dei campiI nomi "russi" dei campi e delle tabelle possono essere considerati in vari modi, ma è una questione di gusto. Dato che non abbiamo sviluppatori stranieri in "Tensor" e PostgreSQL ci consente di dare nomi anche con i geroglifici, se sono racchiusi tra virgolette, preferiamo nominare gli oggetti in modo chiaro e ovvio, per evitare ambiguità.
Esaminiamo il piano risultante:

[guarda su explain.tensor.ru]

144ms e quasi 53K buffers — cioè più di 400MB di dati! E saremo fortunati se tutti si trovano nella cache al momento della nostra query, altrimenti diventerà significativamente più lunga durante la lettura dal disco.

L'algoritmo è la cosa più importante!

Per ottimizzare in qualche modo una query, bisogna prima capire cosa deve effettivamente fare.
Per ora escludiamo dalla discussione la progettazione della struttura del DB e conveniamo che possiamo relativamente 'economicamente' riscrivere la query e/o applicare sul database alcuni indici necessari Quindi, la query:.

— verifica l'esistenza di almeno un documento
— nel nostro stato desiderato e di un tipo specifico
— dove l'autore o l'esecutore è il dipendente di cui abbiamo bisogno
JOIN + LIMIT 1

È abbastanza comune che allo sviluppatore sembri più semplice scrivere una query in cui prima viene eseguita un'unione di un gran numero di tabelle, per poi rimanere solo con un record. Ma più semplice per lo sviluppatore non significa più efficiente per il DB.

Nel nostro caso, c'erano solo 3 tabelle — e che effetto...
Iniziamo eliminando l'unione con la tabella "TipoDocumento", e nel frattempo indichiamo al database che la

registrazione di tipo è unica (noi lo sappiamo, ma il pianificatore per ora non lo sospetta): WITH T AS ( SELECT "@TipoDocumento" FROM "TipoDocumento" WHERE "TipoDocumento" = 'PianoLavori' LIMIT 1 ) ... WHERE d."TipoDocumento" = (TABLE T) ...

Sì, se la tabella/CTE è composta da un unico campo di un'unica registrazione, allora in PG si può scrivere anche così, invece di

d."TipoDocumento" = (SELECT "@TipoDocumento" FROM T LIMIT 1)

«Calcoli pigri» nelle query PostgreSQL

BitmapOr vs UNION

BitmapOr vs UNION

In alcuni casi, il Bitmap Heap Scan ci costerà molto — ad esempio, nella nostra situazione, quando molte registrazioni rientrano nelle condizioni richieste. Lo abbiamo ottenuto a causa di una condizione OR, trasformata in BitmapOr-operazione nel piano.
Torniamo al compito originale — dobbiamo trovare una registrazione che corrisponda a qualsiasi delle condizioni — quindi non è necessario cercare tutte e 59K registrazioni per entrambe le condizioni. C'è un modo per elaborare una condizione e passare alla seconda solo quando la prima non ha prodotto risultati. Ci aiuterà questa struttura:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

Il «LIMIT 1» esterno garantisce che la ricerca si concluda al primo risultato trovato. E se viene trovato nel primo blocco, l'esecuzione del secondo non avverrà (never executed nel piano).

«Nascondiamo sotto CASE» condizioni complesse

Ciò che rende l'istruzione originale estremamente scomoda è il controllo dello stato nella tabella collegata «DocumentoEstensione». Indipendentemente dalla veridicità delle altre condizioni nell'espressione (ad esempio, d.«Eliminato» IS NOT TRUE), questa join viene sempre eseguita e «costa risorse». Maggiore o minore sarà la quantità di risorse utilizzate, dipende dal volume di quella tabella.
Ma possiamo modificare la query in modo tale che la ricerca della registrazione collegata avvenga solo quando è realmente necessario:

SELECT
  ...
FROM
  "Documento" d
WHERE
  ... 0 AND
  CASE
    WHEN "$Bozza" IS NULL AND "Eliminato" IS NOT TRUE THEN (
      SELECT
        "Stato"[1] IS TRUE
      FROM
        "DocumentoEstensione"
      WHERE
        "@Documento" = d."@Documento"
    )
  END

Dal momento che nella tabella collegata non abbiamo bisogno di alcun campo , abbiamo l'opportunità di trasformare il JOIN in una condizione tramite sottoselezione.Lasciamo i campi indicizzabili «al di fuori» del CASE, le condizioni semplici dalla registrazione le inseriamo nel blocco WHEN — e ora la query «pesante» viene eseguita solo quando si passa al THEN.
Il mio cognome è «Totale»

Costruiamo l'istruzione risultante con tutte le meccaniche descritte sopra:

Stiamo raccogliendo la richiesta finale con tutte le meccaniche descritte sopra:

CON T COME (
  SELECT
    "@TipoDocumento"
  FROM
    "TipoDocumento"
  WHERE
    "TipoDocumento" = 'PianoLavori'
)
  (
    SELECT
      TRUE
    FROM
      "Documento" d
    WHERE
      ("Parte3", "TipoDocumento") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Bozza" IS NULL AND "Eliminato" IS NOT TRUE THEN (
          SELECT
            "Stato"[1] IS TRUE
          FROM
            "DocumentoEstensione"
          WHERE
            "@Documento" = d."@Documento"
        )
      END
    LIMIT 1
  )
UNIONE TUTTO
  (
    SELECT
      TRUE
    FROM
      "Documento" d
    WHERE
      ("TipoDocumento", "Dipendente") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Bozza" IS NULL AND "Eliminato" IS NOT TRUE THEN (
          SELECT
            "Stato"[1] IS TRUE
          FROM
            "DocumentoEstensione"
          WHERE
            "@Documento" = d."@Documento"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Adattiamo [per] gli indici

L'occhio esperto ha notato che le condizioni indicizzate nei sotto-blocchi UNION differiscono leggermente: questo perché abbiamo già indici adeguati sulla tabella. E se non ci fossero stati — sarebbe stato opportuno crearli: Documento(Parte3, TipoDocumento) e Documento(TipoDocumento, Dipendente).
sull'ordine dei campi nelle condizioni ROWDal punto di vista del pianificatore, ovviamente, si può scrivere anche (A, B) = (constA, constB), e (B, A) = (constB, constA). Ma quando si scrive nell'ordine dei campi nell'indice, una tale query è semplicemente più comoda da debug.
Qual è il piano?

[guarda su explain.tensor.ru]

Sfortunatamente, non siamo stati fortunati, e nel primo blocco UNION non è stato trovato nulla, quindi il secondo è stato comunque eseguito. Ma anche in questo caso — solo 0.037ms e 11 buffers!
Abbiamo accelerato la query e ridotto il "ridimensionamento" dei dati in memoria di diverse migliaia di volte, utilizzando tecniche abbastanza semplici — un buon risultato con un po' di copia e incolla. 🙂

Fonte: habr.com