🥇PostgreSQL Antipatterns: Kodi dzenje la akalulu ndi lakuya bwanji? tiyeni tidutse mu utsogoleri

Mu machitidwe ovuta a ERP mabungwe ambiri ali ndi chikhalidwe cha hierarchicalpamene zinthu homogeneous zikulumikizana mtengo wa maubale a makolo ndi mbadwa - ichi ndi dongosolo la bungwe la ogwira ntchito (nthambi zonsezi, madipatimenti ndi magulu ogwira ntchito), ndi mndandanda wa katundu, ndi madera a ntchito, ndi malo ogulitsa, ...

Ndipotu palibe madera opangira mabizinesi, pomwe sipadzakhalanso utsogoleri uliwonse. Koma ngakhale simukugwira ntchito "bizinesi," mutha kukumana ndi maubwenzi otsogola mosavuta. Ndizosadabwitsa, ngakhale banja lanu kapena mapulani apansi a malo ogulitsira ndizomwezo.

Pali njira zambiri zosungira mtengo wotere mu DBMS, koma lero tiyang'ana njira imodzi yokha:

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

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

Ndipo pamene mukuyang'ana mu kuya kwa utsogoleri, ikuyembekezera moleza mtima kuti muwone momwe njira zanu "zopanda pake" zogwirira ntchito ndi dongosolo loterolo zidzakhalire.

Tiyeni tiwone zovuta zomwe zimachitika, kukhazikitsidwa kwawo mu SQL, ndikuyesera kukonza magwiridwe antchito awo.

#1. Kodi dzenje la akalulu ndi lakuya bwanji?

Tiyeni, motsimikizika, tivomereze kuti dongosololi lidzawonetsa kugonjera kwa madipatimenti mu dongosolo la bungwe: madipatimenti, magawo, magawo, nthambi, magulu ogwira ntchito ... - chirichonse chimene mumachitcha.

Choyamba, tiyeni tipange 'mtengo' wathu wa zinthu 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;

Tiyeni tiyambe ndi ntchito yosavuta - kupeza antchito onse omwe amagwira ntchito m'gawo linalake, kapena malinga ndi utsogoleri - kupeza ana onse mfundo. Zingakhalenso zabwino kupeza "kuya" kwa mbadwa ... Zonsezi zingakhale zofunikira, mwachitsanzo, kumanga mtundu wina wa kusankha kovuta kutengera mndandanda wa ma ID a ogwira ntchitowa.

Chilichonse chingakhale bwino ngati pali milingo ingapo yokha ya mbadwa izi ndipo chiwerengerocho chili mkati mwa khumi ndi awiri, koma ngati pali milingo yopitilira 5, ndipo palinso mbadwa zambiri, pakhoza kukhala zovuta. Tiyeni tiwone momwe kusaka kwamitengo yachikhalidwe kumalembedwera (ndi ntchito). Koma choyamba, tiyeni tiwone kuti ndi ma node ati omwe angakhale osangalatsa kwambiri pa kafukufuku wathu.

Kwambiri "zakuya" mitengo yaing'ono:

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

Kwambiri "wamba" mitengo yaing'ono:

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

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

Pamafunso awa tidagwiritsa ntchito zofananira recursive JOIN:

Mwachiwonekere, ndi chitsanzo chopempha ichi chiwerengero cha kubwereza chidzakhala chofanana ndi chiwerengero chonse cha mbadwa (ndipo pali khumi ndi awiri a iwo), ndipo izi zitha kutenga zofunikira kwambiri, ndipo, chifukwa chake, nthawi.

Tiyeni tiwone "zambiri" 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;

[onani pa explain.tensor.ru]

Monga momwe timayembekezera, tidapeza zolemba zonse 30. Koma adawononga 60% ya nthawi yonse pa izi - chifukwa adafufuzanso 30 pamndandanda. Kodi ndizotheka kuchita zochepa?

Kuwerengera mochuluka ndi index

Kodi tifunika kupanga funso losiyana pa mfundo iliyonse? Zikukhalira kuti ayi - tikhoza kuwerenga kuchokera index kugwiritsa ntchito makiyi angapo nthawi imodzi pakuyimba kumodzi ndi thandizo = ANY(array).

Ndipo mu gulu lirilonse la zozindikiritsa tikhoza kutenga ma ID onse omwe akupezeka mu sitepe yapitayi ndi "node". Ndiko kuti, pa sitepe iliyonse yotsatira tidzatero fufuzani mbadwa zonse za mulingo winawake nthawi imodzi.

Kungoti ndiye tsoka, pakusankha kobwerezabwereza, simungathe kudzifikira nokha mufunso lokhazikitsidwa, koma tifunika kusankha mwanjira ina zomwe zinapezeka pamlingo wapitawo ... Zikuoneka kuti simungathe kupanga funso lachisa pazosankha zonse, koma pamunda wake weniweni mungathe. Ndipo gawo ili litha kukhalanso gulu - zomwe ndi zomwe tikuyenera kugwiritsa ntchito ANY.

Zikumveka zopenga pang'ono, koma mu chithunzi chirichonse chiri chophweka.

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;

[onani pa explain.tensor.ru]

Ndipo apa chinthu chofunikira kwambiri sichili ngakhale kupambana 1.5 nthawi, ndi kuti tachotsa zosungira zochepa, popeza tili ndi mafoni 5 okha m'malo mwa 30!

Bhonasi yowonjezera ndi yakuti pambuyo pa unnest yomaliza, zozindikiritsa zidzakhalabe zolamulidwa ndi "ma level".

Chizindikiro cha node

Lingaliro lotsatira lomwe lingathandize kukonza magwiridwe antchito ndi - "masamba" sangakhale ndi ana, ndiko kuti, kwa iwo palibe chifukwa choyang'ana "pansi" konse. Popanga ntchito yathu, izi zikutanthauza kuti ngati titatsatira mndandanda wa madipatimenti ndikufikira wogwira ntchito, ndiye kuti palibe chifukwa choyang'ana mopitilira munthambi iyi.

Tiyeni tilowe mu tebulo lathu zowonjezera boolean-munda, zomwe zidzatiuza nthawi yomweyo ngati kulowa mumtengo wathu ndi "node" - ndiko kuti, kaya kungakhale ndi mbadwa konse.

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

Zabwino! Zikuoneka kuti 30% yokha ya mitengo yonse imakhala ndi mbadwa.

Tsopano tiyeni tigwiritse ntchito makaniko osiyana pang'ono - kulumikizana ndi gawo lobwereza kudzera LATERAL, zomwe zidzatilola kuti tipeze nthawi yomweyo minda ya "tebulo" yobwerezabwereza, ndikugwiritsa ntchito ntchito yowonjezera yokhala ndi zosefera zochokera pa node kuti muchepetse mafungulo:

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;

[onani pa explain.tensor.ru]

Tinatha kuchepetsa kuyimbanso kamodzi kolozera ndi adapambana maulendo opitilira 2 mu voliyumu tsimikizirani.

#2. Tiyeni tibwerere ku mizu

Algorithm iyi idzakhala yothandiza ngati mukufuna kusonkhanitsa zolemba zazinthu zonse "pamwamba pamtengo", ndikusunga zambiri za tsamba lomwe (komanso ndi zizindikiro ziti) zomwe zidapangitsa kuti ziphatikizidwe pachitsanzo - mwachitsanzo, kupanga lipoti lachidule. ndi aggregation mu mfundo.

Zomwe zimatsatira ziyenera kutengedwa ngati umboni wa lingaliro, chifukwa pempho limakhala lovuta kwambiri. Koma ngati ikulamulira database yanu, muyenera kuganizira kugwiritsa ntchito njira zofanana.

Tiyeni tiyambe ndi mawu angapo osavuta:

Zolemba zomwezo kuchokera ku database Ndi bwino kuliwerenga kamodzi kokha.
Zolemba kuchokera ku database Ndizothandiza kwambiri kuwerenga m'magulukuposa yekha.

Tsopano tiyeni tiyese kupanga pempho lomwe tikufuna.

mwatsatane 1

Mwachiwonekere, poyambitsa kubwereza (tikanakhala kuti popanda izo!) tidzayenera kuchotsa zolemba za masamba omwewo potengera zozindikiritsa zoyamba:

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

Ngati zikuwoneka zachilendo kwa wina kuti "set" amasungidwa ngati chingwe osati mndandanda, ndiye pali kufotokozera kosavuta kwa izi. Pali ntchito yophatikizira "gluing" ya zingwe string_agg, koma osati a magulu. Ngakhale iye zosavuta kukhazikitsa nokha.

mwatsatane 2

Tsopano tipeza ma ID agawo omwe adzafunika kuwerengedwanso. Pafupifupi nthawi zonse amalembedwa m'mabuku osiyanasiyana amtundu woyambirira - momwe tingachitire gulu iwo, pamene mukusunga zambiri zokhudza masamba a gwero.

Koma pali zovuta zitatu zomwe zikutiyembekezera:

Gawo la "subrecursive" lafunso silingakhale ndi magwiridwe antchito ndi GROUP BY.
Kalozera wa "tebulo" wobwerezabwereza sangakhale mu chisa chaching'ono.
Pempho mu gawo lobwereza silingakhale ndi CTE.

Mwamwayi, mavuto onsewa ndi osavuta kuthana nawo. Tiyeni tiyambire kumapeto.

CTE mu gawo lobwerezabwereza

Ngati chonchi osati ntchito:

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

Ndipo kotero zimagwira ntchito, mabatani amapanga kusiyana!

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

Funso lokhazikika motsutsana ndi "tebulo" lobwerezabwereza

Hmm... CTE yobwerezabwereza singapezeke muzokambirana. Koma zitha kukhala mkati mwa CTE! Ndipo pempho lokhazikitsidwa litha kupeza kale CTE iyi!

GROUP BY mkati mobwereza

Ndizosasangalatsa, koma ... Tili ndi njira yosavuta yotsanzira GROUP BY pogwiritsa ntchito DISTINCT ON ndi ntchito za mawindo!

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

Ndipo umu ndi momwe zimagwirira ntchito!

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

Tsopano tikuwona chifukwa chake ID ya manambala idasinthidwa kukhala mawu - kuti alumikizike pamodzi ndikusiyanitsidwa ndi koma!

mwatsatane 3

Pomaliza tilibe chilichonse:

timawerenga zolemba za "gawo" potengera ma ID amagulu
timafanizira magawo ochotsedwa ndi "maseti" a mapepala oyambirira
"kulitsa" chingwe chokhazikitsidwa pogwiritsa ntchito 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;

[onani pa explain.tensor.ru]

Source: www.habr.com

PostgreSQL Antipatterns: Kodi dzenje la kalulu ndi lakuya bwanji? tiyeni tidutse mu utsogoleri

#1. Kodi dzenje la akalulu ndi lakuya bwanji?

Kuwerengera mochuluka ndi index

Chizindikiro cha node

#2. Tiyeni tibwerere ku mizu

mwatsatane 1

mwatsatane 2

CTE mu gawo lobwerezabwereza

Funso lokhazikika motsutsana ndi "tebulo" lobwerezabwereza

GROUP BY mkati mobwereza

mwatsatane 3

Kuwonjezera ndemanga kuletsa reply