ProHoster > blog > amministrazione > Antipattern PostgreSQL: CTE x CTE

Antipattern PostgreSQL: CTE x CTE

A causa del mio lavoro, devo affrontare situazioni in cui uno sviluppatore scrive una richiesta e pensa "La base è intelligente, può gestire tutto da sola!«

In alcuni casi (in parte per ignoranza delle capacità del database, in parte per ottimizzazioni premature), questo approccio porta alla comparsa di “Frankenstein”.

Innanzitutto, fornirò un esempio di tale richiesta:

-- для каждой ключевой пары находим ассоциированные значения полей
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
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, 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 sostanziale la qualità di una richiesta, creiamo un set 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 esecuzione della query:

Antipattern PostgreSQL: CTE x CTE[Guarda spiegare.tensor.ru]

Smontandolo pezzo per pezzo

Diamo un'occhiata più da vicino alla richiesta e restiamo perplessi:

  1. Perché qui è WITH RECURSIVE se non ci sono CTE ricorsivi?
  2. Perché raggruppare i valori min/max in un CTE separato se poi sono comunque legati al campione originale?
    +25% di tempo
  3. Perché utilizzare un "SELECT * FROM" incondizionato alla fine per ripetere il CTE precedente?
    +14% di tempo

In questo caso, siamo stati molto fortunati che per la connessione sia stato scelto Hash Join e non Nested Loop, perché in tal caso avremmo ricevuto non solo un passaggio CTE Scan, ma 10K!

qualcosa sulla scansione CTEQui dobbiamo ricordarlo La scansione CTE è simile alla scansione Seq - cioè nessuna indicizzazione, ma solo una ricerca completa, che richiederebbe 10K x 0.3 ms = 3000ms per cicli di cte_max o 1K x 1.5 ms = 1500ms durante il looping tramite cte_bind!
In realtà, cosa volevi ottenere come risultato? Sì, di solito questa è la domanda che sorge da qualche parte nel quinto minuto di analisi delle query "a tre piani".

Volevamo produrre un output per ogni coppia di chiavi univoca min/max dal gruppo tramite key_a.
Quindi usiamolo per questo funzioni della 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);

Antipattern PostgreSQL: CTE x CTE
[Guarda spiegare.tensor.ru]

Poiché la lettura dei dati in entrambe le opzioni richiede circa 4-5 ms, tutto il nostro tempo guadagna -32% - questo è nella sua forma più pura carico rimosso dalla CPU di base, se tale richiesta viene eseguita abbastanza spesso.

In generale, non dovresti forzare la base a “portare quella rotonda, far rotolare quella quadrata”.

Fonte: habr.com

Aggiungi un commento