🥇PostgreSQL mótmynstur: CTE x CTE

Vegna eðlis vinnu minnar þarf ég að takast á við aðstæður þegar verktaki skrifar beiðni og hugsar „Grunnurinn er snjall, hann ræður við allt sjálfur!«

Í sumum tilfellum (að hluta vegna vanþekkingar á getu gagnagrunnsins, að hluta til vegna ótímabærrar hagræðingar), leiðir þessi nálgun til útlits „Frankensteins“.

Fyrst skal ég nefna dæmi um slíka beiðni:

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

Til að meta efnislega gæði beiðni skulum við búa til nokkur handahófskennd gagnasett:

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

Það kemur í ljós að lestur gagna tók minna en fjórðung tímans framkvæmd fyrirspurnar:

[horfðu á explain.tensor.ru]

Að taka það í sundur stykki fyrir stykki

Við skulum skoða beiðnina nánar og vera undrandi:

Af hverju er WITH RECURSIVE hér ef það eru engar endurkvæmar CTEs?
Hvers vegna flokka lágmarks-/hámarksgildi í sérstakt CTE ef þau eru þá samt tengd upprunalegu sýninu?
+25% tíma
Af hverju að nota skilyrðislaust „SELECT * FROM“ í lokin til að endurtaka fyrri CTE?
+14% tíma

Í þessu tilfelli vorum við mjög heppin að Hash Join var valinn fyrir tenginguna, en ekki Nested Loop, því þá hefðum við ekki bara fengið einn CTE Scan pass, heldur 10K!

smá um CTE ScanHér verðum við að muna það CTE Scan er svipað og Seq Scan - það er engin flokkun, heldur aðeins fullkomin leit, sem myndi krefjast 10K x 0.3ms = 3000ms fyrir lotur eftir cte_max eða 1K x 1.5ms = 1500ms þegar þú ert með lykkju með cte_bind!
Reyndar, hvað vildirðu fá í kjölfarið? Já, venjulega er þetta spurningin sem kemur upp einhvers staðar á 5. mínútu við að greina „þriggja hæða“ fyrirspurnir.

Við vildum gefa út fyrir hvert einstakt lyklapar mín/max frá hópi eftir key_a.
Svo við skulum nota það í þetta gluggaaðgerðir:

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

[horfðu á explain.tensor.ru]

Þar sem lestur gagna í báðum valkostum tekur það sama um það bil 4-5ms, þá hagnast allur okkar tíma -32% - þetta er í sinni hreinustu mynd álag fjarlægt af grunn CPU, ef slík beiðni er framkvæmd nógu oft.

Almennt séð ættirðu ekki að þvinga grunninn til að „bera hringinn, rúlla ferningnum“.

Heimild: www.habr.com

PostgreSQL mótmynstur: CTE x CTE

Að taka það í sundur stykki fyrir stykki

Bæta við athugasemd Hætta við svar