PostgreSQL Antipatterns: CTE x CTE

Në përputhje me veprimtarinë, shpesh përballemi me situata ku zhvilluesi shkruan një kërkesë dhe mendon: "database është e mençur, do të përballet me gjithçka vetë!"«

Në disa raste (pjesërisht për shkak të mungesës së njohurive për mundësitë e BD-së, pjesërisht për shkak të optimizimeve të parakohshme) ky qasje çon në krijimin e "frankensteinëve."

Së pari do të jap një shembull të tillë të një kërkese:

-- për çdo çift çelësi gjejmë vlerat e lidhura të fushave
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
)
-- gjejmë min/max vlerat për çdo çelës të parë
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- lidhim sipas çelësit të parë çiftet e çelësit dhe vlerat 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;

Për të vlerësuar cilësinë e kërkesës, le të krijojmë një grup të rastësishëm të dhënash:

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

Duket se vetë leximi i të dhënave zuri më pak se një e katërta e gjithë kohës së ekzekutimit të kërkesës:

[shiko në explain.tensor.ru]

Analizojmë në detaje

Do ta shqyrtojmë kërkesën me vëmendje dhe do të shqetësohemi:

1. Pse është këtu WITH RECURSIVE, nëse nuk ka CTE të përsëritura?
2. Pse të grupohen vlerat min/max në një CTE të veçantë, kur ato përsëri i nënshtrohen grumbullit origjinal?
   +25% kohë
3. Pse përdoret në fund një rikthim i përsëritur nga CTE e mëparshme përmes ‘SELECT * FROM’?
   +14% kohë

Në këtë rast, na ka ecur mirë që u zgjodh Hash Join për bashkimin, jo Nested Loop, pasi përndryshe do të kishim jo një kalim të vetëm CTE Scan, por 10K!

pak për CTE ScanKëtu duhet të kujtojmë se CTE Scan është ekuivalenti i Seq Scan — pra, pa indeksim, vetëm përmes një përzgjedhjeje të plotë, që do të kërkonte 10K x 0.3ms = 3000ms në ciklet për cte_max ose 1K x 1.5ms = 1500ms në ciklet për cte_bind!
Në fakt, çfarë dëshironim të merrnim në rezultat? Ah, zakonisht ky është pikërisht pyetja që na vjen në mendje pas rreth 5 minutash analizimi të kërkesave "tre-katëshe."

Deshëm që për çdo çift të veçantë çelësi të nxjerrim min/max nga grupi sipas key_a.
Pra le ta përdorim këtë funksionet e dritareve:

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

[shiko në explain.tensor.ru]

Meqenëse leximi i të dhënave në të dyja rastet zë afërsisht 4-5ms, gjithë përfitimi ynë në kohë -32% — është thjesht ngarkesa e hequr nga CPU e bazës, nëse një kërkesë e tillë ekzekutohet mjaft shpesh.

Në përfundim, nuk duhet ta detyrojmë bazën të "mbajë rreth" ose "rrotull ethe."

Burimi: habr.com