Pulogalamu ya ProHoster > Blog > Ulamuliro > PostgreSQL Antipatterns: CTE x CTE

PostgreSQL Antipatterns: CTE x CTE

Chifukwa cha ntchito yanga, ndiyenera kuthana ndi zochitika zomwe wopanga alemba pempho ndikuganiza "Maziko ndi anzeru, amatha kuthana ndi chilichonse chokha!Β«

Nthawi zina (mwina chifukwa cha kusadziwa luso la Nawonso achichepere, mwina kuchokera kukhathamiritsa msanga), njira imeneyi kumabweretsa maonekedwe a "Frankensteins".

Choyamba, ndipereka 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 momwe pempho likuyendera, tiyeni tipange seti ya data yokhazikika:

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

Zikukhalira kuti kuwerenga deta anatenga zosakwana kotala la nthawi kufunsa mafunso:

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

Kuchigawaniza chidutswa ndi chidutswa

Tiyeni tiwone bwinobwino pempholo ndikudabwa:

  1. Chifukwa chiyani WITH RECURSIVE apa ngati palibe ma CTE obwereza?
  2. Chifukwa chiyani magulu a min/max mu CTE yosiyana ngati amangiriridwa ku zitsanzo zoyambirira?
    + 25% nthawi
  3. Chifukwa chiyani mugwiritse ntchito 'SAKHANI * KUCHOKERA' kumapeto kuti mubwereze CTE yam'mbuyo?
    + 14% nthawi

Pankhaniyi, tinali ndi mwayi kwambiri kuti Hash Join adasankhidwa kuti alumikizane, osati Nested Loop, chifukwa ndiye tikadalandira osati CTE Scan imodzi yokha, koma 10K!

pang'ono za CTE ScanApa tiyenera kukumbukira CTE Scan ndi yofanana ndi Seq Scan - ndiko kuti, palibe indexing, koma kufufuza kwathunthu, komwe kungafune 10K x 0.3ms = 3000ms zozungulira ndi cte_max kapena 1K x 1.5ms = 1500ms pozungulira ndi cte_bind!
Kwenikweni, kodi mumafuna mutapeza chiyani? Inde, nthawi zambiri ili ndi funso lomwe limabwera penapake mu mphindi ya 5 pofufuza 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 nthawi yathu yonse imapindula -32% - izi ziri mu mawonekedwe ake oyera katundu wachotsedwa ku base CPU, ngati pempho loterolo likuchitidwa nthawi zambiri mokwanira.

Nthawi zambiri, simuyenera kukakamiza maziko "kunyamula chozungulira, gudubuza lalikulu."

Source: www.habr.com

Kuwonjezera ndemanga