ProHoster > Блог > Administracija > PostgreSQL antiobrasci: CTE x CTE

PostgreSQL antiobrasci: CTE x CTE

Zbog svog posla, moram da se nosim sa situacijama kada programer napiše zahtev i pomisli “Baza je pametna, sve može sama!«

U nekim slučajevima (djelomično zbog nepoznavanja mogućnosti baze podataka, dijelom zbog preuranjenih optimizacija) ovaj pristup dovodi do pojave “Frankenštajnovih”.

Prvo ću dati primjer takvog zahtjeva:

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

Da bismo suštinski procijenili kvalitet zahtjeva, napravimo neki proizvoljni skup podataka:

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

Ispostavilo se da čitanje podataka je trajalo manje od četvrtine vremena izvršenje upita:

PostgreSQL antiobrasci: CTE x CTE[pogledajte objasni.tensor.ru]

Rastavljam deo po deo

Pogledajmo pobliže zahtjev i budimo zbunjeni:

  1. Zašto je ovdje WITH RECURSIVE ako nema rekurzivnih CTE-ova?
  2. Zašto grupirati min/maksimalne vrijednosti u poseban CTE ako su one ionako vezane za originalni uzorak?
    +25% vremena
  3. Zašto koristiti bezuslovno 'SELECT * FROM' na kraju da ponovite prethodni CTE?
    +14% vremena

U ovom slučaju, imali smo veliku sreću da je za konekciju izabran Hash Join, a ne Nested Loop, jer tada bismo dobili ne samo jedan prolaz CTE skeniranja, već 10K!

malo o CTE skeniranjuOvdje moramo to zapamtiti CTE skeniranje je slično Seq skeniranju - odnosno bez indeksiranja, već samo kompletna pretraga, koja bi zahtijevala 10K x 0.3ms = 3000ms za cikluse po cte_max ili 1K x 1.5ms = 1500ms prilikom petlje pomoću cte_bind!
Zapravo, šta ste željeli dobiti kao rezultat? Da, obično je ovo pitanje koje se pojavljuje negdje u 5. minuti analize upita na "tri priče".

Htjeli smo dati izlaz za svaki jedinstveni par ključeva min/max iz grupe po ključu_a.
Pa hajde da ga iskoristimo za ovo funkcije prozora:

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

PostgreSQL antiobrasci: CTE x CTE
[pogledajte objasni.tensor.ru]

Budući da čitanje podataka u obje opcije traje isto otprilike 4-5ms, onda sve naše vrijeme dobijamo -32% - ovo je u svom najčistijem obliku opterećenje uklonjeno sa osnovnog CPU-a, ako se takav zahtjev izvršava dovoljno često.

Općenito, ne biste trebali prisiljavati bazu da "nosi okrugli, kotrljajte kvadratni".

izvor: www.habr.com

Dodajte komentar