PostgreSQL Antipattern: JOIN e OR dannosi

Attenzione alle operazioni che portano buffer...
Utilizzando una piccola query come esempio, diamo un'occhiata ad alcuni approcci universali per ottimizzare le query in PostgreSQL. Dipende da te se usarli o meno, ma vale la pena conoscerli.

In alcune versioni successive di PG la situazione potrebbe cambiare man mano che lo scheduler diventa più intelligente, ma per 9.4/9.6 sembra più o meno lo stesso, come negli esempi qui.

Prendiamo una richiesta molto reale:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

sui nomi di tabelle e campiI nomi “russi” di campi e tabelle possono essere trattati diversamente, ma questa è una questione di gusti. Perché il qui a Tensor non ci sono sviluppatori stranieri e PostgreSQL ci permette di dare nomi anche in geroglifici, se presenti racchiuso tra virgolette, allora preferiamo nominare gli oggetti in modo inequivocabile e chiaro in modo che non ci siano discrepanze.
Diamo un'occhiata al piano risultante:

[Guarda spiegare.tensor.ru]

144 ms e quasi 53 KB di buffer - ovvero più di 400 MB di dati! E saremo fortunati se saranno tutti nella cache al momento della nostra richiesta, altrimenti ci vorrà molto più tempo durante la lettura dal disco.

L'algoritmo è molto importante!

Per poter ottimizzare in qualche modo qualsiasi richiesta, bisogna prima capire cosa dovrebbe fare.
Lasciamo per ora lo sviluppo della struttura del database stessa fuori dall'ambito di questo articolo e concordiamo sul fatto che possiamo farlo in modo relativamente "economico" riscrivere la richiesta e/o arrotolare sulla base alcune delle cose di cui abbiamo bisogno indici.

Quindi la richiesta:
— verifica l'esistenza di almeno qualche documento
- nella condizione di cui abbiamo bisogno e di un certo tipo
- dove l'autore o l'esecutore è il dipendente di cui abbiamo bisogno

UNISCITI + LIMITE 1

Molto spesso è più semplice per uno sviluppatore scrivere una query in cui viene prima unito un numero elevato di tabelle e quindi rimane solo un record dell'intero set. Ma più facile per lo sviluppatore non significa più efficiente per il database.
Nel nostro caso c'erano solo 3 tavoli - e qual è l'effetto...

Per prima cosa eliminiamo la connessione con la tabella "Tipo documento" e allo stesso tempo diciamo al database che il nostro record di tipo è unico (lo sappiamo, ma lo scheduler non ne ha ancora idea):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Sì, se la tabella/CTE è composta da un solo campo di un solo record, allora in PG puoi anche scrivere così, invece di

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Valutazione pigra nelle query PostgreSQL

BitmapO vs UNIONE

In alcuni casi, la scansione dell'heap bitmap ci costerà molto, ad esempio nella nostra situazione in cui molti record soddisfano le condizioni richieste. L'abbiamo capito perché La condizione OR è stata trasformata in BitmapOr- funzionamento in programma.
Torniamo al problema originale: dobbiamo trovare un record corrispondente a nessuno dalle condizioni, ovvero non è necessario cercare tutti i record da 59K in entrambe le condizioni. C'è un modo per risolvere una condizione e passare alla seconda solo quando nella prima non è stato trovato nulla. Il seguente disegno ci aiuterà:

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

Il LIMITE 1 “esterno” garantisce che la ricerca termini quando viene trovato il primo record. E se è già trovato nel primo blocco, il secondo blocco non verrà eseguito (mai eseguito nel rispetto di).

“Nascondere condizioni difficili sotto CASE”

C'è un momento estremamente scomodo nella query originale: controllare lo stato rispetto alla tabella correlata "DocumentExtension". Indipendentemente dalla verità delle altre condizioni nell'espressione (ad esempio, d.“Eliminato” NON È VERO), questa connessione viene sempre eseguita e “costa risorse”. Verranno spesi più o meno, dipende dalle dimensioni di questa tabella.
Puoi però modificare la query in modo che la ricerca di un record correlato avvenga solo quando è realmente necessaria:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Una volta dalla tabella collegata a noi nessuno dei campi è necessario per il risultato, allora abbiamo l'opportunità di trasformare JOIN in una condizione su una sottoquery.
Lasciamo i campi indicizzati "fuori dalle parentesi CASE", aggiungiamo semplici condizioni dal record al blocco WHEN - e ora la query "pesante" viene eseguita solo quando si passa a THEN.

Il mio cognome è "Total"

Raccogliamo la query risultante con tutte le meccaniche sopra descritte:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Regolazione [a] indici

Un occhio esperto ha notato che le condizioni indicizzate nei sottoblocchi UNION sono leggermente diverse, questo perché abbiamo già gli indici adatti sulla tabella. E se non esistessero, varrebbe la pena crearli: Documento(Persona3, Tipo documento) и Documento (Tipo documento, Dipendente).
sull'ordine dei campi nelle condizioni ROWDal punto di vista del pianificatore, ovviamente, puoi scrivere (A, B) = (costA, costB)E (B, A) = (costB, costA). Ma durante la registrazione nell'ordine dei campi nell'indice, è semplicemente più conveniente eseguire il debug di tale richiesta in un secondo momento.
Cosa c'è nel piano?

[Guarda spiegare.tensor.ru]

Sfortunatamente siamo stati sfortunati e non è stato trovato nulla nel primo blocco UNION, quindi il secondo è stato comunque eseguito. Ma anche così - solo 0.037 ms e 11 buffer!
Abbiamo velocizzato la richiesta e ridotto il pompaggio di dati in memoria diverse migliaia di volte, utilizzando tecniche abbastanza semplici: un buon risultato con un po' di copia-incolla. 🙂

Fonte: habr.com