ProHoster > blog > Gweinyddiaeth > Gwrthbatrymau PostgreSQL: CTE x CTE

Gwrthbatrymau PostgreSQL: CTE x CTE

Oherwydd fy llinell waith, mae'n rhaid i mi ddelio Γ’ sefyllfaoedd pan fydd datblygwr yn ysgrifennu cais ac yn meddwl β€œMae'r sylfaen yn smart, gall drin popeth ei hun!Β«

Mewn rhai achosion (yn rhannol oherwydd anwybodaeth o alluoedd y gronfa ddata, yn rhannol o optimeiddio cynamserol), mae'r dull hwn yn arwain at ymddangosiad "Frankensteins".

Yn gyntaf, rhoddaf enghraifft o gais o'r fath:

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

I werthuso ansawdd cais yn sylweddol, gadewch i ni greu set ddata mympwyol:

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

Mae'n troi allan hynny cymerodd darllen y data lai na chwarter yr amser cyflawni ymholiad:

Gwrthbatrymau PostgreSQL: CTE x CTE[edrychwch ar explain.tensor.ru]

Ei dynnu'n ddarnau fesul darn

Gadewch i ni edrych yn agosach ar y cais a chael ein drysu:

  1. Pam fod GYDA AILGYLCHOL yma os nad oes CTEs ailadroddus?
  2. Pam grwpio gwerthoedd min/uchafswm mewn CTE ar wahΓ’n os ydynt wedyn yn cael eu clymu i'r sampl gwreiddiol beth bynnag?
    +25% o amser
  3. Pam defnyddio 'SELECT * From' diamod ar y diwedd i ailadrodd y CTE blaenorol?
    +14% o amser

Yn yr achos hwn, roeddem yn ffodus iawn bod Hash Join wedi'i ddewis ar gyfer y cysylltiad, ac nid Nested Loop, oherwydd wedyn byddem wedi derbyn nid yn unig un tocyn CTE Scan, ond 10K!

ychydig am CTE ScanYma mae'n rhaid i ni gofio hynny Mae CTE Scan yn debyg i Seq Scan β€” hyny yw, dim mynegu, ond chwiliad cyflawn yn unig, a fyddai yn gofyn 10K x 0.3ms = 3000ms ar gyfer cylchoedd gan cte_max neu 1K x 1.5ms = 1500ms wrth ddolennu gan cte_bind!
A dweud y gwir, beth oeddech chi eisiau ei gael o ganlyniad? Ie, fel arfer dyma'r cwestiwn sy'n codi rhywle yn y 5ed munud o ddadansoddi ymholiadau β€œtair stori”.

Roeddem eisiau allbwn ar gyfer pob pΓ’r allwedd unigryw min/uchafswm o grΕ΅p yn Γ΄l bysell_a.
Felly gadewch i ni ei ddefnyddio ar gyfer hyn swyddogaethau ffenestr:

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

Gwrthbatrymau PostgreSQL: CTE x CTE
[edrychwch ar explain.tensor.ru]

Gan fod darllen data yn y ddau opsiwn yn cymryd yr un tua 4-5ms, yna mae ein holl amser yn ennill -32% β€” y mae hwn yn ei ffurf buraf llwyth wedi'i dynnu o'r CPU sylfaen, os gweithredir y cyfryw gais yn ddigon aml.

Yn gyffredinol, ni ddylech orfodi'r sylfaen i "gario'r un crwn, rholio'r un sgwΓ’r."

Ffynhonnell: hab.com

Ychwanegu sylw