🥇PostgreSQL Antipatterns: Wéi déif ass d'Kanéngchenhol? loosst eis duerch d'Hierarchie goen

A komplexen ERP Systemer vill Entitéite hunn eng hierarchesch Naturwann homogen Objeten opgestallt sinn Bam vun Virfueren-Nokommen Relatiounen - dëst ass d'Organisatiounsstruktur vun der Entreprise (all dës Filialen, Departementer an Aarbechtsgruppen), an de Katalog vu Wueren, a Beräicher vun der Aarbecht, an d'Geographie vu Verkafspunkten, ...

Tatsächlech gëtt et keen Geschäftsautomatisatiounsberäicher, wou et dowéinst keng Hierarchie géif ginn. Awer och wann Dir net "fir d'Geschäft" schafft, kënnt Dir nach ëmmer einfach hierarchesch Bezéiungen treffen. Et ass trite, och Äre Stammbaum oder de Buedemplang vun de Raimlechkeeten an engem Akafszentrum ass déiselwecht Struktur.

Et gi vill Weeër fir sou e Bam an engem DBMS ze späicheren, awer haut konzentréiere mir eis op nëmmen eng Optioun:

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

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

A wärend Dir an d'Tiefe vun der Hierarchie kuckt, waart et geduldig ze gesinn wéi [in] effektiv Är "naiv" Weeër fir mat esou enger Struktur ze schaffen.

Loosst eis déi typesch Probleemer kucken, déi entstinn, hir Ëmsetzung an SQL, a probéieren hir Leeschtung ze verbesseren.

#1. Wéi déif ass den Hues Lach?

Loosst eis, fir d'Definiteness, akzeptéieren datt dës Struktur d'Ënneruerdnung vun den Departementer an der Struktur vun der Organisatioun reflektéiert: Departementer, Divisiounen, Secteuren, Filialen, Aarbechtsgruppen ... - wat Dir se och nennt.

Als éischt, loosst eis eise "Bam" vun 10K Elementer generéieren

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;

Loosst eis mat der einfachster Aufgab ufänken - all Mataarbechter ze fannen déi an engem spezifesche Secteur schaffen, oder a punkto Hierarchie - fannen all Kanner vun engem Node. Et wier och flott fir d'"Déift" vum Nokommen ze kréien ... All dat kann néideg sinn, zum Beispill, eng Aart vu komplex Auswiel baséiert op der Lëscht vun IDen vun dëse Mataarbechter.

Alles wier gutt wann et nëmmen e puer Niveauen vun dësen Nokommen sinn an d'Zuel ass bannent enger Dosen, awer wann et méi wéi 5 Niveauen sinn, an et gi schonn Dosende vun Nokommen, kënne Problemer ginn. Loosst eis kucken wéi traditionell Down-the-Bam Sichoptioune geschriwwe ginn (a funktionnéieren). Awer als éischt, loosst eis bestëmmen wéi eng Noden déi interessantst fir eis Fuerschung sinn.

Déi meescht "déif" 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}
...

Déi meescht "breet" 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

Fir dës Ufroe hu mir déi typesch benotzt rekursiv JOIN:

Natierlech, mat dësem Ufro Modell d'Zuel vun Iteratiounen wäert d'selwecht sinn wéi d'Gesamtzuel vun den Nokommen (an et ginn e puer Dosen vun hinnen), an dëst kann relativ bedeitendst Ressourcen huelen, an, als Resultat, Zäit.

Loosst eis op de "breetste" Ënnerbaum kucken:

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;

[kuckt op explain.tensor.ru]

Wéi erwaart hu mir all 30 Placke fonnt. Awer si hunn 60% vun der Gesamtzäit dofir verbruecht - well se och 30 Recherchen am Index gemaach hunn. Ass et méiglech manner ze maachen?

Bulk Korrektur duerch Index

Musse mir eng separat Indexquery fir all Node maachen? Et stellt sech eraus datt nee - mir kënnen aus dem Index liesen benotzt e puer Schlësselen gläichzäiteg an engem Uruff mat der Hëllef vun = ANY(array).

An an all esou Grupp vun Identifizéierer kënne mir all d'IDen, déi am virege Schrëtt fonnt goufen, duerch "Nodes" huelen. Dat ass, bei all nächste Schrëtt wäerte mir Sich no all Nokommen vun engem bestëmmten Niveau op eemol.

Nëmmen, hei ass de Problem, an der rekursiver Auswiel kënnt Dir net selwer an enger nestéierter Ufro zougräifen, awer mir mussen iergendwéi nëmmen auswielen wat am viregten Niveau fonnt gouf ... Et stellt sech eraus datt et onméiglech ass eng nestéiert Ufro fir déi ganz Auswiel ze maachen, awer fir säi spezifescht Feld ass et méiglech. An dëst Feld kann och eng Array sinn - dat ass wat mir musse benotzen ANY.

Et kléngt e bësse verréckt, awer am Diagramm ass alles einfach.

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;

[kuckt op explain.tensor.ru]

An hei ass dat Wichtegst net emol gewannen 1.5 Mol an Zäit, an datt mir manner Puffer ofgezunn hunn, well mir hunn nëmmen 5 Uruff un den Index amplaz 30!

En zousätzleche Bonus ass d'Tatsaach, datt no der Finale Unnest d'Identificateuren op "Niveauen" bestallt bleiwen.

Node Zeechen

Déi nächst Iwwerleeung déi hëlleft d'Performance ze verbesseren ass - "Blieder" kënnen net Kanner hunn, dat heescht, fir si brauch een iwwerhaapt net "no ënnen" ze kucken. An der Formuléierung vun eiser Aufgab heescht dat, datt wa mir d'Kette vun den Departementer verfollegen an en Employé erreechen, da brauch een net méi wäit laanscht dës Branche ze kucken.

Loosst eis an eisen Dësch eragoen zousätzlech boolean-Feld, déi eis direkt soen, ob dës speziell Entrée an eisem Bam e "Knuet" ass - dat heescht, ob et iwwerhaapt Nokommen kann hunn.

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

Super! Et stellt sech eraus datt nëmmen e bësse méi wéi 30% vun alle Bamelementer Nokommen hunn.

Loosst eis elo e bëssen anere Mechaniker benotzen - Verbindungen zum rekursive Deel duerch LATERAL, wat eis erlaabt direkt op d'Felder vun der rekursiver "Tabelle" ze kommen, an eng aggregéiert Funktioun mat enger Filterbedingung op Basis vun engem Node ze benotzen fir de Set vu Schlësselen ze reduzéieren:

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;

[kuckt op explain.tensor.ru]

Mir konnten e méi Index Opruff reduzéieren an gewonnen méi wéi 2 Mol am Volume Korrektur.

#2. Komme mer zréck op d'Wuerzelen

Dësen Algorithmus wäert nëtzlech sinn wann Dir Rekorder fir all Elementer "up the Bam" sammele musst, wärend Dir Informatioun behält iwwer wéi eng Quellblat (a mat wéi enger Indicateuren) et an der Probe gefouert huet - zum Beispill fir e Resumébericht ze generéieren mat Aggregatioun an Noden.

Wat folgendermoossen soll eleng als proof-of-concept geholl ginn, well d'Ufro ganz ëmständlech ass. Awer wann et Är Datebank dominéiert, sollt Dir drun denken ähnlech Techniken ze benotzen.

Loosst eis mat e puer einfachen Aussoen ufänken:

Déi selwecht Rekord aus der Datebank Et ass am beschten et nëmmen eng Kéier ze liesen.
Records aus der Datebank Et ass méi effizient a Chargen ze liesenwéi eleng.

Loosst eis elo probéieren déi Ufro ze konstruéieren déi mir brauchen.

Schrëtt 1

Natierlech, wann d'Rekursioun initialiséiert (wou wäre mir ouni et!) Mir mussen d'Records vun de Blieder selwer subtrahéieren op Basis vun der Set vun initialen Identifizéierer:

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

Wann et iergendeen komesch ausgesäit datt de "Set" als String gespäichert ass an net als Array, da gëtt et eng einfach Erklärung dofir. Et gëtt eng agebaute aggregéierend "Gluing" Funktioun fir Saiten string_agg, awer net fir Arrays. Obwuel si einfach op Iech selwer ëmzesetzen.

Schrëtt 2

Elo kréie mir eng Rei Sektiouns-IDen déi weider gelies musse ginn. Bal ëmmer wäerte se a verschiddenen Opzeechnunge vum Originalset duplizéiert ginn - also mir géifen gruppéieren hinnen, iwwerdeems Erhaalung Informatiounen iwwert d'Quell Blieder.

Awer hei waarden dräi Probleemer op eis:

De "subrekursive" Deel vun der Ufro kann net aggregéiert Funktiounen enthalen mat GROUP BY.
Eng Referenz op eng rekursiv "Tabelle" kann net an enger nestéierter Ënnerquery sinn.
Eng Ufro am rekursiven Deel kann net e CTE enthalen.

Glécklecherweis sinn all dës Probleemer ganz einfach ëmzegoen. Loosst eis vum Enn ufänken.

CTE am rekursive Deel

Hei ass esou Net schaffen:

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

An esou funktionnéiert et, d'Klammern maachen den Ënnerscheed!

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

Nestéiert Ufro géint eng rekursiv "Tabelle"

Hmm ... E rekursive CTE kann net an enger Ënnerquery zougänglech sinn. Awer et kéint am CTE sinn! An eng nestéiert Ufro ka schonn Zougang zu dësem CTE kréien!

GROUP BY bannen Rekursioun

Et ass désagréabel, awer ... Mir hunn en einfache Wee fir GROUP BY ze emuléieren DISTINCT ON an Fënster Funktiounen!

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

An esou funktionnéiert et!

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

Elo gesi mir firwat déi numeresch ID an Text ëmgewandelt gouf - sou datt se matenee verbonne kënne mat Kommen getrennt sinn!

Schrëtt 3

Fir d'Finale bleiwe mir näischt méi:

mir liesen "Sektioun" records baséiert op enger Rei vu gruppéiere IDen
mir vergläichen déi subtrahéiert Sektiounen mat den "Sets" vun den ursprénglechen Blieder
"erweidert" de Set-String mat 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;

[kuckt op explain.tensor.ru]

Source: will.com

PostgreSQL Antipatterns: Wéi déif ass d'Kanéngchen Lach? loosst eis duerch d'Hierarchie goen

#1. Wéi déif ass den Hues Lach?

Bulk Korrektur duerch Index

Node Zeechen

#2. Komme mer zréck op d'Wuerzelen

Schrëtt 1

Schrëtt 2

CTE am rekursive Deel

Nestéiert Ufro géint eng rekursiv "Tabelle"

GROUP BY bannen Rekursioun

Schrëtt 3

Setzt e Commentaire annuléieren reply