Antipatterns PostgreSQL: CTE x CTE

Kusabab padamelan abdi, abdi kedah nungkulan kaayaan nalika pamekar nyerat pamundut sareng mikir "Dasar pinter, tiasa ngadamel sadayana nyalira!«

Dina sababaraha kasus (sawaréh ti jahiliah tina kamampuhan database, sabagean ti optimizations prématur), pendekatan ieu ngabalukarkeun penampilan "Frankensteins".

Mimiti, kuring bakal masihan conto pamundut sapertos kieu:

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

Pikeun sacara substantif ngevaluasi kualitas pamundut, hayu urang nyiptakeun sababaraha set data sawenang-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);

Tétéla éta maca data nyandak kirang ti saparapat waktu palaksanaan query:

[tingali dina explain.tensor.ru]

Nyokot eta sapotong demi sapotong

Hayu urang tingali langkung caket kana pamundut sareng janten bingung:

1. Naha WITH RECURSIVE didieu upami teu aya CTE rekursif?
2. Naha nilai grup min / max dina CTE anu misah upami aranjeunna teras dihijikeun kana conto aslina?
   + 25% waktos
3. Naha make saratna 'PILIH * FROM' dina tungtung CTE saméméhna?
   + 14% waktos

Dina hal ieu, kami pisan untung nu Hash Gabung dipilih pikeun sambungan, sarta teu Nested Citakan: Loop, sabab lajeng urang bakal nampi teu ngan hiji CTE Scan pass, tapi 10K!

saeutik ngeunaan CTE ScanDi dieu urang kudu inget yen CTE Scan sami sareng Seq Scan - nyaeta, euweuh indexing, tapi ngan hiji pilarian lengkep, nu bakal merlukeun 10K x 0.3 ms = 3000ms pikeun siklus ku cte_max atawa 1K x 1.5 ms = 1500ms nalika looping ku cte_bind!
Sabenerna, naon anu anjeun hoyong kéngingkeun salaku hasilna? Leres, biasana ieu patarosan anu muncul dina menit ka-5 nganalisa patarosan "tilu-carita".

Kami hoyong kaluaran pikeun unggal pasangan konci anu unik mnt / max ti grup ku key_a.
Ku kituna hayu urang ngagunakeun eta pikeun ieu fungsi jandela:

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

[tingali dina explain.tensor.ru]

Kusabab maca data dina duanana pilihan nyokot sarua kira 4-5ms, teras sadayana gain waktos urang -32% - ieu dina formulir purest na beban dipiceun tina CPU dasar, upami pamundut sapertos kitu sering dilaksanakeun.

Sacara umum, anjeun henteu kedah maksakeun dasarna pikeun "mawa anu buleud, gulung kuadrat."

sumber: www.habr.com