ProHoster > Blog > Rianachd > Antipatterns PostgreSQL: CTE x CTE

Antipatterns PostgreSQL: CTE x CTE

Mar thoradh air an loidhne obrach agam, feumaidh mi dèiligeadh ri suidheachaidhean nuair a bhios leasaiche a’ sgrìobhadh iarrtas agus a’ smaoineachadh “Tha am bonn spaideil, is urrainn dha a h-uile càil a làimhseachadh fhèin!«

Ann an cuid de chùisean (gu ìre air sgàth aineolas air comasan an stòr-dàta, gu ìre bho ro-luath optimizations), dòigh-obrach seo a 'leantainn gu coltas "Frankensteins".

An toiseach, bheir mi eisimpleir de leithid de dh’ iarrtas:

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

Gus measadh susbainteach a dhèanamh air càileachd iarrtas, cruthaichidh sinn seata dàta neo-riaghailteach:

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

Tha e a 'tionndadh a-mach sin thug leughadh an dàta nas lugha na cairteal den ùine cur an gnìomh ceiste:

Antipatterns PostgreSQL: CTE x CTE[sealladh aig explain.tensor.ru]

A thoirt às a chèile pìos air pìos

Bheir sinn sùil nas mionaidiche air an iarrtas agus bidh sinn fo imcheist:

  1. Carson a tha LE RECURSIVE an seo mura h-eil CTEn ath-chuairteachaidh ann?
  2. Carson a tha buidhnean min / max a’ cur luach ann an CTE air leth ma tha iad an uairsin ceangailte ris an t-sampall tùsail co-dhiù?
    +25% ùine
  3. Carson a chleachdas tu ‘SELECT * FROM’ gun chumhachan aig deireadh an CTE roimhe seo?
    +14% ùine

Anns a ’chùis seo, bha sinn gu math fortanach gun deach Hash Join a thaghadh airson a’ cheangail, agus chan e Nested Loop, oir an uairsin bhiodh sinn air chan e a-mhàin aon chead CTE Scan fhaighinn, ach 10K!

Beagan fiosrachaidh mu CTE ScanAn seo feumaidh sinn cuimhneachadh air sin Tha CTE Scan coltach ri Seq Scan - is e sin, gun chlàr-amais, ach a-mhàin sgrùdadh coileanta, a dh’ fheumadh 10K x 0.3ms = 3000ms airson cuairtean le cte_max no 1K x 1.5ms = 1500ms nuair a lùbadh tu le cte_bind!
Gu fìrinneach, dè a bha thu airson fhaighinn mar thoradh air an sin? Yeah, mar as trice is e seo a’ cheist a thig am bàrr am badeigin anns a’ 5mh mionaid de bhith a’ sgrùdadh cheistean “trì-sgeulachd”.

Bha sinn airson toradh a chuir a-mach airson gach prìomh phaidhir sònraichte min/max bhon bhuidheann le key_a.
Mar sin cleachd sinn e airson seo gnìomhan uinneig:

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
[sealladh aig explain.tensor.ru]

Leis gu bheil leughadh dàta anns an dà roghainn a’ toirt an aon rud timcheall air 4-5ms, an uairsin bidh an ùine againn a’ faighinn buannachd -32% - tha seo anns a chruth fhìor-ghlan luchdaich air falbh bhon CPU bunaiteach, ma thèid an leithid de dh'iarrtas a chur gu bàs tric gu leòr.

San fharsaingeachd, cha bu chòir dhut toirt air a’ bhunait “an tè cruinn a chaitheamh, am fear ceàrnagach a roiligeadh.”

Source: www.habr.com

Cuir beachd ann