🥇PostgreSQL Antipatterns: Gomba retsuro rakadzika zvakadii? ngatipfuure nepakati pehumambo

Mune yakaoma ERP masisitimu masangano mazhinji ane chimiro che hierarchicalapo zvinhu zvakafanana zvakamira mukati muti wehukama hwemadzitateguru-wedzinza - ichi ndicho chimiro chesangano chebhizinesi (ese matavi aya, madhipatimendi nemapoka ebasa), uye kodhi yezvinhu, nenzvimbo dzebasa, uye geography yenzvimbo dzekutengesa, ...

Kutaura zvazviri, hapana bhizinesi otomatiki nzvimbo, uko kwaisazove nechero hutungamiri semugumisiro. Asi kunyangwe ukasashandira "bhizinesi," unogona kusangana nyore nehukama hwehukama. Ihwo hutatu, kunyangwe muti wemhuri yako kana chirongwa chepasi chezvivakwa munzvimbo yekutenga chakafanana chimiro.

Kune nzira dzakawanda dzekuchengeta muti wakadaro muDBMS, asi nhasi tichatarisa pane imwe chete sarudzo:

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

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

Uye iwe uchidongorera mukati mehupamhi hwehutungamiriri, yakamirira nemoyo murefu kuti uone kuti [ne]nzira dzako dze "kusaziva" dzekushanda nechimiro chakadaro dzingave dzakadii.

Ngatitarisei matambudziko akajairika anomuka, kuita kwavo muSQL, uye edza kuvandudza mashandiro avo.

#1. Gomba retsuro rakadzika zvakadii?

Ngativei, kune chokwadi, tibvume kuti chimiro ichi chicharatidza kuzviisa pasi kwemadhipatimendi muhurongwa hwesangano: madhipatimendi, zvikwata, zvikamu, mapazi, mapoka ekushanda ... - chero zvamunodana.

Kutanga, ngatigadzire 'muti' wedu wezvikamu gumi

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;

Ngatitange nebasa rakareruka - kutsvaga vashandi vese vanoshanda mukati mechikamu chakati, kana maererano nehutungamiriri - tsvaga vana vese ve node. Zvingavawo zvakanaka kuwana "kudzika" kwemuzukuru ... Zvose izvi zvingave zvakakosha, semuenzaniso, kuvaka imwe rudzi kusarudzwa kwakaoma kunoenderana nerondedzero yezvitupa zvevashandi ava.

Zvese zvingave zvakanaka kana pangove nematanho akati wandei evazukuru ava uye nhamba yacho iri mukati megumi nemaviri, asi kana paine anopfuura mazinga mashanu, uye kwatova nevazhinji vevazukuru, panogona kunge paine matambudziko. Ngatitarisei maitiro echinyakare pasi-muti yekutsvaga sarudzo dzakanyorwa (uye basa). Asi chekutanga, ngationei kuti ndedzipi node dzichanyanya kunakidza patsvagurudzo yedu.

Zvanyanya "dzakadzika" 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}
...

Zvanyanya "zvakafara" 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

Pamibvunzo iyi takashandisa zvakajairwa recursive JOIN:

Zviri pachena, nemuenzaniso wekukumbira uyu kuwanda kwekudzokororwa kuchaenderana nenhamba yese yevazukuru (uye kune akati wandei awo), uye izvi zvinogona kutora zvakakosha zviwanikwa, uye, semhedzisiro, nguva.

Ngatitarisei pane "yakafara" subtree:

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;

[tarisa ku explain.tensor.ru]

Sezvaitarisirwa, takawana zvese makumi matatu zvinyorwa. Asi vakashandisa 30% yenguva yakazara pane izvi - nekuti ivo vakaitawo makumi matatu ekutsvaga mune index. Zvinoita here kuita zvishoma?

Kuongororwa kwehuwandu neindex

Isu tinoda kuita yakaparadzana indekisi mubvunzo kune yega yega node? Zvinoitika kuti kwete - tinogona kuverenga kubva pane index uchishandisa makiyi akati wandei kamwechete mukufona kumwe chete nerubatsiro = ANY(array).

Uye muboka rimwe nerimwe rezviziviso tinogona kutora maID ese anowanikwa munhanho yapfuura ne "node". Ndiko kuti, pane imwe neimwe nhanho inotevera isu ticha tsvaga vazukuru veimwe nhanho kamwechete.

Chete, heino dambudziko, mune inodzokororwa sarudzo, haugone kuzviwanira pachako mumubvunzo wakaiswa, asi isu tinoda neimwe nzira kusarudza chete izvo zvakawanikwa padanho rekare ... Zvinoitika kuti hazvibviri kuita nested query yekusarudzwa kwese, asi kune iyo chaiyo ndima inogoneka. Uye iyi ndima inogona zvakare kuve yakatevedzana - ndiyo yatinofanira kushandisa ANY.

Zvinonzwika kupenga zvishoma, asi mudhayagiramu zvese zviri nyore.

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;

[tarisa ku explain.tensor.ru]

Uye pano chinhu chinonyanya kukosha hachisi kunyange kuhwina 1.5 nguva nenguva, uye kuti takabvisa mabhafa mashoma, sezvo tichingova nemafoni mashanu chete kune indekisi pane makumi matatu!

Imwe bhonasi inyaya yekuti mushure mekupedzisira unnest, zviziviso zvicharamba zvakarairwa ne "mazinga".

Node sign

Kufunga kunotevera kunozobatsira kuvandudza mashandiro ndei- "mashizha" haagoni kuita vana, ndiko kuti, kwavari hapana chikonzero chekutarisa "pasi" zvachose. Mukugadzirisa basa redu, izvi zvinoreva kuti kana isu takatevera cheni yemadhipatimendi uye tikasvika kune mushandi, saka hapana chikonzero chekutarisa zvakare pabazi iri.

Ngatipinde mutafura yedu kuwedzera boolean-munda, iyo ichabva yatiudza kana iyi yekupinda mumuti wedu iri "node" - ndiko kuti, ingave nevazukuru zvachose.

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

Hukuru! Zvinoitika kuti zvishoma kudarika 30% yemiti yose ine zvizvarwa.

Zvino ngatishandisei mechanic yakati siyanei - yekubatanidza kune inodzokororwa chikamu kuburikidza LATERAL, iyo ichatibvumira kuti tisvike nokukurumidza minda ye "tafura" inodzokororwa, uye shandisa aggregate basa nemamiriro ekusefa kunobva pane node kuderedza seti yemakiyi:

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;

[tarisa ku explain.tensor.ru]

Takakwanisa kudzikisa imwezve index call uye akahwina kanopfuura ka2 muvhoriyamu kuongorora.

#2. Ngatidzokere kumidzi

Iyi algorithm ichave inobatsira kana iwe uchida kuunganidza marekodhi ezvese zvinhu "kumusoro kwemuti", uchichengeta ruzivo rwekuti nderipi pepa (uye nezviratidzo zvipi) zvaita kuti ribatanidzwe mumuenzaniso - semuenzaniso, kugadzira pfupiso yeshumo. nekubatanidzwa mumanode.

Izvo zvinotevera zvinofanirwa kutorwa chete sehumbowo-hwe-pfungwa, sezvo chikumbiro chinoshanduka kuve chakaomesesa. Asi kana ichitonga dhatabhesi yako, iwe unofanirwa kufunga nezve kushandisa maitiro akafanana.

Ngatitangei nemashoko mashoma akareruka:

Iyo yakafanana rekodhi kubva kune database Zvakanakisisa kuriverenga kamwe chete.
Zvinyorwa kubva ku database Zvinonyanya kushanda kuverenga mumabhechikwete chete.

Zvino ngatiedzei kugadzira chikumbiro chatinoda.

vanotsika 1

Zviripachena, kana tichitanga kudzokororwa (taizovepi tisina!) isu tichafanirwa kubvisa zvinyorwa zvemashizha pachazvo zvichienderana neseti yezviziviso zvekutanga:

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

Kana zvaiita sezvisinganzwisisiki kune mumwe munhu kuti "seti" inochengetwa setambo uye kwete mutsara, saka pane tsanangudzo iri nyore yeizvi. Pane yakavakirwa-mukati aggregating "gluing" basa retambo string_agg, asi kwete yezvakawanda. Kunyangwe iye nyore kushandisa uri woga.

vanotsika 2

Zvino taizowana seti yezvikamu ID izvo zvinoda kuverengerwa mberi. Anenge nguva dzose anozodzokororwa mumarekodhi akasiyana eiyo yekutanga set - saka isu taizodaro vaunganidze, apo uchichengetedza ruzivo pamusoro pematsime mashizha.

Asi pano matambudziko matatu akatimirira:

Iyo "subrecursive" chikamu chemubvunzo haigone kuve neaggregate mabasa ne GROUP BY.
Chirevo che "tafura" inodzokororwa haigone kuve mudendere subquery.
Chikumbiro muchikamu chinodzokororwa hachigone kuve neCTE.

Sezvineiwo, matambudziko ese aya ari nyore kugadzirisa. Ngatitangei kubva kumagumo.

CTE muchikamu chinodzokororwa

Pano saizvozvo kwete anoshanda:

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

Uye saka inoshanda, maparentheses anoita mutsauko!

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

Nested mubvunzo uchipokana ne "tafura" inodzokororwa

Hmm... Recursive CTE haigone kuwanikwa muchidimbu. Asi inogona kunge iri mukati meCTE! Uye chikumbiro chakagara chinogona kutowana iyi CTE!

GROUP BY mukati recursion

Hazvinakidze, asi... Tine nzira iri nyore yekutevedzera GROUP NEkushandisa DISTINCT ON uye hwindo mabasa!

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

Uye iyi ndiyo mashandiro ayo!

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

Ikozvino tava kuona kuti nei ID yenhamba yakashandurwa kuita mavara - kuti abatanidzwe pamwechete achipatsanurwa nemakoma!

vanotsika 3

Kwekupedzisira isu hatina chasara:

tinoverenga "chikamu" zvinyorwa zvichibva pane seti yezvitupa zvakaiswa
tinoenzanisa zvikamu zvakabviswa ne "seti" yemashizha ekutanga
"wedzera" set-tambo uchishandisa 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;

[tarisa ku explain.tensor.ru]

Source: www.habr.com

PostgreSQL Antipatterns: Gomba retsuro rakadzika zvakadii? ngatipfuure nepakati pehumambo

#1. Gomba retsuro rakadzika zvakadii?

Kuongororwa kwehuwandu neindex

Node sign

#2. Ngatidzokere kumidzi

vanotsika 1

vanotsika 2

CTE muchikamu chinodzokororwa

Nested mubvunzo uchipokana ne "tafura" inodzokororwa

GROUP BY mukati recursion

vanotsika 3

Voeg kukanzura mhinduro