ProHoster > Blog > Uprava > Protivzorci PostgreSQL: CTE x CTE

Protivzorci PostgreSQL: CTE x CTE

Zaradi svojega dela se moram soočati s situacijami, ko razvijalec napiše zahtevo in si misli »Podlaga je pametna, vse zmore sama!«

V nekaterih primerih (deloma zaradi nepoznavanja zmožnosti baze podatkov, deloma zaradi prezgodnjih optimizacij) ta pristop vodi do pojava "frankensteinov".

Najprej bom dal primer takšne zahteve:

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

Za vsebinsko ovrednotenje kakovosti zahteve ustvarimo poljubni niz podatkov:

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

Izkazalo se je, da branje podatkov je trajalo manj kot četrtino časa izvedba poizvedbe:

Protivzorci PostgreSQL: CTE x CTE[ogled na expand.tensor.ru]

Razstavljanje kos za kosom

Oglejmo si prošnjo podrobneje in se začudimo:

  1. Zakaj je tukaj WITH RECURSIVE, če ni rekurzivnih CTE?
  2. Zakaj združevati najmanjše/maksimalne vrednosti v ločenem CTE, če so potem tako ali tako povezane z izvirnim vzorcem?
    +25% časa
  3. Zakaj uporabiti brezpogojno 'SELECT * FROM' na koncu za ponovitev prejšnjega CTE?
    +14% časa

V tem primeru smo imeli veliko srečo, da je bil za povezavo izbran Hash Join in ne Nested Loop, ker potem ne bi prejeli samo enega prehoda CTE Scan, ampak 10K!

nekaj o CTE ScanTukaj se moramo tega spomniti CTE Scan je podoben Seq Scan - torej brez indeksiranja, ampak le popolno iskanje, ki bi zahtevalo 10K x 0.3 ms = 3000ms za cikle po cte_max ali 1K x 1.5 ms = 1500ms pri zanki s cte_bind!
Pravzaprav, kaj ste želeli dobiti kot rezultat? Ja, običajno je to vprašanje, ki se pojavi nekje v 5. minuti analize "trinadstropnih" poizvedb.

Želeli smo izpisati za vsak edinstven par ključev min/max iz skupine po ključu_a.
Zato ga uporabimo za to okenske funkcije:

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

Protivzorci PostgreSQL: CTE x CTE
[ogled na expand.tensor.ru]

Ker branje podatkov v obeh možnostih traja enako približno 4-5 ms, potem ves čas pridobimo -32% - to je v najčistejši obliki obremenitev odstranjena iz osnovnega procesorja, če se taka zahteva izvaja dovolj pogosto.

Na splošno ne smete siliti podstavka, da "nosi okroglega, kotali kvadratnega."

Vir: www.habr.com

Dodaj komentar