🥇PostgreSQL Antipatterns: CTE x CTE

Nel corso della mia attività, mi trovo spesso ad affrontare situazioni in cui lo sviluppatore scrive una query e pensa «il database è intelligente, gestirà tutto da solo!"«

In alcuni casi (in parte per ignoranza delle possibilità del DB, in parte per ottimizzazioni premature), questo approccio porta alla creazione di «frankenstein».

Prima darò un esempio di una tale query:

-- per ogni coppia chiave troviamo i valori associati ai 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 min/max dei valori 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 in modo concreto la qualità della query, creiamo un insieme di dati arbitrario:

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 a fondo

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

Esaminiamo attentamente la query e ci poniamo:

Perché qui c'è un WITH RECURSIVE, se non ci sono CTE ricorsivi?

Perché raggruppare i valori min/max in un separato CTE, se poi sono comunque legati all'originale selezione?
+25% di tempo
Perché utilizzare alla fine una lettura ripetuta dal CTE precedente con un ‘SELECT * FROM’ incondizionato?
+14% di tempo
In questo caso siamo stati fortunati che per la join è stata scelta l'Hash Join e non il Nested Loop, altrimenti avremmo avuto non un solo passaggio del CTE Scan, ma 10K!

Un po' sul CTE Scan

Qui bisogna ricordare cheil CTE Scan è equivalente a un Seq Scan — cioè nessuna indicizzazione, ma solo un completo esame che richiederebbe 10K x 0.3ms = 3000ms nei cicli su cte_max 1K x 1.5ms = o 1500ms nei cicli su cte_bind In effetti, cosa volevamo ottenere come risultato?!
Ah, di solito è proprio questa domanda che viene in mente intorno al quinto minuto di analisi delle query «a tre livelli». Volevamo per ogni coppia chiave unica ottenere

i valori min/max del gruppo per key_a Quindi utilizziamo.
le 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);:

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);

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

Poiché la lettura dei dati in entrambe le opzioni richiede circa 4-5 ms, tutto il nostro guadagno temporale -32% è in forma pura il carico rimosso dalla CPU del database, se tale richiesta viene eseguita abbastanza frequentemente.

In generale, non è opportuno costringere il database a "portare il tondo, rotolare il quadrato".

Fonte: habr.com