ProHoster > Π‘Π»ΠΎΠ³ > Administrasi > Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

Ing sistem ERP kompleks akeh entitas duwe sifat hirarkisnalika obyek homogen baris ing wit relasi leluhur-turunan - iki minangka struktur organisasi perusahaan (kabeh cabang, departemen lan kelompok kerja iki), lan katalog barang, lan wilayah kerja, lan geografi titik penjualan, ...

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

Nyatane, ora ana area otomatisasi bisnis, sing ora bakal ana hirarki minangka asil. Nanging sanajan sampeyan ora kerja "kanggo bisnis," sampeyan isih bisa gampang nemoni hubungan hierarkis. Iku trite, malah wit kulawarga utawa lantai rencana saka panggonan ing pusat blanja struktur padha.

Ana akeh cara kanggo nyimpen wit kasebut ing DBMS, nanging dina iki kita bakal fokus ing siji pilihan:

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

CREATE INDEX ON hier(pid); -- Π½Π΅ Π·Π°Π±Ρ‹Π²Π°Π΅ΠΌ, Ρ‡Ρ‚ΠΎ FK Π½Π΅ ΠΏΠΎΠ΄Ρ€Π°Π·ΡƒΠΌΠ΅Π²Π°Π΅Ρ‚ автосозданиС индСкса, Π² ΠΎΡ‚Π»ΠΈΡ‡ΠΈΠ΅ ΠΎΡ‚ PK

Lan nalika sampeyan ndeleng jero hierarki, sampeyan kudu sabar ngenteni kepiye cara "naif" sampeyan nggarap struktur kasebut kanthi efektif.

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki
Ayo goleki masalah khas sing muncul, implementasine ing SQL, lan nyoba nambah kinerja.

#1. Sepira jerone bolongan kelinci?

Ayo kita, kanggo definiteness, nampa yen struktur iki bakal nggambarake subordination departemen ing struktur organisasi: departemen, divisi, sektor, cabang, kelompok kerja ... - apa wae sing diarani.
Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

Pisanan, ayo ngasilake 'wit' saka unsur 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;

Ayo diwiwiti kanthi tugas sing paling gampang - nemokake kabeh karyawan sing kerja ing sektor tartamtu, utawa babagan hierarki - golek kabeh anak saka simpul. Iku uga bakal becik kanggo njaluk "ambane" saka turunane ... Kabeh iki bisa uga perlu, contone, kanggo mbangun sawetara jenis pilihan Komplek adhedhasar dhaftar ID karyawan iki.

Kabeh bakal dadi apik yen mung ana sawetara tingkat saka turunane lan nomer ing rolas, nanging yen ana luwih saka 5 tingkat, lan wis ana puluhan turunane, bisa uga ana masalah. Ayo goleki carane opsi telusuran mudhun-the-tree tradisional ditulis (lan bisa digunakake). Nanging pisanan, ayo nemtokake simpul sing paling menarik kanggo riset kita.

Paling akeh "jero" subtree:

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

Paling akeh "lebar" subtree:

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

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

Kanggo pitakonan iki kita nggunakake khas rekursif JOIN:
Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

Temenan, karo model request iki jumlah iterasi bakal cocog karo jumlah turunane (lan ana sawetara rolas mau), lan iki bisa njupuk sumber daya cukup pinunjul, lan, minangka asil, wektu.

Ayo mriksa subtree "paling jembar":

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;

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki
[deleng ing explain.tensor.ru]

Kaya sing dikarepake, kita nemokake kabeh 30 rekaman. Nanging padha nglampahi 60% saka total wektu iki - amarga padha uga nindakake 30 panelusuran ing indeks. Apa bisa ditindakake kurang?

Koreksi massal kanthi indeks

Apa kita kudu nggawe pitakon indeks sing kapisah kanggo saben simpul? Pranyata ora - kita bisa maca saka indeks nggunakake sawetara tombol bebarengan ing siji telpon kanthi pitulung saka = ANY(array).

Lan ing saben klompok pengenal kasebut, kita bisa njupuk kabeh ID sing ditemokake ing langkah sadurunge kanthi "simpul". Sing, ing saben langkah sabanjure kita bakal goleki kabeh turunan saka tingkat tartamtu bebarengan.

Mung, iki masalah, ing pilihan rekursif, sampeyan ora bisa ngakses dhewe ing query nested, nanging kita kudu piye wae milih mung apa sing ditemokake ing tingkat sadurunge ... Pranyata ora bisa nggawe query nested kanggo kabeh pilihan, nanging kanggo lapangan tartamtu iku bisa. Lan lapangan iki uga bisa dadi array - sing kudu digunakake ANY.

Iku muni sethitik edan, nanging ing diagram kabeh iku prasaja.

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

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;

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki
[deleng ing explain.tensor.ru]

Lan ing kene sing paling penting ora malah menang 1.5 kaping ing wektu, lan kita nyuda buffer kurang, awit kita mung duwe 5 telpon kanggo indeks tinimbang 30!

Bonus tambahan iku kasunyatan sing sawise unnest final, pengenal bakal tetep dhawuh dening "tingkat".

Tandha simpul

Pertimbangan sabanjure sing bakal mbantu ningkatake kinerja yaiku βˆ’ "godhong" ora bisa duwe anak, yaiku, kanggo wong-wong mau ora perlu katon "mudhun" kabeh. Ing rumusan tugas kita, iki tegese yen kita ngetutake rantai departemen lan tekan karyawan, mula ora perlu goleki maneh ing cabang iki.

Ayo mlebu ing meja kita tambahan boolean- lapangan, sing bakal langsung ngandhani apa entri tartamtu ing wit kita minangka "simpul" - yaiku, apa bisa duwe keturunan.

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

apik tenan! Pranyata mung luwih saka 30% saka kabeh unsur wit duwe turunan.

Saiki ayo nggunakake mekanik sing rada beda - sambungan menyang bagean rekursif liwat LATERAL, sing bakal ngidini kita langsung ngakses kolom "tabel" rekursif, lan nggunakake fungsi agregat kanthi kondisi nyaring adhedhasar simpul kanggo nyuda set tombol:

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki

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;

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki
[deleng ing explain.tensor.ru]

Kita padha bisa nyuda siji telpon indeks liyane lan menang luwih saka 2 kaping ing volume proofread.

#2. Ayo bali menyang oyod

Algoritma iki bakal migunani yen sampeyan kudu ngumpulake cathetan kanggo kabeh unsur "munggah wit", nalika nahan informasi babagan lembar sumber (lan karo indikator apa) sing nyebabake dilebokake ing sampel - contone, kanggo nggawe laporan ringkesan. kanthi agregasi menyang node.

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki
Apa ing ngisor iki kudu dijupuk mung minangka bukti-konsep, amarga panjaluk kasebut dadi rumit banget. Nanging yen dominasi database sampeyan, sampeyan kudu mikir babagan nggunakake teknik sing padha.

Ayo dadi miwiti karo sawetara statements prasaja:

  • Rekaman sing padha saka database Luwih becik maca sepisan wae.
  • Cathetan saka database Iku luwih efisien kanggo maca ing kumpulantinimbang piyambak.

Saiki ayo nyoba mbangun panjaluk sing dibutuhake.

langkah 1

Temenan, nalika miwiti rekursi (ing ngendi kita bakal tanpa iku!) Kita kudu nyuda cathetan saka godhong kasebut dhewe adhedhasar set pengenal 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
  ...

Yen ketoke aneh kanggo wong sing "set" disimpen minangka senar lan ora Uploaded, banjur ana panjelasan prasaja kanggo iki. Ana fungsi "gluing" agregasi sing dibangun kanggo senar string_agg, nanging ora kanggo susunan. Senajan dheweke gampang kanggo ngleksanakake dhewe.

langkah 2

Saiki kita bakal entuk set ID bagean sing kudu diwaca maneh. Meh mesthi padha bakal diduplikasi ing macem-macem cathetan saka pesawat asli - supaya kita bakal klompok mau, nalika njaga informasi babagan godhong sumber.

Nanging ing kene ana telung masalah sing nunggu kita:

  1. Bagian "subrekursif" saka pitakonan ora bisa ngemot fungsi agregat karo GROUP BY.
  2. A referensi kanggo "tabel" rekursif ora bisa ing subquery nested.
  3. Panjaluk ing bagean rekursif ora bisa ngemot CTE.

Begjanipun, kabeh masalah iki cukup gampang kanggo ngatasi. Ayo miwiti saka pungkasan.

CTE ing bagean rekursif

Kene mangkono ora nyambut gawe:

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

Lan supaya bisa, kurung nggawe prabΓ©dan!

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

Pitakon bersarang marang "tabel" rekursif

Hmm... CTE rekursif ora bisa diakses ing subquery. Nanging bisa uga ana ing CTE! Lan panjalukan nested wis bisa ngakses CTE iki!

GROUP BY nang recursion

Iku ora nyenengake, nanging ... Kita duwe cara prasaja kanggo niru GROUP BY nggunakake DISTINCT ON lan fungsi jendhela!

SELECT
  (rec).pid id
, string_agg(chld::text, ',') chld
FROM
  tree
WHERE
  (rec).pid IS NOT NULL
GROUP BY 1 -- Π½Π΅ Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚!

Lan iki cara kerjane!

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

Saiki kita ngerti sebabe ID numerik diowahi dadi teks - supaya bisa digabungake kanthi dipisahake karo koma!

langkah 3

Kanggo final, kita ora duwe apa-apa:

  • kita maca cathetan "bagean" adhedhasar pesawat saka ID klompok
  • kita mbandhingake bagean sing dikurangi karo "set" saka lembaran asli
  • "expand" set-string nggunakake 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;

Antipattern PostgreSQL: Sepira jerone bolongan kelinci? ayo liwat hirarki
[deleng ing explain.tensor.ru]

Source: www.habr.com

Add a comment