Antimodelët PostgreSQL: CTE x CTE

Për shkak të natyrës së punës sime, më duhet të përballem me situata kur një zhvillues shkruan një kërkesë dhe mendon "Baza është e zgjuar, do të trajtojë gjithçka vetë!«

Në disa raste (pjesërisht për shkak të mosnjohjes së aftësive të bazës së të dhënave, pjesërisht për shkak të optimizimeve të parakohshme), kjo qasje çon në shfaqjen e "Frankensteinëve".

Së pari, le të jap një shembull të një kërkese të tillë:

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

Për të vlerësuar objektivisht cilësinë e një pyetjeje, le të krijojmë një grup të dhënash të rastësishme:

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

Rezulton se është Leximi i të dhënave mori më pak se një të katërtën e kohës totale ekzekutimi i pyetjes:

[shikoni në shpjegojnë.tensor.ru]

Le ta copëtojmë copë pas cope

Le ta hedhim një vështrim më të afërt në kërkesë dhe të habitemi:

1. Pse është WITH RECURSIVE këtu nëse nuk ka CTE rekursive?
2. Pse të grupohen vlerat min/max në një CTE të veçantë nëse ato janë ende të lidhura me mostrën origjinale?
   +25% kohë
3. Pse të përdoret një rilexim nga CTE-ja e mëparshme në fund nëpërmjet një 'SELECT * FROM' të pakushtëzuar?
   +14% kohë

Në këtë rast, ishim shumë me fat që Hash Join u zgjodh për lidhjen, dhe jo Nested Loop, pasi atëherë nuk do të kishim marrë asnjë kalim CTE Scan, por 10K!

Pak rreth skanimit CTEKëtu duhet të kujtojmë se Skanimi CTE është një analog i Skanimit Seq - domethënë, pa indeksim, por vetëm një numërim i plotë, gjë që do të kërkonte 10K x 0.3ms = 3000ms kur kaloni me biçikletë nëpër cte_max ose 1K x 1.5ms = 1500ms kur ciklizohet nga cte_bind!
Në fakt, çfarë donit të merrnit si rezultat? Po, ky është lloji i pyetjes që zakonisht shfaqet diku rreth minutës së 5-të të analizimit të pyetjeve "trekatëshe".

Ne donim të jepnim rezultate për secilin çift unik çelësash min/maks nga grupi nga key_a.
Pra, le ta përdorim për këtë funksionet e dritares:

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

[shikoni në shpjegojnë.tensor.ru]

Meqenëse leximi i të dhënave në të dyja opsionet zgjat afërsisht 4-5ms, atëherë i gjithë fitimi ynë në kohë -32% - kjo është në formën e saj më të pastër ngarkesa u hoq nga baza e CPU-së, nëse një kërkesë e tillë ekzekutohet mjaftueshëm shpesh.

Në përgjithësi, nuk ka nevojë të detyrosh gjërat bazike për të "veshur gjëra të rrumbullakëta, rrotulluar gjëra katrore".

Burimi: www.habr.com