🥇PostgreSQL Antipatterns: Shimo la sungura lina kina kipi? tupitie uongozi

Katika mifumo ngumu ya ERP vyombo vingi vina asili ya darajawakati vitu vyenye homogeneous vinapoingia mti wa mahusiano ya babu na kizazi - hii ni muundo wa shirika la biashara (matawi haya yote, idara na vikundi vya kazi), na orodha ya bidhaa, na maeneo ya kazi, na jiografia ya pointi za mauzo, ...

Kwa kweli, hakuna maeneo ya automatisering ya biashara, ambapo hakutakuwa na uongozi wowote kama matokeo. Lakini hata kama hufanyi kazi "kwa ajili ya biashara," bado unaweza kukutana kwa urahisi na mahusiano ya hierarchical. Ni trite, hata mti wa familia yako au mpango wa sakafu wa majengo katika kituo cha ununuzi ni muundo sawa.

Kuna njia nyingi za kuhifadhi mti kama huo kwenye DBMS, lakini leo tutazingatia chaguo moja tu:

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

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

Na wakati unachungulia ndani ya kina cha uongozi, inasubiri kwa subira kuona jinsi [katika] njia zako "za ujinga" za kufanya kazi na muundo kama huo zitakuwa.

Wacha tuangalie shida za kawaida zinazotokea, utekelezaji wao katika SQL, na jaribu kuboresha utendaji wao.

#1. Shimo la sungura lina kina kipi?

Hebu, kwa uhakika, tukubali kwamba muundo huu utaonyesha utii wa idara katika muundo wa shirika: idara, mgawanyiko, sekta, matawi, vikundi vya kazi ... - chochote unachowaita.

Kwanza, hebu tutengeneze 'mti' wetu wa vipengele 10K

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;

Wacha tuanze na kazi rahisi zaidi - kutafuta wafanyikazi wote wanaofanya kazi ndani ya sekta fulani, au kwa suala la uongozi - pata watoto wote wa nodi. Pia itakuwa nzuri kupata "kina" cha uzao ... Yote hii inaweza kuwa muhimu, kwa mfano, kujenga aina fulani ya uteuzi tata kulingana na orodha ya vitambulisho vya wafanyikazi hawa.

Kila kitu kitakuwa sawa ikiwa kuna viwango kadhaa tu vya wazao hawa na nambari iko ndani ya dazeni, lakini ikiwa kuna viwango zaidi ya 5, na tayari kuna kadhaa ya vizazi, kunaweza kuwa na shida. Hebu tuangalie jinsi chaguzi za jadi za utafutaji chini ya mti zimeandikwa (na kufanya kazi). Lakini kwanza, hebu tubaini ni nodi zipi zitapendeza zaidi kwa utafiti wetu.

Wengi "ndani" miti ndogo:

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

Wengi "pana" miti ndogo:

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

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

Kwa maswali haya tulitumia kawaida kujirudia JIUNGE:

Ni wazi, na mfano huu wa ombi idadi ya marudio italingana na jumla ya idadi ya vizazi (na kuna kadhaa yao), na hii inaweza kuchukua rasilimali muhimu, na, kama matokeo, wakati.

Wacha tuangalie mti mdogo "mpana" zaidi:

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;

[tazama kwenye explain.tensor.ru]

Kama ilivyotarajiwa, tulipata rekodi zote 30. Lakini walitumia 60% ya muda wote kwenye hili - kwa sababu pia walifanya utafutaji 30 kwenye faharisi. Je, inawezekana kufanya kidogo?

Usahihishaji kwa wingi kwa faharasa

Je! tunahitaji kufanya swala tofauti la faharisi kwa kila nodi? Inageuka kuwa hapana - tunaweza kusoma kutoka kwa index kutumia vitufe kadhaa kwa wakati mmoja katika simu moja na msaada = ANY(array).

Na katika kila kikundi kama hicho cha vitambulisho tunaweza kuchukua vitambulisho vyote vilivyopatikana katika hatua ya awali na "nodi". Hiyo ni, katika kila hatua inayofuata tutafanya tafuta vizazi vyote vya kiwango fulani mara moja.

Tu, hapa ndio shida, katika uteuzi unaorudiwa, huwezi kufikia yenyewe katika swali lililowekwa, lakini tunahitaji kwa namna fulani kuchagua tu kile kilichopatikana kwenye ngazi ya awali ... Inatokea kwamba haiwezekani kufanya swala la kiota kwa uteuzi mzima, lakini kwa shamba lake maalum linawezekana. Na uwanja huu pia unaweza kuwa safu - ambayo ndio tunahitaji kutumia ANY.

Inaonekana ni wazimu kidogo, lakini katika mchoro kila kitu ni rahisi.

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;

[tazama kwenye explain.tensor.ru]

Na hapa jambo muhimu zaidi sio hata kushinda mara 1.5 kwa wakati, na kwamba tulitoa bafa chache, kwa kuwa tuna simu 5 pekee kwenye faharasa badala ya 30!

Bonasi ya ziada ni ukweli kwamba baada ya unnest ya mwisho, vitambulisho vitabaki kuamuru na "ngazi".

Alama ya nodi

Jambo linalofuata ambalo litasaidia kuboresha utendakazi ni − "majani" hayawezi kupata watoto, yaani, kwao hakuna haja ya kuangalia "chini" kabisa. Katika uundaji wa kazi yetu, hii inamaanisha kwamba ikiwa tulifuata mlolongo wa idara na kufikia mfanyakazi, basi hakuna haja ya kuangalia zaidi kwenye tawi hili.

Hebu tuingie kwenye meza yetu ziada boolean-shamba, ambayo itatuambia mara moja ikiwa kiingilio hiki kwenye mti wetu ni "nodi" - ambayo ni, ikiwa inaweza kuwa na kizazi hata kidogo.

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

Kubwa! Inabadilika kuwa zaidi ya 30% tu ya vitu vyote vya miti vina wazao.

Sasa hebu tumia fundi tofauti kidogo - miunganisho kwa sehemu ya kujirudia kupitia LATERAL, ambayo itaturuhusu kupata mara moja sehemu za "meza" ya kujirudia, na kutumia kazi ya jumla na hali ya kuchuja kulingana na nodi ili kupunguza seti ya funguo:

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;

[tazama kwenye explain.tensor.ru]

Tuliweza kupunguza simu moja zaidi ya faharasa na alishinda zaidi ya mara 2 kwa kiasi kusahihisha.

#2. Hebu turudi kwenye mizizi

Kanuni hii itakuwa muhimu ikiwa unahitaji kukusanya rekodi za vipengele vyote "juu ya mti", huku ukihifadhi taarifa kuhusu ni karatasi gani ya chanzo (na kwa viashirio gani) ilisababisha kujumuishwa kwenye sampuli - kwa mfano, ili kutoa ripoti ya muhtasari. na mkusanyiko katika nodi.

Ifuatayo inapaswa kuchukuliwa tu kama uthibitisho wa dhana, kwani ombi linageuka kuwa gumu sana. Lakini ikiwa inatawala hifadhidata yako, unapaswa kufikiria kutumia mbinu zinazofanana.

Wacha tuanze na kauli chache rahisi:

Rekodi sawa kutoka kwa hifadhidata Ni bora kuisoma mara moja tu.
Rekodi kutoka kwa hifadhidata Ni bora zaidi kusoma katika vikundikuliko peke yake.

Sasa hebu tujaribu kuunda ombi tunalohitaji.

Hatua ya 1

Ni wazi, wakati wa kuanzisha kujirudia (tungekuwa wapi bila hiyo!) tutalazimika kutoa rekodi za majani yenyewe kulingana na seti ya vitambulisho vya awali:

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

Ikiwa ilionekana kuwa ya kushangaza kwa mtu kwamba "seti" imehifadhiwa kama kamba na sio safu, basi kuna maelezo rahisi kwa hili. Kuna kazi ya kuunganisha "gluing" iliyojengwa kwa masharti string_agg, lakini sio kwa safu. Ingawa yeye rahisi kutekeleza peke yako.

Hatua ya 2

Sasa tungepata seti ya vitambulisho vya sehemu ambavyo vitahitaji kusomwa zaidi. Karibu kila mara zitanakiliwa katika rekodi tofauti za seti asili - kwa hivyo tungefanya kundi, wakati wa kuhifadhi habari kuhusu majani ya chanzo.

Lakini hapa kuna shida tatu zinazotungojea:

Sehemu ya "subrecursive" ya hoja haiwezi kuwa na vitendaji vilivyojumlishwa GROUP BY.
Marejeleo ya "meza" ya kujirudia hayawezi kuwa katika hoja ndogo iliyoorodheshwa.
Ombi katika sehemu ya kujirudia haliwezi kuwa na CTE.

Kwa bahati nzuri, shida hizi zote ni rahisi kushughulikia. Hebu tuanze kutoka mwisho.

CTE katika sehemu ya kujirudia

Kama hii hakuna kazi:

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

Na hivyo inafanya kazi, mabano hufanya tofauti!

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

Hoja iliyoorodheshwa dhidi ya "meza" inayojirudia

Hmm... CTE inayojirudia haiwezi kufikiwa katika hoja ndogo. Lakini inaweza kuwa ndani ya CTE! Na ombi lililowekwa tayari linaweza kufikia CTE hii!

KUNDI KWA ndani ya kujirudia

Haipendezi, lakini... Tuna njia rahisi ya kuiga GROUP KWA kutumia DISTINCT ON na kazi za dirisha!

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

Na hivi ndivyo inavyofanya kazi!

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

Sasa tunaona kwa nini kitambulisho cha nambari kiligeuzwa kuwa maandishi - ili ziweze kuunganishwa pamoja zikitenganishwa na koma!

Hatua ya 3

Kwa fainali hatuna chochote kilichobaki:

tunasoma rekodi za "sehemu" kulingana na seti ya vitambulisho vya vikundi
tunalinganisha sehemu zilizopunguzwa na "seti" za karatasi za asili
"Panua" safu-seti kwa kutumia 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;

[tazama kwenye explain.tensor.ru]

Chanzo: mapenzi.com

Antipatterns za PostgreSQL: shimo la sungura lina kina kipi? tupitie uongozi

#1. Shimo la sungura lina kina kipi?

Usahihishaji kwa wingi kwa faharasa

Alama ya nodi

#2. Hebu turudi kwenye mizizi

Hatua ya 1

Hatua ya 2

CTE katika sehemu ya kujirudia

Hoja iliyoorodheshwa dhidi ya "meza" inayojirudia

KUNDI KWA ndani ya kujirudia

Hatua ya 3

Kuongeza maoni Kufuta kujibu