PostgreSQL-antipatterns: CTE x CTE

Työlinjani vuoksi joudun käsittelemään tilanteita, joissa kehittäjä kirjoittaa pyynnön ja ajattelee "Pohja on älykäs, se selviää kaikesta itse!«

Joissakin tapauksissa (osittain tietokannan ominaisuuksien tietämättömyydestä, osittain ennenaikaisista optimoinneista) tämä lähestymistapa johtaa "Frankensteinien" ilmaantuvuuteen.

Ensin annan esimerkin tällaisesta pyynnöstä:

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

Pyynnön laadun arvioimiseksi perusteellisesti luodaan mielivaltainen tietojoukko:

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

Siitä käy ilmi tietojen lukeminen kesti alle neljänneksen ajasta kyselyn suoritus:

[katso selittää.tensor.ru]

Purkamalla se pala palalta

Katsotaanpa pyyntöä tarkemmin ja ollaan ymmällään:

1. Miksi WITH RECURSIVE on täällä, jos rekursiivisia CTE:itä ei ole?
2. Miksi ryhmitellä min/max-arvot erilliseen CTE:hen, jos ne on joka tapauksessa sidottu alkuperäiseen otokseen?
   +25% aika
3. Miksi käyttää lopuksi ehdotonta 'SELECT * FROM' toistaaksesi edellisen CTE:n?
   +14% aika

Tässä tapauksessa olimme erittäin onnekkaita, että yhteyteen valittiin Hash Join, ei Nested Loop, koska silloin olisimme saaneet ei vain yhden CTE Scan -passin, vaan 10 XNUMX!

vähän CTE ScanistaTässä meidän on muistettava se CTE Scan on samanlainen kuin Seq Scan - eli ei indeksointia, vaan vain täydellinen haku, joka vaatisi 10 kt x 0.3 ms = 3000ms jaksoille cte_max-arvolla tai 1 kt x 1.5 ms = 1500ms kun silmukkaa cte_bind!
Itse asiassa, mitä halusit saada tuloksena? Joo, yleensä tämä on kysymys, joka tulee esiin jossain viidennellä minuutilla "kolmikerroksisia" kyselyitä analysoitaessa.

Halusimme tulostaa jokaiselle ainutlaatuiselle avainparille min/max ryhmästä avaimen_a mukaan.
Joten käytetään sitä tähän ikkunan toiminnot:

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

[katso selittää.tensor.ru]

Koska tietojen lukeminen molemmissa vaihtoehdoissa kestää noin 4-5 ms, niin kaikki aikamme hyötyy -32% - tämä on puhtaimmassa muodossaan kuorma poistettu perussuorittimesta, jos tällainen pyyntö suoritetaan riittävän usein.

Yleensä pohjaa ei pidä pakottaa "kantamaan pyöreää, pyörittämään nelikulmaista".

Lähde: will.com