🥇PostgreSQL Antipatterns: Ki pwofondè twou lapen an genyen? ann ale nan yerachi a

Nan sistèm ERP konplèks anpil antite gen yon nati yerarchizelè objè omojèn nan liy pye bwa nan relasyon zansèt-desandan - sa a se estrikti òganizasyonèl nan antrepriz la (tout branch sa yo, depatman ak gwoup travay), ak katalòg la nan machandiz, ak zòn nan travay, ak jewografi a nan pwen lavant, ...

An reyalite, pa gen okenn zòn automatisation biznis yo, kote pa ta gen okenn yerachi kòm yon rezilta. Men, menm si ou pa travay "pou biznis la," ou ka toujou fasil rankontre relasyon yerarchize. Li banal, menm pyebwa fanmi ou oswa plan etaj lokal nan yon sant komèsyal se menm estrikti a.

Gen plizyè fason pou estoke yon pye bwa konsa nan yon DBMS, men jodi a nou pral konsantre sou yon sèl opsyon:

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

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

Epi pandan w ap gade pwofondè yerachi a, w ap tann avèk pasyans pou w wè ki jan fason "nayif" ou ap travay ak yon estrikti konsa ap efikas.

Ann gade nan pwoblèm tipik ki rive, aplikasyon yo nan SQL, epi eseye amelyore pèfòmans yo.

#1. Ki pwofondè twou lapen an genyen?

Se pou nou, pou defini, aksepte ke estrikti sa a pral reflete sibòdone depatman nan estrikti òganizasyon an: depatman, divizyon, sektè, branch, gwoup travay... - kèlkeswa sa ou rele yo.

Premyèman, an n jenere 'pyebwa' nou an nan 10K eleman

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;

Ann kòmanse ak travay ki pi senp la - jwenn tout anplwaye ki travay nan yon sektè espesifik, oswa an tèm de yerachi - jwenn tout pitit yon ne. Li ta bon tou pou jwenn "pwofondè" desandan an... Tout bagay sa yo ka nesesè, pou egzanp, bati kèk kalite seleksyon konplèks ki baze sou lis ID anplwaye sa yo.

Tout bagay ta byen si gen sèlman yon koup nan nivo desandan sa yo ak nimewo a nan yon douzèn, men si gen plis pase 5 nivo, epi gen deja plizyè douzèn desandan, ka gen pwoblèm. Ann gade nan ki jan opsyon rechèch tradisyonèl desann nan pye bwa yo ekri (ak travay). Men, anvan, ann detèmine ki nœuds ki pral pi enteresan pou rechèch nou an.

Pi plis la "fon" 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}
...

Pi plis la "lajè" 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

Pou demann sa yo nou itilize tipik la rekursif JOIN:

Li evidan, ak modèl demann sa a kantite iterasyon an ap matche ak kantite total desandan yo (e gen plizyè douzèn nan yo), e sa ka pran resous byen enpòtan, epi, kòm yon rezilta, tan.

Ann tcheke sou tree "pi laj" la:

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;

[wè nan eksplike.tensor.ru]

Jan nou espere, nou jwenn tout 30 dosye. Men, yo te pase 60% nan tan total la sou sa a - paske yo menm tou yo te fè 30 rechèch nan endèks la. Èske li posib fè mwens?

Lekti esansyèl pa endèks

Èske nou bezwen fè yon rechèch endèks separe pou chak ne? Li sanble ke pa gen okenn - nou ka li nan endèks la lè l sèvi avèk plizyè kle nan yon fwa nan yon sèl apèl avèk èd la = ANY(array).

Ak nan chak gwoup sa yo nan idantifyan nou ka pran tout ID yo te jwenn nan etap anvan an pa "nœuds". Sa vle di, nan chak pwochen etap nou pral rechèch pou tout pitit pitit yon sèten nivo alafwa.

Sèlman, isit la se pwoblèm nan, nan seleksyon rekursif, ou pa ka jwenn aksè nan tèt li nan yon rechèch enbrike, men nou bezwen yon jan kanmenm chwazi sèlman sa ki te jwenn nan nivo anvan an ... Li sanble ke li enposib fè yon rechèch enbrike pou seleksyon an antye, men pou jaden espesifik li yo li posib. Ak jaden sa a kapab tou yon etalaj - ki se sa nou bezwen sèvi ak ANY.

Li son yon ti kras fou, men nan dyagram nan tout bagay se senp.

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;

[wè nan eksplike.tensor.ru]

Ak isit la bagay ki pi enpòtan se pa menm genyen 1.5 fwa nan tan, e ke nou soustraksyon mwens tanpon, paske nou gen sèlman 5 apèl nan endèks la olye pou yo 30!

Yon bonis adisyonèl se lefèt ke apre final unnest la, idantifyan yo ap rete òdone pa "nivo".

Siy ne

Konsiderasyon kap vini an ki pral ede amelyore pèfòmans se - "fèy" pa ka fè pitit, se sa ki, pou yo pa gen okenn bezwen gade "anba" nan tout. Nan fòmilasyon travay nou an, sa vle di ke si nou te swiv chèn depatman yo ak rive jwenn yon anplwaye, Lè sa a, pa gen okenn bezwen gade pi lwen sou branch sa a.

Ann antre nan tab nou an adisyonèl boolean-jaden, ki pral imedyatman di nou si sa a antre patikilye nan pye bwa nou an se yon "ne" - se sa ki, si wi ou non li ka gen desandan nan tout.

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

Gwo! Li sanble ke sèlman yon ti kras plis pase 30% nan tout eleman pye bwa gen desandan.

Koulye a, kite a sèvi ak yon mekanisyen yon ti kras diferan - koneksyon ak pati nan recursive nan LATERAL, ki pral pèmèt nou jwenn aksè imedyatman nan jaden yo nan "tablo" rekursif la, epi sèvi ak yon fonksyon total ak yon kondisyon filtraj ki baze sou yon ne pou diminye seri kle yo:

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;

[wè nan eksplike.tensor.ru]

Nou te kapab redwi yon lòt apèl endèks ak te genyen plis pase 2 fwa nan volim korije.

#2. Ann tounen nan rasin yo

Algorithm sa a pral itil si ou bezwen kolekte dosye pou tout eleman "moute pye bwa a", pandan w ap kenbe enfòmasyon sou ki fèy sous (ak ki endikatè) ki te lakòz li enkli nan echantiyon an - pou egzanp, jenere yon rapò rezime. ak agrégation nan nœuds.

Sa ki swiv yo ta dwe pran sèlman kòm yon prèv-de-konsèp, depi demann lan vire soti yo dwe trè ankonbran. Men, si li domine baz done ou, ou ta dwe reflechi sou itilize teknik menm jan an.

Ann kòmanse ak yon koup nan deklarasyon senp:

Menm dosye a soti nan baz done a Li pi bon pou w li yon sèl fwa.
Dosye ki soti nan baz done a Li pi efikas pou li an pakètpase pou kont li.

Koulye a, ann eseye konstwi demann nou bezwen an.

Etap 1

Li evidan, lè inisyalize recursion (ki kote nou ta ye san li!) Nou pral oblije soustraksyon dosye yo nan fèy yo tèt yo ki baze sou seri inisyal idantifyan yo:

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

Si yon moun te panse li etranj ke "seri an" te estoke kòm yon fisèl epi yo pa yon etalaj, Lè sa a, gen yon eksplikasyon senp pou sa a. Gen yon fonksyon entegre "kole" pou strings string_agg, men se pa pou etalaj. Malgre ke li fasil pou aplike pou kont ou.

Etap 2

Koulye a, nou ta jwenn yon seri ID seksyon ki pral bezwen li pi lwen. Prèske toujou yo pral kopi nan dosye diferan nan seri orijinal la - se konsa nou ta gwoup yo, pandan y ap konsève enfòmasyon sou fèy sous yo.

Men, isit la twa pwoblèm ap tann nou:

Pati "subrecursive" nan rechèch la pa ka genyen fonksyon total ak GROUP BY.
Yon referans a yon "tab" rekursif pa ka nan yon subquery imbrike.
Yon demann nan pati rekursif la pa ka genyen yon CTE.

Erezman, tout pwoblèm sa yo se byen fasil yo travay alantou. Ann kòmanse depi nan fen.

CTE nan pati repetitif

Isit la se konsa pa gen okenn travay:

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

Se konsa, li travay, parantèz yo fè diferans lan!

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

Rekèt anbrike kont yon "tab" rekursif

Hmm... Yon CTE recursive pa ka jwenn aksè nan yon subquery. Men, li ta ka andedan CTE! Epi yon demann enbrike ka deja jwenn aksè nan CTE sa a!

GROUP BY anndan recursion

Li dezagreyab, men... Nou gen yon fason senp yo imite GROUP BY itilize DISTINCT ON ak fonksyon fenèt!

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

Ak sa a se ki jan li fonksyone!

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

Koulye a, nou wè poukisa ID nimerik la te tounen tèks - pou yo ka mete ansanm separe ak vigil!

Etap 3

Pou final la nou pa gen anyen ki rete:

nou li dosye "seksyon" ki baze sou yon seri idantite gwoupe
nou konpare seksyon soustraksyon yo ak "ansanm" fèy orijinal yo
"agrandi" mete-string lè l sèvi avèk 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;

[wè nan eksplike.tensor.ru]

Sous: www.habr.com

PostgreSQL Antipatterns: Ki pwofondè twou lapen an genyen? ann ale nan yerachi a

#1. Ki pwofondè twou lapen an genyen?

Lekti esansyèl pa endèks

Siy ne

#2. Ann tounen nan rasin yo

Etap 1

Etap 2

CTE nan pati repetitif

Rekèt anbrike kont yon "tab" rekursif

GROUP BY anndan recursion

Etap 3

Add nouvo kòmantè Anile reply