🥇Mga Antipattern sa PostgreSQL: CTE x CTE

Tungod sa kinaiya sa akong trabaho, kinahanglan nakong atubangon ang mga sitwasyon kung ang usa ka developer nagsulat og hangyo ug naghunahuna "Ang sukaranan maalamon, kini makahimo sa tanan sa iyang kaugalingon!«

Sa pipila ka mga kaso (sa bahin gikan sa pagkawalay alamag sa mga kapabilidad sa database, usa ka bahin gikan sa ahat nga pag-optimize), kini nga pamaagi modala ngadto sa dagway sa "Frankensteins".

Una, maghatag ako usa ka pananglitan sa ingon nga hangyo:

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

Aron masusi pag-ayo ang kalidad sa usa ka hangyo, maghimo kita og pipila ka arbitraryong set sa datos:

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

Kini nahimo nga Ang pagbasa sa datos gikuha ubos sa ikaupat nga bahin sa panahon pagpatuman sa pangutana:

[tan-awa sa explain.tensor.ru]

Gibahinbahin kini sa usa ka piraso

Atong tan-awon pag-ayo ang hangyo ug maglibog:

Ngano nga ang WITH RECURSIVE dinhi kung wala’y mga recursive CTE?
Ngano nga grupo nga min / max nga mga kantidad sa usa ka bulag nga CTE kung sila gihigot sa orihinal nga sample gihapon?
+ 25% nga oras
Ngano nga mogamit ug walay kondisyon nga 'PILI * GIKAN' sa katapusan aron masubli ang miaging CTE?
+ 14% nga oras

Sa kini nga kaso, kami swerte kaayo nga ang Hash Join ang gipili alang sa koneksyon, ug dili ang Nested Loop, tungod kay makadawat kami dili lang usa ka CTE Scan pass, apan 10K!

gamay bahin sa CTE ScanDinhi kinahanglan natong hinumdoman kana Ang CTE Scan susama sa Seq Scan - kana mao, walay pag-indeks, apan usa lamang ka kompleto nga pagpangita, nga magkinahanglan 10K x 0.3ms = 3000ms alang sa mga siklo pinaagi sa cte_max o 1K x 1.5ms = 1500ms kung nag-loop pinaagi sa cte_bind!
Sa pagkatinuod, unsa ang gusto nimong makuha isip resulta? Oo, kasagaran kini ang pangutana nga moabut sa usa ka dapit sa ika-5 nga minuto sa pag-analisar sa "tulo ka istorya" nga mga pangutana.

Gusto namon nga mag-output alang sa matag talagsaon nga pares sa yawe min/max gikan sa grupo pinaagi sa key_a.
Busa atong gamiton kini alang niini mga gimbuhaton sa bintana:

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

[tan-awa sa explain.tensor.ru]

Tungod kay ang pagbasa sa datos sa duha nga mga kapilian nagkuha sa parehas nga gibana-bana nga 4-5ms, unya ang tanan namong oras nga makuha -32% - kini sa iyang labing putli nga porma load gikuha gikan sa base CPU, kung ang ingon nga hangyo ipatuman kanunay nga igo.

Sa kinatibuk-an, dili nimo kinahanglan nga pugson ang base sa "pagdala sa lingin nga usa, paligdi ang kuwadrado."

Source: www.habr.com

Mga Antipattern sa PostgreSQL: CTE x CTE

Gibahinbahin kini sa usa ka piraso

Idugang sa usa ka comment Отменить ответ