🥇 PostgreSQL Antipatterns: Quam profunda est lepus foraminis? eamus per hierarchiam

In complex systemata ERP multa entia habent naturam hierarchicamCum homogenea aciem in lignum antecessoris abnepos relationes - haec est norma incepti (omnes hae propagines, Dicasteria et coetus operandi), et catalogus bonorum, et ambitus laboris, ac geographiae venditionum puncta,...

Nam non est negotium automation areasubi non esset aliqua hierarchia. Sed etsi "pro negotio" non laboras, tamen relationes hierarchicae facile occurrant. Tritum est, etiam tua arbor vel area consilium praemissorum in centro shopping eadem structura.

Multi modi sunt talem arborem condere in DBMS, sed hodie unam tantum optionem intendunt;

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

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

Et dum in profundum hierarchiae perspicis, patienter exspectat videre quomodo operandi vias tuas « simplices » talis structurae erit.

Intueamur quaestiones typicas, quae oriuntur, in SQL exsequendam, et operam suam emendare conantur.

#1. Quam altum est lepus foraminis?

Nos, pro definito, accipiamus hanc structuram subordinationem Dicasteriorum reflectere in structura ordinationis: Dicasteria, divisiones, sectores, rami, coetus operandi... - quidquid vocas.

Primum generare nostrum lignum de elementis 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;

Incipiamus a munere simplicissimo - inveniendo omnes operarios qui operantur intra certas partes vel secundum hierarchiam - invenio omnes filios a nodi. Id quoque esset noscere ut "profundum" abnepos... Haec omnia necessaria sunt, exempli gratia, ut quaedam aedificent. multiplex lectio secundum numerum IDs horum conductorum.

Omnia denique essent si modo duo gradus horum posteritatis et numerus intra duodecim, sed si plures quam 5 gradus sunt, et iam justos posterorum sunt, problemata esse possunt. Intueamur quomodo traditum descendens-arboris optiones quaerendi scriptae sint (et opus). Sed primum, quae nodes maxime interesting investigationis nostrae constituamus.

optimum "profundum" 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}
...

optimum "lata" 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

Pro his queries usi sumus in typica recursive JOIN:

Patet, cum rogationis exemplar numerus iterations par erit numerus posterorum (et plures sunt duodecim eorum), et haec satis significant facultates capere et, consequenter, tempus.

cohibeamus in subtilitate "latissima":

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;

[Aspice explain.tensor.ru]

Sicut expectatur, omnes 30 monumenta invenimus. Sed 60% totius temporis in hoc consumpserunt - quia etiam 30 inquisitiones in indice fecerunt. Num minus id fieri potest?

Mole proofreading by index

Num necesse est ut indices singulas nodi singulas facias? Evenit ut nullum - ex indice legere possimus pluribus clavibus simul in vocatione propter = ANY(array).

Et in unaquaque tali numero identificantium possumus omnes IDs in priori gradu reperiri per "nodos". Hoc est, ad quemlibet gradum proximum volumus quaeramus omnes posteros cuiusdam gradus simul.

Solus hic est quaestio; in delectu recursivo, ipsum accedere non potes in inquisitione nidificatased solum aliquo modo eligere debemus quod in priore gradu repertum est.. Evenit non posse interrogationem integram facere delectu, sed de suo proprio campo possibile est. Hic campus etiam potest esse ornatus, quo uti oportet ANY.

Insanus paulo sonat, sed in schemate omnia simplicia sunt.

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;

[Aspice explain.tensor.ru]

Et hic potissima res non est vincere 1.5 temporibus in temporeet pauciores quiddam demp- gimus, cum tantum 5 ad indicem pro XXX!

Bonus additus est eo quod identiciarii, post ultimum unnest, per "graduum" ordinati manebunt.

nodi signum

Postero consideratione quod adiuvabit amplio effectus est "Folia" non possunt habere liberoshoc est, illis omnino «despicere» non est necesse. In formulatione operis nostri, hoc significat, quod si dicastionum catenam sequeremur et operarium attigissemus, non opus est ulterius per hunc ramum inspicere.

Eamus in mensam nostram additional boolean-fieldquod statim nobis indicabit an hic ingressus in nostra arbore sit "nodi", id est, an omnino posteritas habere possit.

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

Magna! Evenit ut paulo plus quam 30% omnium elementorum arboris originem habeat.

Nunc utamur mechanicis paulo diversis necessariis ad partem recursivam per LATERALquae sinit nos statim ad agros "mensae" recursivae accedere, et munus aggregatum uti conditione eliquante innixa nodo ad clausuram clavium reducendam:

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;

[Aspice explain.tensor.ru]

Potuimus reducere unum indicem vocationis et vicit plus quam II temporibus in volumine probatio.

#2. Eamus ad radices

Hoc algorithmus utile erit si monumenta omnia elementa "arborem" colligere necesse sit, servata informatione de qua fonte scheda (et quibus indicibus) id effecit ut in sample includi (exempli gratia, relationem summariam generare. cum aggregatione in nodis.

Id quod sequitur solum ad probationem notionis accipiendum est, quoniam postulatio gravissima evadit. Si vero datorum vestrorum dominatur, cogitare de similibus artificiis utendo.

Sit scriptor satus cum duobus simplicibus sententiis:

Eundem commentarium e database Est optimum legere hoc modo semel.
Records ex database Plus est efficacius legere in batchesquam solum.

Nunc instantiam quae nobis opus est construere conemur.

1 step

Patet, cum recursus initialis (ubi carere volumus) monumenta foliorum ipsarum ex identificantium initialium statuto subtrahere debebimus;

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 mirum visum est alicui quod "paro" pro filo reponitur et non ordo, tunc simplex explicatio est. Est constructum in aggregatione "gluing" munus pro chordis string_aggnon vero vestit. Etsi illa facile ad efficiendum in tua.

2 step

Nunc de sectionis IDs statuto volumus quod ulterior legere opus erit. Fere semper in diversis monumentis duplicata archetypi - sic volumus coetus eorumservata notitia de fonte foliorum.

Sed hic tria exspectant molestias;

"subrecursive" pars interrogationis non potest continere munera aggregata cum GROUP BY.
Respicientia ad "mensam" recursivam in subquerio collocari non potest.
Petitio in parte recursiva non potest continere CTE.

Fortunate, omnia haec problemata satis facilia sunt ad elaborandum. A fine sumamus exordium.

CTE in parte recursive

Sicut hoc non laborans;

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

Ita facit, parentheses discernunt!

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

Investigatio nidificata contra recursivam "mensam"

Hmm... A recursiva CTE in subqueria accedere non potest. Sed posset intus CTE! Petitio nested iam potest accedere hanc CTE!

Group intus recursus

Ingratum est, sed... modum simplicis habemus ut GROUPEM aemulandi utendo DISTINCT ON et fenestrarum munera!

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

Et hoc quomodo operatur!

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

Nunc videmus cur ID numerorum in textum versa sit, ut commatibus disiungi possent!

3 step

Ad extremum nihil nobis superest;

legitur "sectionem" records secundum a paro of grouped IDs
comparamus sectiones detractas cum "occidere" originalis schedae
"Expand" in paro-filum utens 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;

[Aspice explain.tensor.ru]

Source: www.habr.com

PostgreSQL Antipatterns: Quam profunda est lepus foraminis? eamus per Hierarchiam

#1. Quam altum est lepus foraminis?

Mole proofreading by index

nodi signum

#2. Eamus ad radices

1 step

2 step

CTE in parte recursive

Investigatio nidificata contra recursivam "mensam"

Group intus recursus

3 step

Add a comment Отменить ответ