ProHoster > Ibhulogi > Ulawulo > I-PostgreSQL Antipatterns: CTE x CTE

I-PostgreSQL Antipatterns: CTE x CTE

Ngenxa yomgca wam womsebenzi, kufuneka ndijongane neemeko xa umphuhlisi ebhala isicelo kwaye acinge "Isiseko sihlakaniphile, siyakwazi ukusingatha yonke into ngokwayo!Β«

Kwezinye iimeko (inxalenye yokungazi kakuhle izakhono zesiseko sedatha, ngokuyinxenye ukusuka ekuphuculweni kwangaphambi kwexesha), le ndlela ikhokelela ekubonakaleni kwe "Frankensteins".

Okokuqala, ndiza kunika umzekelo wesicelo esinjalo:

-- для ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΠΊΠ»ΡŽΡ‡Π΅Π²ΠΎΠΉ ΠΏΠ°Ρ€Ρ‹ Π½Π°Ρ…ΠΎΠ΄ΠΈΠΌ ассоциированныС значСния ΠΏΠΎΠ»Π΅ΠΉ
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;

Ukuvandlakanya umgangatho wesicelo, makhe senze iseti yedatha engenasizathu:

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

Kuvela ukuba ukufunda idatha kuthathe ngaphantsi kwekota yexesha Ukwenziwa kombuzo:

I-PostgreSQL Antipatterns: CTE x CTE[Jonga apha explain.tensor.ru]

Ukuyicalula iqhekeza ngeqhekeza

Masijonge ngakumbi isicelo kwaye sixakeke:

  1. Kutheni i-RECURSIVE apha ukuba azikho ii-CTEs eziphindaphindayo?
  2. Kutheni le nto iqela le-min/max amaxabiso kwi-CTE eyahlukileyo ukuba ibotshelelwe kwisampulu yoqobo?
    + 25% ixesha
  3. Kutheni usebenzisa i-'KHETHA * FROM 'engenamiqathango ekupheleni ukuphinda i-CTE yangaphambili?
    + 14% ixesha

Kule meko, sibe nethamsanqa kakhulu ukuba i-Hash Joyina ikhethelwe uqhagamshelo, kwaye hayi i-Nested Loop, kuba ke besingayi kufumana nje ipasi enye ye-CTE Scan, kodwa i-10K!

kancinci malunga CTE ScanApha kufuneka siyikhumbule loo nto I-CTE Scan iyafana neSeq Scan - oko kukuthi, akukho indexing, kodwa kuphela uphendlo olupheleleyo, oluya kufuna 10K x 0.3ms = 3000ms kwimijikelo nge cte_max okanye 1K x 1.5ms = 1500ms xa ulophu nge cte_bind!
Ngokwenene, ubufuna ukufumana ntoni ngenxa yoko? Ewe, ngokuqhelekileyo lo ngumbuzo ovela kwindawo ethile kwimizuzu ye-5 yokuhlalutya imibuzo "yemigangatho emithathu".

Sasifuna ukuvelisa isibini esisitshixo esikhethekileyo min/ubuninzi ukusuka kwiqela nge key_a.
Ngoko masiyisebenzisele oku imisebenzi yefestile:

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

I-PostgreSQL Antipatterns: CTE x CTE
[Jonga apha explain.tensor.ru]

Ekubeni idatha yokufunda kuzo zombini iinketho ithatha okufanayo malunga ne-4-5ms, ngoko lonke ixesha lethu lizuza -32% - oku kukwimo ecocekileyo umthwalo ususiwe kwisiseko se-CPU, ukuba eso sicelo senziwe rhoqo ngokwaneleyo.

Ngokubanzi, akufanele unyanzelise isiseko ukuba "uthwale umjikelo, qengqeleka isikwere."

umthombo: www.habr.com

Yongeza izimvo