Antipattern PostgreSQL: CTE x CTE

Amarga garis kerjaku, aku kudu ngatasi kahanan nalika pangembang nulis panjaluk lan mikir "Dasar pinter, bisa nangani kabeh dhewe!«

Ing sawetara kasus (sebagéyan saka nggatekke saka kapabilitas database, sebagéyan saka optimizations durung wayahe), pendekatan iki ndadékaké kanggo katon saka "Frankensteins".

Kaping pisanan, aku bakal menehi conto panjaluk kasebut:

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

Kanggo ngevaluasi sacara substansial kualitas panyuwunan, ayo nggawe sawetara set data sing sewenang-wenang:

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

Pranyata iku maca data njupuk kurang saka seprapat wektu eksekusi pitakon:

[deleng ing explain.tensor.ru]

Mbukak sepotong demi sepotong

Ayo dideleng kanthi luwih rinci babagan panjaluk kasebut lan dadi bingung:

1. Napa WITH RECURSIVE kene yen ora ana CTE rekursif?
2. Napa nilai min / maksimal klompok ing CTE sing kapisah yen banjur diikat menyang sampel asli?
   + 25% wektu
3. Napa nggunakake 'PILIH * FROM' tanpa syarat ing pungkasan kanggo mbaleni CTE sadurunge?
   + 14% wektu

Ing kasus iki, kita padha banget Bejo sing Hash Join dipilih kanggo sambungan, lan ora Nested Loop, amarga banjur kita bakal nampa ora mung siji CTE Scan pass, nanging 10K!

sethitik babagan CTE ScanIng kene kita kudu ngelingi CTE Scan padha karo Seq Scan - yaiku, ora ana indeksasi, nanging mung telusuran lengkap, sing mbutuhake 10K x 0.3 ms = 3000ms kanggo siklus dening cte_max utawa 1K x 1.5 ms = 1500ms nalika looping dening cte_bind!
Bener, apa sing sampeyan pengin entuk minangka asil? Ya, biasane iki pitakonan sing muncul ing endi wae ing menit kaping 5 nganalisis pitakon "telung crita".

We wanted kanggo output kanggo saben pasangan tombol unik min / maks saka grup dening key_a.
Dadi ayo digunakake kanggo iki fungsi jendhela:

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

[deleng ing explain.tensor.ru]

Wiwit maca data ing loro opsi njupuk padha kira-kira 4-5ms, banjur kabeh gain wektu kita -32% - iki ing wangun murni mbukak dibusak saka CPU dhasar, yen panyuwunan kasebut asring ditindakake.

Umumé, sampeyan ora kudu meksa dhasar kanggo "nggawa sing bunder, gulung kothak."

Source: www.habr.com