ProHoster > Blog > Pangangasiwa > Mga Antipattern ng PostgreSQL: CTE x CTE

Mga Antipattern ng PostgreSQL: CTE x CTE

Dahil sa aking linya ng trabaho, kailangan kong harapin ang mga sitwasyon kapag ang isang developer ay sumulat ng isang kahilingan at iniisip na "Ang base ay matalino, kaya nitong hawakan ang lahat ng bagay mismo!Β«

Sa ilang mga kaso (bahagi mula sa kamangmangan ng mga kakayahan ng database, bahagyang mula sa napaaga na pag-optimize), ang diskarte na ito ay humahantong sa hitsura ng "Frankensteins".

Una, magbibigay ako ng halimbawa ng naturang kahilingan:

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

Upang lubos na masuri ang kalidad ng isang kahilingan, gumawa tayo ng ilang arbitrary na set ng data:

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

Lumalabas na ang pagbabasa ng data ay tumagal nang wala pang isang-kapat ng oras pagpapatupad ng query:

Mga Antipattern ng PostgreSQL: CTE x CTE[tingnan sa explain.tensor.ru]

Paghiwalayin ito nang pira-piraso

Tingnan natin ang kahilingan at magtaka:

  1. Bakit nandito ang WITH RECURSIVE kung walang recursive CTEs?
  2. Bakit igrupo ang min/max na halaga sa isang hiwalay na CTE kung ang mga ito ay nakatali pa rin sa orihinal na sample?
    + 25% na oras
  3. Bakit gagamit ng walang kondisyong 'PUMILI * MULA' sa dulo upang ulitin ang nakaraang CTE?
    + 14% na oras

Sa kasong ito, napakaswerte namin na Hash Join ang napili para sa koneksyon, at hindi Nested Loop, dahil hindi lang isang CTE Scan pass ang natanggap namin, kundi 10K!

kaunti tungkol sa CTE ScanDito natin dapat tandaan iyon Ang CTE Scan ay katulad ng Seq Scan - iyon ay, walang pag-index, ngunit isang kumpletong paghahanap lamang, na mangangailangan 10K x 0.3ms = 3000ms para sa mga cycle ng cte_max o 1K x 1.5ms = 1500ms kapag nag-loop sa pamamagitan ng cte_bind!
Sa totoo lang, ano ang gusto mong makuha bilang resulta? Oo, kadalasan ito ang tanong na lumalabas sa isang lugar sa ika-5 minuto ng pagsusuri ng "tatlong kuwento" na mga query.

Nais naming mag-output para sa bawat natatanging key pair min/max mula sa pangkat ayon sa key_a.
Kaya't gamitin natin ito para dito mga function ng window:

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

Mga Antipattern ng PostgreSQL: CTE x CTE
[tingnan sa explain.tensor.ru]

Dahil ang pagbabasa ng data sa parehong mga opsyon ay tumatagal ng parehong humigit-kumulang 4-5ms, pagkatapos ay ang lahat ng aming oras ay nadagdag -32% - ito ay nasa pinakadalisay nitong anyo inalis ang load mula sa base CPU, kung ang naturang kahilingan ay madalas na naisakatuparan.

Sa pangkalahatan, hindi mo dapat pilitin ang base na "dalhin ang bilog, igulong ang parisukat."

Pinagmulan: www.habr.com

Magdagdag ng komento