Antipatterny PostgreSQL: CTE x CTE

Vzhľadom na moju pracovnú náplň musím riešiť situácie, keď vývojár napíše požiadavku a myslí si „Základňa je šikovná, so všetkým si poradí sama!«

V niektorých prípadoch (čiastočne z neznalosti možností databázy, čiastočne z predčasných optimalizácií) tento prístup vedie k objaveniu sa „Frankensteinov“.

Najprv uvediem príklad takejto žiadosti:

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

Aby sme mohli vecne vyhodnotiť kvalitu žiadosti, vytvorte ľubovoľnú množinu údajov:

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

Ukazuje sa, že čítanie údajov trvalo menej ako štvrtinu času vykonanie dotazu:

[pozrite sa na explain.tensor.ru]

Rozbíjanie kostí

Pozrime sa bližšie na žiadosť a nechajme sa zmiasť:

1. Prečo je tu WITH RECURSIVE, ak neexistujú žiadne rekurzívne CTE?
2. Prečo zoskupovať hodnoty min/max do samostatného CTE, ak sú potom aj tak spojené s pôvodnou vzorkou?
   +25 % času
3. Prečo používať bezpodmienečné 'SELECT * FROM' na konci na zopakovanie predchádzajúceho CTE?
   +14 % času

V tomto prípade sme mali veľké šťastie, že pre pripojenie bol vybraný Hash Join, a nie Nested Loop, pretože potom by sme nedostali len jeden CTE sken, ale 10 XNUMX!

trochu o CTE ScanTu si to musíme pamätať CTE Scan je podobný ako Seq Scan - teda žiadne indexovanie, ale iba kompletné vyhľadávanie, ktoré by si vyžadovalo 10 0.3 x XNUMX ms = 3000ms pre cykly podľa cte_max alebo 1 1.5 x XNUMX ms = 1500ms pri zacyklení pomocou cte_bind!
Vlastne, čo ste chceli dosiahnuť ako výsledok? Áno, zvyčajne je to otázka, ktorá sa objaví niekde v 5. minúte analýzy „trojposchodových“ dopytov.

Chceli sme vytvoriť výstup pre každý jedinečný pár kľúčov min/max zo skupiny podľa kľúča_a.
Využime to teda na to funkcie okien:

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

[pozrite sa na explain.tensor.ru]

Keďže čítanie údajov v oboch možnostiach trvá rovnako približne 4-5 ms, získame tak celý náš čas -32% - toto je vo svojej najčistejšej forme záťaž odstránená zo základného CPU, ak sa takáto požiadavka vykonáva dostatočne často.

Vo všeobecnosti by ste nemali nútiť základňu „niesť okrúhlu, rolovať štvorcovú“.

Zdroj: hab.com