🥇Antipatterns PostgreSQL: CTE x CTE

Datorită domeniului meu de lucru, trebuie să fac față situațiilor în care un dezvoltator scrie o solicitare și gândește „Baza este inteligentă, poate gestiona totul singură!«

În unele cazuri (parțial din ignorarea capacităților bazei de date, parțial din optimizări premature), această abordare duce la apariția „Frankensteins”.

Mai întâi, voi da un exemplu de astfel de solicitare:

-- для каждой ключевой пары находим ассоциированные значения полей
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;

Pentru a evalua în mod substanțial calitatea unei cereri, să creăm un set de date arbitrare:

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

Se pare că citirea datelor a durat mai puțin de un sfert din timp executarea interogării:

[uita-te pe explic.tensor.ru]

Demontând-o bucată cu bucată

Să aruncăm o privire mai atentă la cerere și să fim nedumeriți:

De ce este WITH RECURSIVE aici dacă nu există CTE recursive?
De ce să grupați valorile min/max într-un CTE separat dacă sunt oricum legate de eșantionul original?
+25% timp
De ce să folosiți un „SELECT * FROM” necondiționat la sfârșit pentru a repeta CTE-ul anterior?
+14% timp

În acest caz, am fost foarte norocoși că a fost ales Hash Join pentru conexiune, și nu Nested Loop, pentru că atunci am fi primit nu doar o trecere CTE Scan, ci 10K!

puțin despre CTE ScanAici trebuie să ne amintim asta CTE Scan este similar cu Seq Scan - adică fără indexare, ci doar o căutare completă, ceea ce ar necesita 10K x 0.3 ms = 3000ms pentru cicluri de cte_max sau 1K x 1.5 ms = 1500ms când se face buclă de către cte_bind!
De fapt, ce ai vrut să obții ca rezultat? Da, de obicei, aceasta este întrebarea care apare undeva în al 5-lea minut de analiză a interogărilor „cu trei etaje”.

Am vrut să ieșim pentru fiecare pereche de chei unică min/max din grup prin key_a.
Deci, să-l folosim pentru asta funcțiile ferestrei:

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

[uita-te pe explic.tensor.ru]

Deoarece citirea datelor în ambele opțiuni durează aproximativ 4-5 ms, atunci câștigăm tot timpul -32% - acesta este în forma sa cea mai pură sarcina eliminată de la CPU de bază, dacă o astfel de cerere este executată suficient de des.

În general, nu ar trebui să forțați baza să „carea cea rotundă, rulați cea pătrată”.

Sursa: www.habr.com

Antipatterns PostgreSQL: CTE x CTE

Demontând-o bucată cu bucată

Adauga un comentariu Anula răspuns