🥇PostgreSQL Antipatterns: CTE x CTE

Chifukwa cha momwe ntchito yanga ilili, ndimayenera kuthana ndi zochitika pamene wopanga alemba pempho ndikuganiza "Maziko ndi anzeru, adzagwira chilichonse chokha!«

Nthawi zina (mwina chifukwa cha umbuli wa Nawonso achichepere mphamvu, mwina chifukwa cha kukhathamiritsa msanga), njira imeneyi kumabweretsa zikamera wa "Frankensteins".

Choyamba, ndiroleni ndipereke chitsanzo cha pempho lotere:

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

Kuti tiwone bwinobwino mtundu wa funso, tiyeni tipange gulu lachisawawa:

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

Iwo likukhalira kuti kuwerenga deta anatenga zosakwana kotala la nthawi yonse kufunsa mafunso:

PostgreSQL Antipatterns: CTE x CTE [onani pa explain.tensor.ru]

Tiyeni tizigawanitsa chidutswa ndi chidutswa

Tiyeni tiwone bwinobwino pempholo ndikudabwa:

Chifukwa chiyani WITH RECURSIVE apa ngati palibe ma CTE obwereza?
Chifukwa chiyani magulu min/max values mu CTE yosiyana ngati akadali omangidwa ku zitsanzo zoyambirira?
+ 25% nthawi
Chifukwa chiyani mugwiritsire ntchito kuwerenganso kuchokera ku CTE yam'mbuyomu kumapeto kudzera pa 'SAKHANI * KUCHOKERA'?
+ 14% nthawi

Pankhaniyi, tinali ndi mwayi kwambiri kuti Hash Join adasankhidwa kuti alumikizane, osati Nested Loop, kuyambira pamenepo sitikadalandira ngakhale CTE Scan imodzi, koma 10K!

Zambiri za CTE ScanApa tiyenera kukumbukira CTE Scan ndi analogue ya Seq Scan - ndiye kuti, palibe kulondolera, koma kuwerengera kwathunthu, komwe kungafune 10K x 0.3ms = 3000ms poyendetsa njinga kudzera cte_max kapena 1K x 1.5ms = 1500ms pozungulira ndi cte_bind!
Kwenikweni, kodi mumafuna mutapeza chiyani? Eya, ndiye funso lomwe nthawi zambiri limatuluka kwinakwake pafupi mphindi 5 posanthula mafunso a "nsanjika zitatu".

Tinkafuna kutulutsa makiyi aliwonse apadera min/kuchuluka kuchokera pagulu ndi key_a.
Ndiye tiyeni tigwiritse ntchito izi ntchito zenera:

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

PostgreSQL Antipatterns: CTE x CTE
[onani pa explain.tensor.ru]

Popeza kuwerenga zambiri pazosankha ziwirizi kumatenga pafupifupi 4-5ms, ndiye kuti timapindula nthawi zonse -32% - izi ziri mu mawonekedwe ake oyera katundu wachotsedwa ku CPU base, ngati pempho loterolo likuchitidwa pafupipafupi mokwanira.

Nthawi zambiri, palibe chifukwa chokakamiza zoyambira "kuvala zinthu zozungulira, kugudubuza zinthu".

Source: www.habr.com