Antipattern di PostgreSQL: JOIN e OR dannosi

Attenzione alle operazioni, i buffer possono portare…
Prendiamo come esempio una piccola query per esaminare alcuni approcci generali all'ottimizzazione delle query su PostgreSQL. Utilizzarli o meno è una vostra scelta, ma è utile conoscerli.

In versioni successive di PG, la situazione potrebbe cambiare con una maggiore "intelligenza" del planner, ma per 9.4/9.6 appare sostanzialmente la stessa, come negli esempi qui.

Considererò una query piuttosto 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."Faccia3" = 19091 or d."Dipendente" = 19091) AND
  d."$Bozza" IS NULL AND
  d."Cancellato" IS NOT TRUE AND
  doc_ex."Stato"[1] IS TRUE AND
  t_doc."TipoDocumento" = 'PianoLavori'
LIMIT 1;

riguardo ai nomi delle tabelle e dei campiAi nomi "russi" dei campi e delle tabelle si può avere un'opinione diversa, ma è una questione di gusto. Poiché nel nostro "Tensor" non ci sono sviluppatori stranieri, e PostgreSQL ci consente di dare nomi anche in geroglifici, se sono racchiusi tra virgolette, preferiamo nominare gli oggetti in modo chiaro e comprensibile, per evitare ambiguità.
Esaminiamo il piano risultante:

[guarda su explain.tensor.ru]

144ms e quasi 53K buffer — il che significa oltre 400MB di dati! E saremo fortunati se tutti questi dati si trovano nella cache al momento della nostra query; altrimenti, richiederà molto più tempo durante la lettura dal disco.

L'algoritmo è il fattore più importante!

Per ottimizzare qualsiasi query, dobbiamo prima capire cosa deve effettivamente fare.
Lasciamo da parte lo sviluppo della struttura del DB in questo articolo e supponiamo che possiamo "riscrivere" la query e/o applicare sul database alcuni indici di cui abbiamo bisogno e/o applicare qualche modifica al database che ci serve indici.

Quindi, la query:
— verifica l'esistenza di almeno un documento
— nello stato di nostro interesse e di un tipo specifico
— dove l'autore o l'esecutore è il dipendente di nostro interesse

JOIN + LIMIT 1

Spesso per uno sviluppatore è più semplice scrivere una query dove prima si uniscono molte tabelle e poi da tutto questo si ottiene un'unica registrazione. Ma più facile per lo sviluppatore non significa necessariamente più efficiente per il DB.
Nel nostro caso c'erano solo 3 tabelle — e che effetto…

Per iniziare, eliminiamo l'unione con la tabella "TipoDocumento", e informiamo il database che il tipo di registrazione è unico (noi lo sappiamo, ma il planner non lo sospetta ancora):

WITH T AS (
  SELECT
    "@TipoDocumento"
  FROM
    "TipoDocumento"
  WHERE
    "TipoDocumento" = 'PianoLavori'
  LIMIT 1
)
...
WHERE
  d."TipoDocumento" = (TABLE T)
...

Sì, se la tabella/CTE consiste in un'unica colonna con un'unica registrazione, in PG è possibile scrivere anche così, invece di

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

Il calcolo "pigro" nelle query PostgreSQL

BitmapOr vs UNION

In alcuni casi, il Bitmap Heap Scan può costarci molto — ad esempio nella nostra situazione, dove molte registrazioni soddisfano la condizione richiesta. L'abbiamo ottenuto a causa di una condizione OR, che è diventata un'operazione BitmapOrnel piano.
Torniamo all'attività iniziale — dobbiamo trovare una registrazione che soddisfi chiunque delle condizioni — il che significa che non è necessario cercare tutte le 59K registrazioni su entrambe le condizioni. Esiste un modo per gestire una condizione e passare alla seconda solo quando non si trova nulla sulla prima.Ci aiuterà una struttura del genere:

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

Il "LIMIT 1" esterno garantisce che la ricerca si fermi al primo risultato trovato. E se viene trovato nel primo blocco, il secondo non verrà eseguito (never executed nel piano).

Nascondere sotto CASE condizioni complesse

Nella query originale, c'è un aspetto estremamente scomodo — la verifica dello stato attraverso la tabella collegata "DocumentoEstensione". Indipendentemente dalla verità delle altre condizioni nell'espressione (ad esempio, d."Cancellato" IS NOT TRUE), questa unione viene sempre eseguita e "consuma risorse". Più o meno risorse saranno spese — dipende dalla dimensione di quella tabella.
Ma possiamo modificare la query in modo tale che la ricerca della registrazione collegata avvenga solo quando è veramente necessaria:

SELECT
  ...
FROM
  "Documento" d
WHERE
  ... /*condizione indice*/ AND
  CASE
    WHEN "$Bozza" IS NULL AND "Cancellato" IS NOT TRUE THEN (
      SELECT
        "Stato"[1] IS TRUE
      FROM
        "DocumentoEstensione"
      WHERE
        "@Documento" = d."@Documento"
    )
  END

Poiché da questa tabella collegata non ci serve alcun campo , possiamo trasformare il JOIN in una condizione mediante sottoquery.Manteniamo i campi indicizzabili "al di fuori" del CASE, portando le condizioni semplici nella clausola WHEN — e ora la query "pesante" viene eseguita solo al passaggio nel THEN.
Il mio cognome è "Risultato"

Costruiamo la query risultante con tutte le meccaniche descritte sopra:

Compiliamo la query risultante con tutte le meccaniche descritte sopra:

WITH T AS (
  SELECT
    "@TipoDocumento"
  FROM
    "TipoDocumento"
  WHERE
    "TipoDocumento" = 'PianoLavori'
)
  (
    SELECT
      TRUE
    FROM
      "Documento" d
    WHERE
      ("Persona3", "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
  )
UNION ALL
  (
    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 [a] indici

L'occhio esperto ha notato che le condizioni indicizzate nei sotto-blocchi UNION differiscono leggermente — questo perché abbiamo già indici adatti sulla tabella. E se non ci fossero — sarebbe opportuno crearli: Documento(Persona3, TipoDocumento) e Documento(TipoDocumento, Dipendente).
sull'ordine dei campi nelle condizioni ROWDal punto di vista del pianificatore, certo, si può scrivere anche (A, B) = (costanteA, costanteB), e (B, A) = (costanteB, costanteA). Ma scrivendo nell'ordine dei campi nell'indice, tale query è semplicemente più facile da debug.
Che c'è nel piano?

[guarda su explain.tensor.ru]

Purtroppo, non abbiamo avuto fortuna, e nel primo blocco UNION non è stato trovato nulla, quindi il secondo è andato comunque in esecuzione. Ma anche in questo caso — solo 0.037ms e 11 buffers!
Abbiamo accelerato la query e ridotto il «caricamento» dei dati in memoria di diverse migliaia di volte, utilizzando metodiche abbastanza semplici — un buon risultato per un po' di copia e incolla. 🙂

Fonte: habr.com