ProHoster > Π‘Π»ΠΎΠ³ > administratio > Antipatterns PostgreSQL: CTE x CTE

Antipatterns PostgreSQL: CTE x CTE

Ob in linea laboris, condiciones agere debeo cum a elit scribens petitionem et cogitat "Turpis est dolor, ipsum integer possimus!Β«

In quibusdam casibus (partim ex ignorantia facultatum datorum, partim ex optimizationibus praematuris), aditus ad speciem "Frankenstein" ducit.

Primum, exemplum petendi dabo.

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

Ut substantive qualitatem petitionis perpendant, aliquas notitias arbitrarias paro;

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

Evenit ut legere notitia tulit minus quam quarta pars temporis quaesitum supplicium:

Antipatterns PostgreSQL: CTE x CTE[Aspice explain.tensor.ru]

Sumens illud per partes seorsum

Propius petamus instantiam instantiam et haesitabimus:

  1. Cur hic cum recursivo, si non sunt recursivae CTEs?
  2. Cur coetus min/maximi valoris in CTE separato si tunc ad exemplum originalis usquam colligantur?
    +25% time
  3. Cur pure ' SELECT * FROM ' in fine repetendi priorem CTE ?
    +14% time

In hoc casu, valde felix fuimus quod Hash Join nexus electi sunt, et non Loop Nested, quia tunc non unum CTE Scan passum suscepimus, sed 10K!

paulum de CTE ScanHic oportet meminisse CTE Scan est similis Seq Scan - id est, nullum indexing, sed solum pervestigationis completae, quae requireret 10K x 0.3ms = 3000ms ad circuitus per cte_max aut 1K x 1.5ms = 1500ms cum looping ab cte_bind!
Profecto quid uis obtinere? Yeah, plerumque haec est quaestio quae alicubi ascendit in 5th minute analysendi "tres-fabulas" queries.

Volumus ut output pro singulis singularibus clavem par min/max a coetus in key_a.
Ita utamur pro hoc fenestrae munera:

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 PostgreSQL: CTE x CTE
[Aspice explain.tensor.ru]

Cum notitia lectionis in utraque optione idem fit circiter 4-5ms, tunc toto nostro tempore lucro 32-% - hoc est in purissima sua forma basis CPU ab onus remotumsi id saepe satis fiat.

In genere, non debes basem "rotundum portare, quadratum volvere."

Source: www.habr.com