🥇Antipatterns di PostgreSQL: CTE x CTE

Nel corso della mia attività, mi trovo spesso di fronte a situazioni in cui uno sviluppatore scrive una query e pensa «il database è intelligente, si occupa di tutto da solo!"«

In alcuni casi (parzialmente per mancanza di conoscenza delle capacità del DB, parzialmente per ottimizzazioni prematurate) questo approccio porta alla creazione di 'frankenstein'.

Iniziamo con un esempio di tale query:

-- per ogni coppia chiave troviamo i valori associati dei campi
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- troviamo i valori min/max per ogni prima chiave
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- colleghiamo le coppie chiave e i valori min/max per la prima chiave
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

Per valutare concretamente la qualità della query, creiamo un insieme di dati casuale:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

Si scopre che la lettura dei dati ha richiesto meno di un quarto del tempo totale di esecuzione della query: Analizziamo nel dettaglio

Antipattern di PostgreSQL: CTE x CTE [guarda su explain.tensor.ru]

Diamo un'occhiata alla query e ci chiediamo:

Perché usare WITH RECURSIVE se non ci sono CTE ricorsivi?

Perché raggruppare i valori min/max in un CTE separato, se poi comunque vengono legati al campione originale?
+25% di tempo
Perché usare alla fine una lettura ripetuta dal CTE precedente con il semplice 'SELECT * FROM'?
+14% di tempo
In questo caso siamo stati fortunati che per la join sia stata scelta la Hash Join, e non la Nested Loop; altrimenti avremmo avuto non un singolo passaggio CTE Scan, ma 10K!

un po' su CTE Scan

È importante ricordare cheCTE Scan è analogo a Seq Scan — quindi senza indicizzazione, ma solo una scansione completa, che avrebbe richiesto 10K x 0.3ms = 3000ms con cicli su cte_max 1K x 1.5ms = o 1500ms con cicli su cte_bind In sostanza, cosa volevamo ottenere come risultato?!
Ah, di solito questa è proprio la domanda che sorge circa al quinto minuto di analisi di query 'a tre piani'. Volevamo per ogni coppia chiave unica mostrare

min/max del gruppo per key_a Quindi usiamo.
funzioni di finestra SELECT DISTINCT ON(key_a, key_b) key_a a , key_b b , max(fld1) OVER(w) bind_fld1 , min(fld2) OVER(w) bind_fld2 FROM tbl WINDOW w AS (PARTITION BY key_a);:

Poiché la lettura dei dati in entrambe le varianti impiega all'incirca 4-5ms, il nostro guadagno totale di tempo

Antipattern di PostgreSQL: CTE x CTE
[guarda su explain.tensor.ru]

è semplicemente -32% il carico rimosso dalla CPU del database , se tale query viene eseguita abbastanza frequentemente.In generale, non si dovrebbe costringere il database a 'portare il tondo e rotolare il quadrato'.

Nel corso della mia attività, mi trovo spesso di fronte a situazioni in cui uno sviluppatore scrive una query e pensa "il database è intelligente, si occupa di tutto da solo!" In alcuni casi (parzialmente per mancanza di conoscenza delle capacità del DB, parzialmente per ottimizzazioni prematurate) questo approccio porta alla creazione di 'frankenstein'. Iniziamo con un esempio di tale query: -- per ogni coppia chiave troviamo i valori associati dei campi WITH RECURSIVE cte_bind AS

Fonte: habr.com