Antipatterns PostgreSQL: Sabaraha jero liang kelenci? hayu urang ngaliwatan hirarki

Dina sistem ERP kompléks loba éntitas boga sipat hirarkisnalika objék homogén baris asup tangkal hubungan karuhun-turunan - Ieu struktur organisasi perusahaan (sadayana cabang ieu, departemén jeung grup gawé), sarta katalog barang, sarta wewengkon gawé, sarta géografi titik jualan, ...

Nyatana, teu aya wewengkon automation bisnis, dimana moal aya hierarki salaku hasilna. Tapi sanajan anjeun teu dianggo "pikeun bisnis," anjeun masih bisa kalayan gampang sapatemon hubungan hirarkis. Ieu trite, sanajan tangkal kulawarga anjeun atanapi rencana lantai enggon di puseur balanja téh struktur sarua.

Aya seueur cara pikeun nyimpen tangkal sapertos kitu dina DBMS, tapi ayeuna urang bakal difokuskeun ngan ukur hiji pilihan:

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

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

Sareng nalika anjeun ningali ka jero hierarki, éta sabar ngantosan ningali kumaha efektifna cara "naif" anjeun damel sareng struktur sapertos kitu.

Hayu urang nempo masalah has anu timbul, palaksanaan maranéhanana di SQL, sarta coba pikeun ngaronjatkeun kinerja maranéhanana.

#1. Sabaraha jero liang kelenci?

Hayu urang, pikeun kapastian, nampi yén struktur ieu bakal ngagambarkeun subordination departemén dina struktur organisasi: departemén, divisi, séktor, cabang, grup kerja ... - naon nelepon aranjeunna.

Kahiji, hayu urang ngahasilkeun 'tangkal' urang tina 10K elemen

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;

Hayu urang mimitian ku tugas pangbasajanna - milarian sadaya karyawan anu damel dina séktor khusus, atanapi tina segi hierarki - manggihan sakabeh barudak tina hiji titik. Éta ogé bakal hadé pikeun meunangkeun "jero" turunan ... Sadaya ieu bisa jadi perlu, contona, pikeun ngawangun sababaraha jenis. Pilihan kompléks dumasar kana daptar KTP karyawan ieu.

Sagalana bakal rupa lamun aya ngan sababaraha tingkat turunan ieu sarta jumlahna aya dina belasan, tapi lamun aya leuwih ti 5 tingkat, sarta geus aya puluhan turunan, meureun aya masalah. Hayu urang tingali kumaha pilihan milarian turun-tangkal tradisional ditulis (sareng damel). Tapi ke heula, hayu urang nangtukeun titik mana anu paling narik pikeun panalungtikan urang.

Anu pang "jero" tangkal leutik:

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

Anu pang "lega" tangkal leutik:

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

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

Pikeun patarosan ieu kami nganggo anu biasa rekursif JOIN:


Jelas, kalayan modél pamundut ieu jumlah iterations bakal cocog jumlah total turunan (jeung aya sababaraha belasan di antarana), sarta ieu tiasa nyandak sumberdaya cukup signifikan, sarta, salaku hasilna, waktos.

Hayu urang pariksa subtree "panglegana":

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;

[tingali dina explain.tensor.ru]

Saperti nu diharapkeun, urang manggihan sakabeh 30 rékaman. Tapi aranjeunna nyéépkeun 60% tina total waktos ieu - sabab ogé 30 milarian dina indéks. Éta mungkin pikeun ngalakukeun kirang?

Koréksi bulk ku indéks

Naha urang kedah ngadamel pamundut indéks anu misah pikeun unggal titik? Tétéla éta euweuh - urang bisa maca tina indéks dina ngagunakeun sababaraha kenop sakaligus dina hiji panggero kalayan bantuan = ANY(array).

Sarta di unggal grup misalna tina identifiers urang tiasa nyandak sakabeh ID kapanggih dina hambalan saméméhna ku "titik". Hartina, dina unggal léngkah salajengna urang bakal milarian sadaya turunan tina tingkat anu tangtu sakaligus.

Ngan, ieu masalahna, dina pilihan recursive, anjeun teu bisa ngakses sorangan dina query nested, Tapi urang kudu kumaha bae milih ngan naon kapanggih dina tingkat saméméhna ... Tétéla teu mungkin pikeun nyieun query nested pikeun sakabéh Pilihan, tapi pikeun widang husus na mungkin. Jeung widang ieu ogé bisa mangrupa Asép Sunandar Sunarya - nu naon urang kudu make ANY.

Keur disada saeutik gélo, tapi dina diagram sagalana basajan.

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;

[tingali dina explain.tensor.ru]

Sarta di dieu hal pangpentingna henteu malah meunang 1.5 kali dina jangka waktu, sarta yén urang ngurangan panyangga pangsaeutikna, saprak urang ngan boga 5 nelepon kana indéks tinimbang 30!

Hiji bonus tambahan nyaéta kanyataan yén sanggeus unnest final, identifiers bakal tetep maréntahkeun ku "tingkat".

Tanda titik

Pertimbangan salajengna anu bakal ngabantosan ningkatkeun kinerja nyaéta − "daun" teu bisa boga anak, nyaeta, keur maranehna teu kudu kasampak "handap" pisan. Dina rumusan tugas urang, ieu ngandung harti yén lamun urang nuturkeun ranté departemén jeung ngahontal hiji pagawe, teu perlu kasampak leuwih sapanjang cabang ieu.

Hayu urang lebet kana méja urang tambahan boolean-sawah, anu bakal langsung nyarioskeun ka urang naha éntri khusus ieu dina tangkal kami mangrupikeun "titik" - nyaéta, naha éta tiasa ngagaduhan turunan.

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

Hebat! Tétéla ngan saeutik leuwih ti 30% tina sakabeh elemen tangkal boga turunan.

Ayeuna hayu urang ngagunakeun montir rada béda - sambungan kana bagian recursive ngaliwatan LATERAL, anu bakal ngamungkinkeun urang langsung ngaksés widang "tabel" rekursif, sareng nganggo fungsi agrégat kalayan kaayaan nyaring dumasar kana titik pikeun ngirangan set konci:

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;

[tingali dina explain.tensor.ru]

Urang éta bisa ngurangan hiji deui panggero indéks jeung meunang leuwih ti 2 kali dina volume proofread.

#2. Hayu urang balik deui ka akar

Algoritma ieu bakal kapaké upami anjeun kedah ngumpulkeun rékaman pikeun sadaya unsur "naék tangkal", bari nahan inpormasi ngeunaan lambaran sumber mana (sareng sareng indikator naon) nyababkeun éta kalebet dina conto - contona, pikeun ngahasilkeun laporan kasimpulan. kalawan aggregation kana node.

Naon anu di handap ieu kedah dicandak ngan ukur salaku konsép buktina, sabab pamundutna tétéla hésé pisan. Tapi upami éta ngadominasi pangkalan data anjeun, anjeun kedah mikirkeun ngagunakeun téknik anu sami.

Hayu urang mimitian ku sababaraha pernyataan basajan:

• Rékam anu sami tina pangkalan data Hadé pisan mun maca éta sakali.
• Rékam tina pangkalan data Éta langkung éfisién maca dina betsti nyorangan.

Ayeuna hayu urang cobian ngawangun pamundut anu urang peryogikeun.

lengkah 1

Jelas, nalika ngamimitian rekursi (dimana urang bakal tanpa éta!) Urang kedah ngirangan catetan tina daun sorangan dumasar kana set identifier awal:

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

Upami aya anu nganggap aneh yén "set" disimpen salaku senar sareng sanés susunan, teras aya katerangan saderhana pikeun ieu. Aya diwangun-di aggregating "gluing" fungsi pikeun string string_agg, Tapi henteu pikeun arrays. Sanajan manéhna gampang pikeun nerapkeun sorangan.

lengkah 2

Ayeuna urang bakal nampi sakumpulan ID bagian anu kedah dibaca deui. Ampir sok aranjeunna bakal duplikat dina rékaman béda tina set aslina - sangkan ngalakukeunana grup aranjeunna, bari ngajaga inpormasi ngeunaan sumber daun.

Tapi di dieu tilu masalah ngantosan urang:

1. Bagian "subrecursive" tina query teu bisa ngandung fungsi agrégat jeung GROUP BY.
2. A rujukan ka "tabel" recursive teu bisa dina subquery nested.
3. Paménta dina bagian rekursif teu tiasa ngandung CTE.

Untungna, sadaya masalah ieu rada gampang pikeun digarap. Hayu urang mimitian ti tungtungna.

CTE dina bagian rekursif

kawas kieu teu dianggo:

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

Sarta sangkan gawéna, kurung nyieun bédana!

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

Paménta bersarang ngalawan "tabel" rekursif

Hmm... CTE rekursif teu tiasa diaksés dina subquery. Tapi bisa jadi di jero CTE! Sareng pamundut nested parantos tiasa ngaksés CTE ieu!

GROUP BY jero recursion

Teu pikaresepeun, tapi ... Urang boga cara basajan pikeun emulate GROUP ku ngagunakeun DISTINCT ON jeung fungsi jandela!

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

Sareng ieu kumaha jalanna!

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

Ayeuna urang tingali naha ID numerik dirobah jadi téks - ambéh maranéhanana bisa ngagabung babarengan dipisahkeun ku koma!

lengkah 3

Pikeun final kami teu boga nanaon deui:

• urang baca catetan "bagian" dumasar kana susunan ID dikelompokeun
• urang ngabandingkeun bagian dikurangan jeung "susunan" tina lambaran aslina
• "ngalegaan" set-string ngagunakeun 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;

[tingali dina explain.tensor.ru]

sumber: www.habr.com