Nire lan ildoa dela eta, garatzaile batek eskaera bat idatzi eta pentsatzen duen egoerei aurre egin behar diet.Oinarria adimentsua da, dena kudeatu dezake!Β«
Zenbait kasutan (neurri batean datu-basearen gaitasunen ezjakintasunagatik, neurri batean optimizazio goiztiarengatik), ikuspegi honek "Frankensteins"-en agerpena dakar.
Lehenik eta behin, eskaera baten adibide bat jarriko dut:
-- Π΄Π»Ρ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΠΊΠ»ΡΡΠ΅Π²ΠΎΠΉ ΠΏΠ°ΡΡ Π½Π°Ρ
ΠΎΠ΄ΠΈΠΌ Π°ΡΡΠΎΡΠΈΠΈΡΠΎΠ²Π°Π½Π½ΡΠ΅ Π·Π½Π°ΡΠ΅Π½ΠΈΡ ΠΏΠΎΠ»Π΅ΠΉ
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;
Eskaera baten kalitatea nabarmen ebaluatzeko, sor ditzagun datu multzo arbitrario batzuk:
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);
Hori gertatzen da datuak irakurtzeak denboraren laurdena baino gutxiago behar izan zuen kontsultaren exekuzioa:
Piezaz pieza bananduz
Begira diezaiogun eskaera gertuagotik eta harritu gaitezen:
- Zergatik dago WITH RECURSIVE hemen CTE errekurtsiborik ez badago?
- Zergatik multzokatu min/max balioak CTE bereizi batean, gero jatorrizko laginarekin lotuta badaude?
+% 25 denbora - Zergatik erabili baldintzarik gabeko 'SELECT * FROM' amaieran aurreko CTE errepikatzeko?
+% 14 denbora
Kasu honetan, zorte handia izan genuen Hash Join aukeratzea konexiorako, eta ez Nested Loop, zeren eta CTE Scan pase bakarra ez, 10K baizik!
CTE Scan-ari buruz pixka batHemen hori gogoratu behar dugu CTE Scan Seq Scan-en antzekoa da - hau da, indexaziorik ez, bilaketa osoa baizik, eta horrek eskatuko luke 10K x 0.3 ms = 3000ms cte_max-eko zikloetarako edo 1K x 1.5 ms = 1500ms cte_bind-en begizta egitean!
Egia esan, zer lortu nahi zenuen ondorioz? Bai, normalean hau da "hiru istorioko" kontsultak aztertzeko 5. minutuan sortzen den galdera.
Gako bikote bakarraren irteera nahi genuen min/max taldetik gako_a.
Beraz, erabil dezagun horretarako
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);
Bi aukeretako datuak irakurtzeak gutxi gorabehera 4-5 ms berdinak hartzen dituenez, orduan gure denbora guztia irabazi -32% - hau bere forma garbienean dago karga kendu da oinarrizko CPUtik, eskaera hori nahikoa maiz egiten bada.
Orokorrean, ez duzu oinarria behartu behar "borobila eraman, karratua jaurti".
Iturria: www.habr.com