🥇PostgreSQLren aurkako ereduak: zenbateko sakonera du untxi-zuloa? goazen hierarkiatik

ERP sistema konplexuetan entitate askok izaera hierarkikoa duteobjektu homogeneoak lerroan jartzen direnean arbaso-ondorengo harremanen zuhaitza - Hau da enpresaren antolamendu-egitura (adar, sail eta lan-talde horiek guztiak), eta ondasunen katalogoa, eta lan-arloak, eta salmenta-guneen geografia,...

Izan ere, ez dago negozioen automatizazio arloak, non hierarkiarik egongo ez litzatekeen ondorioz. Baina "negoziorako" lan egiten ez baduzu ere, harreman hierarkikoak erraz topa ditzakezu. Apur bat da, nahiz eta zure zuhaitz genealogikoa edo merkataritza-gune bateko lokalen planoa egitura bera izan.

Horrelako zuhaitz bat DBMS batean gordetzeko modu asko daude, baina gaur aukera bakar batean jarriko dugu arreta:

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

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

Eta hierarkiaren sakonera begiratzen ari zaren bitartean, pazientziaz zain dago nola [in]eraginkorra izango den egitura horrekin lan egiteko zure modu “inozoak”.

Ikus ditzagun sortzen diren arazo tipikoak, SQLn inplementatzea, eta saia gaitezen haien errendimendua hobetzen.

#1. Zenbateko sakonera du untxi-zuloa?

Onar dezagun, zehatz-mehatz, egitura horrek erakundearen egituran sailen menpekotasuna islatuko duela: sailak, dibisioak, sektoreak, sukurtsalak, lantaldeak... - deitzen diezun.

Lehenik eta behin, sor dezagun 10K elementuz osatutako gure "zuhaitza".

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;

Has gaitezen zeregin errazenetik - sektore zehatz batean lan egiten duten langile guztiak aurkitzea, edo hierarkiaren arabera - aurkitu nodo baten seme-alaba guztiak. Ondorengoaren “sakontasuna” lortzea ere ondo legoke... Hori guztia beharrezkoa izan daiteke, adibidez, nolabaiteko hautaketa konplexua langile horien NANen zerrendan oinarrituta.

Dena ondo egongo litzateke ondorengo hauen maila pare bat besterik ez badaude eta kopurua dozena baten barruan badago, baina 5 maila baino gehiago badira, eta dagoeneko dozenaka ondorengo badira, arazoak egon daitezke. Ikus dezagun zuhaitzean beherako bilaketa-aukera tradizionalak nola idazten diren (eta funtzionatzen duten). Baina lehenik eta behin, zehaztu dezagun zein nodo izango diren gure ikerketarako interesgarrienak.

Gehien "sakona" azpizuhaitzak:

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

Gehien "zabala" azpizuhaitzak:

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

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

Kontsulta hauetarako ohikoa erabili dugu JOIN errekurtsiboa:

Jakina, eskaera eredu honekin iterazio kopurua ondorengoen guztizkoarekin bat etorriko da (eta hainbat dozenaka daude), eta horrek baliabide nahiko esanguratsuak har ditzake, eta, ondorioz, denbora.

Ikus dezagun azpizuhaitz "zabalena":

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;

[ikusi explain.tensor.ru helbidean]

Espero bezala, 30 erregistro guztiak aurkitu ditugu. Baina denbora osoaren % 60 eman zuten horretan, indizean 30 bilaketa ere egin baitzituzten. Gutxiago egitea posible al da?

Testuen zuzenketa indizearen arabera

Nodo bakoitzeko indize-kontsulta bereizi bat egin behar al dugu? Bihurtzen da ezetz - aurkibidetik irakur dezakegu dei batean hainbat tekla aldi berean erabiliz laguntzarekin = ANY(array).

Eta halako identifikatzaile talde bakoitzean aurreko urratsean aurkitutako ID guztiak "nodoen arabera" har ditzakegu. Hau da, hurrengo urrats bakoitzean egingo dugu maila jakin bateko ondorengo guztiak aldi berean bilatu.

Bakarrik, hona hemen arazoa, hautapen errekurtsiboan, ezin duzu bere burua sartu habiaratutako kontsulta batean, baina nolabait aurreko mailan aurkitutakoa bakarrik hautatu behar dugu... Ematen du ezinezkoa dela hautapen osorako habiaraturiko kontsulta bat egitea, baina bere eremu zehatzerako posible da. Eta eremu hau array bat ere izan daiteke, hau da, erabili behar duguna ANY.

Zoro samarra dirudi, baina diagraman dena sinplea da.

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;

[ikusi explain.tensor.ru helbidean]

Eta hemen garrantzitsuena ere ez da garaiz 1.5 aldiz irabazi, eta buffer gutxiago kendu genuela, indizeari 5 dei baino ez baititugu 30 beharrean!

Hobari gehigarri bat da azken desegitearen ondoren identifikatzaileak "mailen" arabera ordenatuta geratuko direla.

Nodoaren seinalea

Errendimendua hobetzen lagunduko duen hurrengo kontua − da "hostoak" ezin du seme-alabarik izan, hau da, haientzat ez dago batere "behera" begiratu beharrik. Gure zereginaren formulazioan, horrek esan nahi du sailen katea jarraitu bagenu eta langile batengana iritsi bagara, ez dagoela adar honetan gehiago begiratu beharrik.

Sar gaitezen gure mahaian osagarria boolean-eremua, eta horrek berehala esango digu gure zuhaitzeko sarrera jakin hau "nodo" bat den ala ez, hau da, ondorengoak izan ditzakeen.

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

Bikaina! Bihurtzen da zuhaitz-elementu guztien % 30 baino apur bat baino gehiagok ondorengoak dituela.

Orain erabil dezagun mekanika apur bat desberdina - zati errekurtsiborako konexioak LATERAL, "taula" errekurtsiboaren eremuetara berehala sartzeko aukera emango diguna, eta gako multzoa murrizteko nodo batean oinarritutako iragazketa-baldintza duen agregazio-funtzio bat erabiliko dugu:

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;

[ikusi explain.tensor.ru helbidean]

Indize dei bat gehiago murriztu ahal izan dugu eta bolumenean 2 aldiz baino gehiago irabazi zuen zuzenketa.

#2. Itzuli gaitezen sustraietara

Algoritmo hau erabilgarria izango da "zuhaiztian gora" elementu guztien erregistroak bildu behar badituzu, zein iturri-orri (eta zein adierazlerekin) laginean sartzea eragin duen informazioa gordez, adibidez, laburpen-txosten bat sortzeko. nodoetan elkartuz.

Hurrengoa kontzeptu-froga gisa soilik hartu behar da, eskaera oso astuna suertatzen baita. Baina zure datu-basea menderatzen badu, antzeko teknikak erabiltzea pentsatu beharko zenuke.

Has gaitezen adierazpen sinple pare batekin:

Datu-baseko erregistro bera Hobe da behin bakarrik irakurtzea.
Datu-baseko erregistroak Eraginkorragoa da multzoka irakurtzeabakarrik baino.

Orain saia gaitezen behar dugun eskaera eraikitzen.

Urratsera 1

Jakina, errekurtsioa abiarazterakoan (non egongo ginateke hori gabe!) hostoen erregistroak beraiek kendu beharko ditugu hasierako identifikatzaileen multzoan oinarrituta:

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

Norbaiti arraroa iruditu bazaio "multzoa" kate gisa gordeta egotea eta ez array gisa, orduan honen azalpen sinple bat dago. Soketarako "itsatsi" funtzio integratua dago string_agg, baina ez arrayetarako. Berak arren zure kabuz ezartzeko erraza.

Urratsera 2

Orain gehiago irakurri beharreko atal ID multzo bat lortuko genuke. Ia beti jatorrizko multzoko erregistro ezberdinetan bikoiztu egingo dira, hala egingo genuke taldekatu, iturri-hostoei buruzko informazioa gordez.

Baina hemen hiru arazo itxaroten gaituzte:

Kontsultaren zati "azpikurtsiboak" ezin du funtzio agregatu eduki GROUP BY.
"Taula" errekurtsibo baten erreferentzia ezin da habiaratutako azpikontsulta batean egon.
Parte errekurtsiboko eskaera batek ezin du CTErik izan.

Zorionez, arazo hauek guztiak nahiko errazak dira konpontzen. Has gaitezen amaieratik.

CTE zati errekurtsiboan

Horrela ez lanean:

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

Eta horrela funtzionatzen du, parentesiek egiten dute aldea!

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

"Taula" errekurtsibo baten aurka habiatutako kontsulta

Hmm... CTE errekurtsibo bat ezin da sartu azpikontsulta batean. Baina CTE barruan egon liteke! Eta habiaraturiko eskaera bat dagoeneko atzitu daiteke CTE honetara!

GROUP BY errekurtsioaren barruan

Desatsegina da, baina... GROUP BY erabiliz emulatzeko modu erraz bat dugu DISTINCT ON eta leiho funtzioak!

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

Eta horrela funtzionatzen du!

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

Orain ikusten dugu zergatik bihurtu den zenbakizko IDa testua, komaz bereizita elkartu ahal izateko!

Urratsera 3

Finalerako ez zaigu ezer geratzen:

taldekako ID multzo batean oinarritutako “atal” erregistroak irakurtzen ditugu
kendutako atalak jatorrizko orrien “multzoekin” alderatzen ditugu
"zabaldu" multzo-katea erabiliz 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;

[ikusi explain.tensor.ru helbidean]

Iturria: www.habr.com

PostgreSQL Antipatterns: Zenbateko sakonera du untxi-zuloa? goazen hierarkiatik

#1. Zenbateko sakonera du untxi-zuloa?

Testuen zuzenketa indizearen arabera

Nodoaren seinalea

#2. Itzuli gaitezen sustraietara

Urratsera 1

Urratsera 2

CTE zati errekurtsiboan

"Taula" errekurtsibo baten aurka habiatutako kontsulta

GROUP BY errekurtsioaren barruan

Urratsera 3

Gehitu iruzkin berria Idatzi