Zbog prirode svog posla, moram se nositi sa situacijama kada programer napiše zahtjev i pomisli “Baza je pametna, sve može sama!«
U nekim slučajevima (dijelom zbog nepoznavanja mogućnosti baze podataka, dijelom zbog preuranjenih optimizacija) ovakav pristup dovodi do pojave “Frankensteina”.
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;
Kako bismo suštinski procijenili kvalitetu zahtjeva, stvorimo proizvoljan 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 trajalo je manje od četvrtine vremena izvođenje upita:
Rastavljajući dio po dio
Pogledajmo pobliže zahtjev i budimo zbunjeni:
- Zašto je ovdje WITH RECURSIVE ako nema rekurzivnih CTE-ova?
- Zašto grupirati minimalne/maksimalne vrijednosti u zasebnom CTE-u ako su ionako vezane za izvorni uzorak?
+25% vremena - Zašto koristiti bezuvjetni 'SELECT * FROM' na kraju za ponavljanje prethodnog CTE-a?
+14% vremena
U ovom slučaju, imali smo veliku sreću što je za vezu odabran Hash Join, a ne Nested Loop, jer tada bismo dobili ne samo jedan CTE Scan pass, već 10K!
malo o CTE skeniranjuOvdje se moramo toga sjetiti CTE skeniranje je slično Seq skeniranju - odnosno, nema indeksiranja, već samo cjelovita pretraga, koja bi zahtijevala 10K x 0.3 ms = 3000ms za cikluse prema cte_max ili 1K x 1.5 ms = 1500ms kod petlje pomoću cte_bind!
Zapravo, što ste željeli dobiti kao rezultat? Da, obično je ovo pitanje koje se pojavi negdje u 5. minuti analize "trokatnih" upita.
Htjeli smo ispisati za svaki jedinstveni par ključeva min/max iz grupe po ključu_a.
Pa iskoristimo ga za ovo
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);
Budući da čitanje podataka u obje opcije traje jednako otprilike 4-5 ms, tada smo dobili sav naš vremenski dobitak -32% - ovo je u svom najčišćem obliku opterećenje uklonjeno s osnovnog procesora, ako se takav zahtjev izvršava dovoljno često.
Općenito, ne biste trebali prisiljavati bazu da "nosi okruglu, kotrlja četvrtastu".
Izvor: www.habr.com