Yn komplekse ERP-systemen in protte entiteiten hawwe in hiërargyske aarddoe't homogene objekten linen yn beam fan foarâlden-ôfstammelingen relaasjes - dit is de organisaasjestruktuer fan 'e ûndernimming (al dizze tûken, ôfdielingen en wurkgroepen), en de katalogus fan guod, en wurkgebieten, en de geografy fan ferkeappunten, ...
Yn feite is der gjinien , dêr't der gjin hierargy as gefolch fan wêze soe. Mar sels as jo net "foar it bedriuw" wurkje, kinne jo noch maklik hiërargyske relaasjes tsjinkomme. It is trite, sels jo stambeam of flierplan fan pânen yn in winkelsintrum is deselde struktuer.
D'r binne in protte manieren om sa'n beam yn in DBMS op te slaan, mar hjoed sille wy ús rjochtsje op mar ien opsje:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
En wylst jo yn 'e djipten fan' e hiërargy sjogge, wachtet it geduldich om te sjen hoe [in] effektyf jo "naïve" manieren fan wurkje mei sa'n struktuer sille wêze.
Litte wy nei typyske problemen sjen dy't ûntsteane, har ymplemintaasje yn SQL, en besykje har prestaasjes te ferbetterjen.
#1. Hoe djip is it konijngat?
Lit ús foar de definityfheid akseptearje dat dizze struktuer de ûndergeskiktheid fan ôfdielingen yn 'e struktuer fan 'e organisaasje wjerspegelje sil: ôfdielingen, ôfdielingen, sektoaren, tûken, wurkgroepen ... - hoe't jo se ek neame.
Litte wy earst ús 'beam' generearje fan 10K eleminten
INSERT INTO hier
WITH RECURSIVE T AS (
SELECT
1::integer id
, '{1}'::integer[] pids
UNION ALL
SELECT
id + 1
, pids[1:(random() * array_length(pids, 1))::integer] || (id + 1)
FROM
T
WHERE
id < 10000
)
SELECT
pids[array_length(pids, 1)] id
, pids[array_length(pids, 1) - 1] pid
FROM
T;Litte wy begjinne mei de ienfâldichste taak - alle meiwurkers te finen dy't wurkje binnen in spesifike sektor, of yn termen fan hiërargy - fine alle bern fan in knooppunt. It soe ek moai wêze om de "djipte" fan 'e neiteam te krijen ... Dit alles kin nedich wêze, bygelyks om in soarte fan te bouwen .
Alles soe goed wêze as d'r mar in pear nivo's binne fan dizze neiteam en it oantal is binnen in tsiental, mar as d'r mear as 5 nivo's binne, en d'r binne al tsientallen neikommelingen, kinne d'r problemen wêze. Litte wy sjen nei hoe't tradisjonele sykopsjes foar down-the-beam wurde skreaun (en wurkje). Mar earst litte wy bepale hokker knooppunten it meast ynteressant sille wêze foar ús ûndersyk.
It meast "djip" subtrees:
WITH RECURSIVE T AS (
SELECT
id
, pid
, ARRAY[id] path
FROM
hier
WHERE
pid IS NULL
UNION ALL
SELECT
hier.id
, hier.pid
, T.path || hier.id
FROM
T
JOIN
hier
ON hier.pid = T.id
)
TABLE T ORDER BY array_length(path, 1) DESC; id | pid | path
---------------------------------------------
7624 | 7623 | {7615,7620,7621,7622,7623,7624}
4995 | 4994 | {4983,4985,4988,4993,4994,4995}
4991 | 4990 | {4983,4985,4988,4989,4990,4991}
...It meast "wiid" subtrees:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
Foar dizze fragen brûkten wy de typyske rekursive JOIN:
Fansels, mei dit fersyk model it oantal iteraasjes sil oerienkomme mei it totale oantal neikommelingen (en der binne ferskate tsientallen fan harren), en dit kin nimme frij wichtige middels, en, as gefolch, tiid.
Litte wy kontrolearje op 'e "breedste" subtree:
WITH RECURSIVE T AS (
SELECT
id
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
hier.id
FROM
T
JOIN
hier
ON hier.pid = T.id
)
TABLE T;
Lykas ferwachte fûnen wy alle 30 records. Mar se hawwe hjir 60% fan 'e totale tiid oan bestege - om't se ek 30 sykopdrachten diene yn' e yndeks. Is it mooglik om minder te dwaan?
Bulk korrektyflêzen troch yndeks
Moatte wy in aparte yndeksfraach meitsje foar elke knooppunt? It docht bliken dat nee - kinne wy lêze út de yndeks mei help fan ferskate kaaien tagelyk yn ien oprop troch = ANY(array).
En yn elke sa'n groep identifiers kinne wy alle ID's nimme dy't yn 'e foarige stap fûn binne troch "knooppunten". Dat is, by elke folgjende stap sille wy sykje alle neikommelingen fan in bepaald nivo tagelyk.
Allinne, hjir is it probleem, yn rekursive seleksje kinne jo gjin tagong krije ta himsels yn in nestede query. En dit fjild kin ek in array wêze - dat is wat wy moatte brûke ANY.
It klinkt in bytsje gek, mar yn it diagram is alles ienfâldich.
WITH RECURSIVE T AS (
SELECT
ARRAY[id] id$
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
ARRAY(
SELECT
id
FROM
hier
WHERE
pid = ANY(T.id$)
) id$
FROM
T
WHERE
coalesce(id$, '{}') <> '{}' -- условие выхода из цикла - пустой массив
)
SELECT
unnest(id$) id
FROM
T;
En hjir is it wichtichste ding net iens win 1.5 kear yn 'e tiid, en dat wy minder buffers lutsen, sûnt wy hawwe mar 5 oproppen nei de yndeks ynstee fan 30!
In ekstra bonus is it feit dat nei de lêste unnest, de identifiers sille bliuwe oardere troch "nivo's".
Knooppunt teken
De folgjende konsideraasje dy't sil helpe ferbetterjen prestaasjes is - "blêden" kinne gjin bern krije, dat is, foar harren is d'r hielendal net nedich om "del" te sjen. Yn de formulearring fan ús taak betsjut dit dat as wy de keten fan ôfdielingen folge hawwe en in meiwurker berikke, dan hoecht net fierder te sykjen by dizze branch.
Lit ús yngean yn ús tabel oanfoljend boolean-fjild, dy't ús daliks fertelle sil oft dizze bepaalde yngong yn ús beam in "knooppunt" is - dat is, oft it überhaupt neikommelingen hawwe kin.
ALTER TABLE hier
ADD COLUMN branch boolean;
UPDATE
hier T
SET
branch = TRUE
WHERE
EXISTS(
SELECT
NULL
FROM
hier
WHERE
pid = T.id
LIMIT 1
);
-- Запрос успешно выполнен: 3033 строк изменено за 42 мс.Grut! It docht bliken dat mar in bytsje mear as 30% fan alle beameleminten neikommelingen hawwe.
Litte wy no in wat oare monteur brûke - ferbiningen mei it rekursive diel troch LATERAL.
WITH RECURSIVE T AS (
SELECT
array_agg(id) id$
, array_agg(id) FILTER(WHERE branch) ns$
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
X.*
FROM
T
JOIN LATERAL (
SELECT
array_agg(id) id$
, array_agg(id) FILTER(WHERE branch) ns$
FROM
hier
WHERE
pid = ANY(T.ns$)
) X
ON coalesce(T.ns$, '{}') <> '{}'
)
SELECT
unnest(id$) id
FROM
T;
Wy wienen by steat om te ferminderjen ien mear yndeks oprop en wûn mear as 2 kear yn folume korreksje.
#2. Litte wy werom nei de woartels
Dit algoritme sil nuttich wêze as jo records moatte sammelje foar alle eleminten "op 'e beam", wylst jo ynformaasje behâlde oer hokker boarneblêd (en mei hokker yndikatoaren) feroarsake hat dat it opnommen is yn 'e stekproef - bygelyks om in gearfettingrapport te generearjen mei aggregaasje yn knopen.
It folgjende moat allinich as in proof-of-concept nommen wurde, om't it fersyk tige omslachtig blykt te wêzen. Mar as it jo databank dominearret, moatte jo tinke oer it brûken fan ferlykbere techniken.
Litte wy begjinne mei in pear ienfâldige útspraken:
- Itselde rekord út de databank It is it bêste om it mar ien kear te lêzen.
- Records út de databank It is effisjinter om te lêzen yn batchesas allinne.
Litte wy no besykje it fersyk te konstruearjen dat wy nedich binne.
stap 1
Fansels, by it initialisearjen fan rekursje (wêr soene wy sûnder wêze!) sille wy de records fan 'e blêden sels moatte subtractearje op basis fan' e set fan inisjele identifiers:
WITH RECURSIVE tree AS (
SELECT
rec -- это цельная запись таблицы
, id::text chld -- это "набор" приведших сюда исходных листьев
FROM
hier rec
WHERE
id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
...
As it foar ien frjemd like dat de "set" wurdt opslein as in snaar en net in array, dan is d'r in ienfâldige ferklearring foar. D'r is in ynboude aggregearjende "lijm" funksje foar snaren string_agg, mar net foar arrays. Hoewol't sy .
stap 2
No soene wy in set seksje-ID's krije dy't fierder moatte wurde lêzen. Hast altyd sille se duplikearre wurde yn ferskate records fan 'e orizjinele set - sa soene wy groep harren, wylst it behâld fan ynformaasje oer de boarneblêden.
Mar hjir wachtsje ús trije problemen:
- It "subrekursive" diel fan 'e query kin gjin aggregaatfunksjes befetsje mei
GROUP BY. - In ferwizing nei in rekursive "tabel" kin net wêze yn in geneste subquery.
- In fersyk yn it rekursive diel kin gjin CTE befetsje.
Gelokkich binne al dizze problemen frij maklik om te wurkjen. Litte wy begjinne fan 'e ein.
CTE yn rekursyf diel
Hjir sa net wurkje:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)En sa wurket it, de heakjes meitsje it ferskil!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)Neste query tsjin in rekursive "tabel"
Hmm... In rekursive CTE kin net tagonklik wurde yn in subquery. Mar it kin binnen CTE wêze! En in geneste fersyk kin al tagong krije ta dizze CTE!
GROUP BY binnen rekursion
It is onaangenaam, mar ... Wy hawwe in ienfâldige manier te emulate GROUP BY brûkend DISTINCT ON en finster funksjes!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!En dit is hoe't it wurket!
SELECT DISTINCT ON((rec).pid)
(rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
tree
WHERE
(rec).pid IS NOT NULLNo sjogge wy wêrom't de numerike ID waard omset yn tekst - sadat se kinne wurde gearfoege skieden troch komma's!
stap 3
Foar de finale hawwe wy neat oer:
- wy lêze "seksje" records basearre op in set fan groepearre IDs
- wy fergelykje de subtrahearre seksjes mei de "sets" fan 'e orizjinele blêden
- "wreidzje" de set-string mei
unnest(string_to_array(chld, ',')::integer[])
WITH RECURSIVE tree AS (
SELECT
rec
, id::text chld
FROM
hier rec
WHERE
id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
(
WITH prnt AS (
SELECT DISTINCT ON((rec).pid)
(rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
)
, nodes AS (
SELECT
rec
FROM
hier rec
WHERE
id = ANY(ARRAY(
SELECT
id
FROM
prnt
))
)
SELECT
nodes.rec
, prnt.chld
FROM
prnt
JOIN
nodes
ON (nodes.rec).id = prnt.id
)
)
SELECT
unnest(string_to_array(chld, ',')::integer[]) leaf
, (rec).*
FROM
tree;
Boarne: www.habr.com