ProHoster > blog > Utawala > Antipatterns za PostgreSQL: CTE x CTE

Antipatterns za PostgreSQL: CTE x CTE

Kwa sababu ya safu yangu ya kazi, lazima nishughulike na hali wakati msanidi programu anaandika ombi na kufikiria "Msingi ni wa busara, unaweza kushughulikia kila kitu yenyewe!Β«

Katika baadhi ya matukio (sehemu kutoka kwa ujinga wa uwezo wa hifadhidata, kwa sehemu kutoka kwa uboreshaji wa mapema), njia hii inaongoza kwa kuonekana kwa "Frankensteins".

Kwanza, nitatoa mfano wa ombi kama hilo:

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

Ili kutathmini kwa kiasi kikubwa ubora wa ombi, hebu tuunde seti fulani ya data kiholela:

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

Inageuka kuwa kusoma data kulichukua chini ya robo ya wakati Utekelezaji wa hoja:

Antipatterns za PostgreSQL: CTE x CTE[tazama kwenye explain.tensor.ru]

Kuitenganisha kipande kwa kipande

Wacha tuangalie kwa karibu ombi hilo na tufadhaike:

  1. Kwa nini ni pamoja na KURUDISHA hapa ikiwa hakuna CTE zinazojirudia?
  2. Kwa nini weka thamani za min/max katika CTE tofauti ikiwa zimefungwa kwa sampuli asili hata hivyo?
    + 25% wakati
  3. Kwa nini utumie 'CHAGUA * KUTOKA' isiyo na masharti mwishoni mwa CTE iliyotangulia?
    + 14% wakati

Katika kesi hii, tulikuwa na bahati sana kwamba Hash Join ilichaguliwa kwa muunganisho, na sio Nested Loop, kwa sababu basi hatungepokea pasi moja tu ya CTE Scan, lakini 10K!

kidogo kuhusu CTE ScanHapa lazima tukumbuke hilo CTE Scan ni sawa na Seq Scan - yaani, hakuna indexing, lakini tu utafutaji kamili, ambayo itahitaji 10K x 0.3ms = 3000ms kwa mizunguko na cte_max au 1K x 1.5ms = 1500ms wakati wa kuzunguka kwa cte_bind!
Kwa kweli, ulitaka kupata nini kama matokeo? Ndio, kwa kawaida hili ndilo swali linalokuja mahali fulani katika dakika ya 5 ya kuchambua maswali ya "hadithi tatu".

Tulitaka kutoa kwa kila jozi ya ufunguo wa kipekee min/max kutoka kwa kikundi kwa key_a.
Basi hebu kuitumia kwa hili kazi za dirisha:

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

Antipatterns za PostgreSQL: CTE x CTE
[tazama kwenye explain.tensor.ru]

Kwa kuwa kusoma data katika chaguzi zote mbili huchukua takriban 4-5ms sawa, basi faida yetu yote ya wakati -32% - hii ni katika fomu yake safi mzigo umeondolewa kutoka kwa msingi wa CPU, ikiwa ombi kama hilo linatekelezwa mara nyingi vya kutosha.

Kwa ujumla, haupaswi kulazimisha msingi "kubeba pande zote, tembeza mraba."

Chanzo: mapenzi.com

Kuongeza maoni