🥇Antipatterns PostgreSQL: Kemm hi fonda t-toqba tal-fenek? ejja ngħaddu mill-ġerarkija

F'sistemi ERP kumplessi ħafna entitajiet għandhom natura ġerarkikameta oġġetti omoġenji jingħaqdu siġra tar-relazzjonijiet antenati-dixxendenti - din hija l-istruttura organizzattiva tal-intrapriża (dawn il-fergħat, dipartimenti u gruppi ta 'ħidma kollha), u l-katalgu ta' oġġetti, u oqsma tax-xogħol, u l-ġeografija tal-punti tal-bejgħ,...

Fil-fatt, m'hemm xejn oqsma ta' awtomazzjoni tan-negozju, fejn ma jkun hemm ebda ġerarkija bħala riżultat. Imma anki jekk ma taħdimx "għan-negozju", xorta tista 'faċilment tiltaqa' ma 'relazzjonijiet ġerarkiċi. Huwa banal, anki s-siġra tal-familja tiegħek jew il-pjan ta 'l-art ta' bini f'ċentru tax-xiri hija l-istess struttura.

Hemm ħafna modi kif taħżen siġra bħal din f'DBMS, iżda llum se niffukaw fuq għażla waħda biss:

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

U waqt li tkun qed tħares lejn il-fond tal-ġerarkija, qed tistenna bil-paċenzja biex tara kemm se jkunu [in]effettivi l-modi “naive” tiegħek ta’ kif taħdem ma’ struttura bħal din.

Ejja nħarsu lejn problemi tipiċi li jinqalgħu, l-implimentazzjoni tagħhom fl-SQL, u nippruvaw ittejbu l-prestazzjoni tagħhom.

#1. Kemm hi fonda t-toqba tal-fenek?

Ejjew, għal definizzjoni definittiva, naċċettaw li din l-istruttura tirrifletti s-subordinazzjoni tad-dipartimenti fl-istruttura tal-organizzazzjoni: dipartimenti, diviżjonijiet, setturi, fergħat, gruppi ta’ ħidma... - tkun xi tkun issejjaħhom.

L-ewwel, ejja niġġeneraw is-'siġra' tagħna ta' 10K elementi

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;

Nibdew bl-aktar biċċa xogħol sempliċi - li nsibu l-impjegati kollha li jaħdmu f'settur speċifiku, jew f'termini ta 'ġerarkija - issib it-tfal kollha ta 'node. Ikun tajjeb ukoll li tikseb il-"profondità" tad-dixxendent... Dan kollu jista 'jkun meħtieġ, pereżempju, biex tinbena xi tip ta' għażla kumplessa bbażata fuq il-lista ta 'IDs ta' dawn l-impjegati.

Kollox ikun tajjeb jekk ikun hemm biss ftit livelli ta 'dawn id-dixxendenti u n-numru huwa fi ħdan tużżana, imma jekk ikun hemm aktar minn 5 livelli, u diġà hemm għexieren ta' dixxendenti, jista 'jkun hemm problemi. Ejja nħarsu lejn kif jinkitbu (u jaħdmu) l-għażliet tradizzjonali ta' tfittxija fl-isfel. Imma l-ewwel, ejja niddeterminaw liema nodi se jkunu l-aktar interessanti għar-riċerka tagħna.

Ħafna "fond" subsiġar:

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}
...

Ħafna "wisa" subsiġar:

...
SELECT
  path[1] id
, count(*)
FROM
  T
GROUP BY
  1
ORDER BY
  2 DESC;

id   | count
------------
5300 |   30
 450 |   28
1239 |   27
1573 |   25

Għal dawn il-mistoqsijiet użajna t-tipiku rikorsiv JOIN:

Ovvjament, b'dan il-mudell ta' talba in-numru ta' iterazzjonijiet se jaqbel man-numru totali ta' dixxendenti (u hemm diversi tużżani minnhom), u dan jista 'jieħu riżorsi pjuttost sinifikanti, u, bħala riżultat, ħin.

Ejja niċċekkjaw is-subsiġra "l-aktar wiesgħa":

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;

[ħares lejn explic.tensor.ru]

Kif mistenni, sibna t-30 rekord. Iżda qattgħu 60% tal-ħin totali fuq dan - għax għamlu wkoll 30 tfittxija fl-indiċi. Huwa possibbli li tagħmel inqas?

Qari tal-provi bl-ingrossa bl-indiċi

Għandna bżonn nagħmlu mistoqsija indiċi separata għal kull node? Jirriżulta li le - nistgħu naqraw mill-indiċi tuża diversi ċwievet f'daqqa f'sejħa waħda bl-għajnuna = ANY(array).

U f'kull grupp bħal dan ta 'identifikaturi nistgħu nieħdu l-IDs kollha misjuba fil-pass preċedenti minn "nodi". Jiġifieri, f'kull pass li jmiss aħna se fittex id-dixxendenti kollha ta’ ċertu livell f’daqqa.

Biss, hawn il-problema, fl-għażla rikorsiva, ma tistax taċċessa ruħha f'mistoqsija nested, iżda għandna bżonn b'xi mod nagħżlu biss dak li nstab fil-livell preċedenti... Jirriżulta li ma tistax tagħmel mistoqsija nested għall-għażla kollha, iżda għall-qasam speċifiku tagħha tista '. U dan il-qasam jista 'jkun ukoll firxa - li huwa dak li għandna bżonn nużaw ANY.

Jidher ftit miġnun, iżda fid-dijagramma kollox huwa sempliċi.

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;

[ħares lejn explic.tensor.ru]

U hawn l-iktar ħaġa importanti hija lanqas irbaħ 1.5 darbiet fil-ħin, u li naqqasna inqas buffers, peress li għandna biss 5 sejħiet għall-indiċi minflok 30!

Bonus addizzjonali huwa l-fatt li wara l-unnest finali, l-identifikaturi jibqgħu ordnati minn "livelli".

Sinjal tan-nodu

Il-kunsiderazzjoni li jmiss li tgħin biex ittejjeb il-prestazzjoni hija - "weraq" ma jistax ikollu tfal, jiġifieri, għalihom m'hemmx għalfejn tħares "l isfel" għal kollox. Fil-formulazzjoni tal-kompitu tagħna, dan ifisser li jekk segwejna l-katina tad-dipartimenti u lħaqna impjegat, allura m'hemmx għalfejn inħarsu aktar tul din il-fergħa.

Ejja nidħlu fit-tabella tagħna addizzjonali boolean-qasam, li immedjatament tgħidilna jekk din id-dħul partikolari fis-siġra tagħna hijiex "node" - jiġifieri, jekk jistax ikollu dixxendenti.

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 мс.

Kbir! Jirriżulta li ftit aktar minn 30% biss tal-elementi kollha tas-siġar għandhom dixxendenti.

Issa ejja nużaw mekkanik kemmxejn differenti - konnessjonijiet mal-parti rikorsiv permezz LATERAL, li se tippermettilna naċċessaw immedjatament l-oqsma tat-"tabella" rikorsiv, u nużaw funzjoni aggregata b'kundizzjoni ta 'filtrazzjoni bbażata fuq node biex tnaqqas is-sett ta' ċwievet:

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;

[ħares lejn explic.tensor.ru]

Konna kapaċi nnaqqsu sejħa indiċi waħda oħra u rebaħ aktar minn 2 darbiet fil-volum proofread.

#2. Ejja mmorru lura għall-għeruq

Dan l-algoritmu jkun utli jekk ikollok bżonn tiġbor rekords għall-elementi kollha "fuq is-siġra", filwaqt li żżomm informazzjoni dwar liema folja sors (u b'liema indikaturi) wassal biex tiġi inkluża fil-kampjun - pereżempju, biex tiġġenera rapport sommarju b'aggregazzjoni f'nodi.

Dak li ġej għandu jittieħed biss bħala prova ta' kunċett, peress li t-talba tirriżulta li hija diffiċli ħafna. Imma jekk tiddomina d-database tiegħek, għandek taħseb dwar l-użu ta 'tekniki simili.

Nibdew bi ftit dikjarazzjonijiet sempliċi:

L-istess rekord mid-database L-aħjar huwa li taqrah darba biss.
Rekords mid-database Huwa aktar effiċjenti li taqra f'lottijietmilli waħdu.

Issa ejja nippruvaw nibnu t-talba li neħtieġu.

Pass 1

Ovvjament, meta inizjalizzaw ir-rikors (fejn inkunu mingħajrha!) Ikollna nnaqqsu r-rekords tal-weraq infushom ibbażati fuq is-sett ta 'identifikaturi inizjali:

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
  ...

Jekk xi ħadd ħaseb li hija stramba li s-"sett" kien maħżun bħala spag u mhux firxa, allura hemm spjegazzjoni sempliċi għal dan. Hemm funzjoni ta '"inkullar" ta' aggregazzjoni mibnija għall-kordi string_agg, iżda mhux għall-arrays. Għalkemm hi faċli biex timplimenta waħdek.

Pass 2

Issa nġibu sett ta 'IDs ta' sezzjoni li jeħtieġ li jinqraw aktar. Kważi dejjem se jkunu duplikati f'rekords differenti tas-sett oriġinali - hekk aħna grupp minnhom, filwaqt li tippreserva l-informazzjoni dwar is-sors weraq.

Imma hawn tliet problemi jistennewna:

Il-parti "subrekursive" tal-mistoqsija ma jistax ikun fiha funzjonijiet aggregati ma GROUP BY.
Referenza għal "tabella" rikorsiv ma tistax tkun f'subquery imnaqqsa.
Talba fil-parti rikorsiva ma jistax ikun fiha CTE.

Fortunatament, dawn il-problemi kollha huma pjuttost faċli biex jaħdmu madwar. Nibdew mill-aħħar.

CTE f'parti rikorsiva

Hawn hekk ebda xogħol:

WITH RECURSIVE tree AS (
  ...
UNION ALL
  WITH T (...)
  SELECT ...
)

U għalhekk jaħdem, il-parentesi jagħmlu d-differenza!

WITH RECURSIVE tree AS (
  ...
UNION ALL
  (
    WITH T (...)
    SELECT ...
  )
)

Mistoqsija mdaħħla ma' "tabella" rikorsiv

Hmm... CTE rikorsiv ma jistax jiġi aċċessat f'subquery. Iżda jista 'jkun ġewwa CTE! U talba nested diġà tista' taċċessa dan is-CTE!

GRUPP BY ġewwa rikorsi

Huwa spjaċevoli, iżda... Għandna mod sempliċi biex nimitaw GROUP BY jużaw DISTINCT ON u l-funzjonijiet tat-tieqa!

SELECT
  (rec).pid id
, string_agg(chld::text, ',') chld
FROM
  tree
WHERE
  (rec).pid IS NOT NULL
GROUP BY 1 -- не работает!

U hekk jaħdem!

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

Issa naraw għaliex l-ID numeriku nbidel f'test - sabiex ikunu jistgħu jingħaqdu flimkien separati b'virgoli!

Pass 3

Għall-finali ma fadal xejn:

naqraw rekords ta’ “sezzjoni” bbażati fuq sett ta’ IDs raggruppati
aħna nqabblu s-sezzjonijiet imnaqqsa mas- "settijiet" tal-folji oriġinali
"tespandi" is-sett-string bl-użu 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;

[ħares lejn explic.tensor.ru]

Sors: www.habr.com

PostgreSQL Antipatterns: Kemm hi fonda t-toqba tal-fenek? ejja ngħaddu mill-ġerarkija

#1. Kemm hi fonda t-toqba tal-fenek?

Qari tal-provi bl-ingrossa bl-indiċi

Sinjal tan-nodu

#2. Ejja mmorru lura għall-għeruq

Pass 1

Pass 2

CTE f'parti rikorsiva

Mistoqsija mdaħħla ma' "tabella" rikorsiv

GRUPP BY ġewwa rikorsi

Pass 3

Żid kumment Ikkanċella risposta