ProHoster > blog > Amministrazzjoni > PostgreSQL Antipatterns: CTE x CTE

PostgreSQL Antipatterns: CTE x CTE

Minħabba l-linja tax-xogħol tiegħi, għandi nittratta sitwazzjonijiet meta żviluppatur jikteb talba u jaħseb "Il-bażi hija intelliġenti, tista 'timmaniġġja kollox innifsu!«

F'xi każijiet (parzjalment mill-injoranza tal-kapaċitajiet tad-database, parzjalment minn ottimizzazzjonijiet prematuri), dan l-approċċ iwassal għad-dehra ta '"Frankensteins".

L-ewwel, ser nagħti eżempju ta 'tali talba:

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

Biex tevalwa b'mod sostantiv il-kwalità ta' talba, ejja noħolqu xi sett ta' data arbitrarju:

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

Jirriżulta li il-qari tad-dejta ħa inqas minn kwart tal-ħin eżekuzzjoni tal-mistoqsija:

PostgreSQL Antipatterns: CTE x CTE[ħares lejn explic.tensor.ru]

Teħodha barra biċċa biċċa

Ejja nagħtu ħarsa aktar mill-qrib lejn it-talba u nkunu mħawda:

  1. Għaliex hawn WITH RECURSIVE jekk m'hemmx CTEs rikorsivi?
  2. Għaliex jiġbor il-valuri min/max f'CTE separat jekk imbagħad ikunu marbuta mal-kampjun oriġinali xorta waħda?
    + 25% ħin
  3. Għaliex tuża 'SELECT * FROM' bla kundizzjoni fl-aħħar biex tirrepeti s-CTE preċedenti?
    + 14% ħin

F'dan il-każ, konna xxurtjati ħafna li l-Hash Join intgħażel għall-konnessjoni, u mhux Nested Loop, għax allura konna nirċievu mhux pass wieħed biss ta 'CTE Scan, iżda 10K!

ftit dwar CTE ScanHawnhekk irridu niftakru li CTE Scan huwa simili għal Seq Scan - jiġifieri, l-ebda indiċjar, iżda biss tfittxija sħiħa, li tkun teħtieġ 10K x 0.3ms = 3000ms għal ċikli minn cte_max jew 1K x 1.5ms = 1500ms meta looping minn cte_bind!
Fil-fatt, x'ridt tikseb bħala riżultat? Iva, normalment din hija l-mistoqsija li toħroġ x'imkien fil-5 minuta ta 'analiżi ta' mistoqsijiet ta '"tliet sulari".

Ridna noħorġu għal kull par ta 'ċwievet uniku min/max mill-grupp minn key_a.
Mela ejja nużawha għal dan funzjonijiet tat-tieqa:

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
[ħares lejn explic.tensor.ru]

Peress li l-qari tad-dejta fiż-żewġ għażliet jieħu l-istess bejn wieħed u ieħor 4-5ms, allura l-ħin kollu tagħna jiksbu -32% - dan huwa fil-forma pura tiegħu tagħbija mneħħija mis-CPU bażi, jekk tali talba tiġi esegwita spiss biżżejjed.

B'mod ġenerali, m'għandekx ġġiegħel il-bażi "ġġorr it-tond, irrombla l-kwadru."

Sors: www.habr.com

Żid kumment