🥇PostgreSQL Antipatterns: Intee ayuu qoto dheer yahay godka bakaylaha? aan soo marno kala sareynta

Nidaamyada ERP ee adag hay'ado badan ayaa leh dabeecad heersare ahmarka walxaha isku midka ah ay saf ugu jiraan geedka xidhiidhka awoowe-faraca - kani waa qaab-dhismeedka ururka (dhammaan laamahaas, waaxaha iyo kooxaha shaqada), iyo liiska alaabta, iyo meelaha shaqada, iyo juqraafi ee goobaha iibka, ...

Dhab ahaantii, ma jirto goobaha iswada ganacsiga, halkaas oo aysan jiri lahayn wax kala sareyn ah natiijada. Laakiin xitaa haddii aadan u shaqeynin "ganacsiga," waxaad weli si fudud ula kulmi kartaa cilaaqaadka kala sareynta. Waa wax aan macquul ahayn, xitaa geedka qoyskaaga ama qorshaha sagxada dhismaha ee xarunta wax iibsiga waa isku qaab.

Waxaa jira siyaabo badan oo geedka noocan oo kale ah loogu kaydiyo DBMS, laakiin maanta waxaan diiradda saari doonaa hal doorasho oo keliya:

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

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

Oo intaad u fiirsato gunta hoose ee kala sareynta, waxa ay si samir leh u sugaysaa in aad aragto sida ay waxtarka u leeyihiin hababkaaga "nafudud" ee la shaqaynta qaabkan oo kale.

Aynu eegno dhibaatooyinka caadiga ah ee soo baxa, hirgelintooda SQL, oo aan isku dayno inaan hagaajinno waxqabadkooda.

#1. Intee ayuu qoto dheer yahay godka bakaylaha?

Aynu, si qeexan, aqbalno in qaab-dhismeedkani uu ka tarjumayo hoos-u-dhaca waaxaha qaab-dhismeedka ururka: waaxaha, qaybaha, qaybaha, laamaha, kooxaha shaqada ... - wax kasta oo aad u yeerto.

Marka hore, aynu soo saarno 'geedka' oo ka kooban 10K walxood

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;

Aan ku bilowno hawsha ugu fudud - helitaanka dhammaan shaqaalaha ka shaqeeya qayb gaar ah, ama xagga kala sareynta - hel dhammaan carruurta sanka. Waxa kale oo ay fiicnaan lahayd in la helo "qoto dheer" faraca ... Dhammaan tani waxay noqon kartaa lagama maarmaan, tusaale ahaan, si loo dhiso nooc ka mid ah xulashada adag ee ku salaysan liiska aqoonsiga shaqaalahan.

Wax kastaaba way fiicnaan lahaayeen haddii ay jiraan laba heer oo farcankiis ah oo tiradu ay tahay dhawr iyo toban, laakiin haddii ay jiraan in ka badan 5 heerar, oo ay hore u jiraan daraasiin farac ah, waxaa laga yaabaa inay dhibaatooyin dhacaan. Aynu eegno sida dhaqameed ee xulashooyinka raadinta geedka-hoos u qoran yihiin (oo u shaqeeyaan). Laakiin marka hore, aynu go'aansanno noodhadhka ugu xiisaha badan cilmi-baaristayada.

Inta ugu badan "qoto dheer" geed hoosaadyo:

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

Inta ugu badan "ballaaran" geed hoosaadyo:

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

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

Weydiimahan waxaan u isticmaalnay kuwa caadiga ah ku soo biir oo soo noqnoqda:

Sida cad, oo leh qaabka codsigan tirada soo noqnoqda waxay la mid noqon doontaa wadarta tirada farcanka (oo waxaa jira dhowr iyo toban ka mid ah), tani waxay qaadan kartaa ilo aad u muhiim ah, iyo, natiijada, waqti.

Aynu eegno geed-hoosaadka "ballaaran":

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;

[fiiri sharaxaad.tensor.ru]

Sidii la filayay, waxaan helnay dhammaan 30 diiwaan. Laakiin waxay ku bixiyeen 60% wadarta wakhtiga tan - sababtoo ah waxay sidoo kale sameeyeen 30 baaritaan oo ku jira tusmada. Suurtagal ma tahay in wax yar la sameeyo?

Akhris badan oo index ah

Miyaynu u baahanahay inaan samayno su'aal tusmaysan oo gooni ah nood kasta? Waxaa soo baxday in maya - waxaan ka akhrisan kartaa index adigoo isticmaalaya dhowr furayaal hal mar hal wicitaan iyadoo gacan ka heleysa = ANY(array).

Iyo koox kasta oo caynkaas ah oo aqoonsiyaal ah waxaan ku qaadan karnaa dhammaan aqoonsiga laga helay tallaabadii hore ee "nodes". Taasi waa, talaabo kasta oo xigta waan sameyn doonaa raadi dhammaan faracii heer gaar ah hal mar.

Kaliya, waa kan dhibaatadu, Xulashada soo noqnoqota, ma geli kartid lafteeda marka la eego su'aal guri, laakiin waxaan u baahanahay inaan si uun u doorano kaliya wixii laga helay heerkii hore ... Waxaa soo baxday in aysan suurtagal ahayn in la sameeyo su'aal ku saabsan xulashada oo dhan, laakiin goobteeda gaarka ah waa suurtagal. Goobtani waxay sidoo kale noqon kartaa array - taas oo ah waxa aan u baahanahay inaan isticmaalno ANY.

Waxay u egtahay wax yar oo waalan, laakiin jaantuska wax walba waa sahlan yihiin.

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;

[fiiri sharaxaad.tensor.ru]

Oo halkan waxa ugu muhiimsan maaha xitaa guul 1.5 jeer waqtiga, iyo in aan ka jarnay wax yar oo kaydiyayaal ah, maadaama aan 5 wicis kaliya ku leenahay tusmada halkii aan ka ahaan lahayn 30!

Gunnada dheeraadka ah waa xaqiiqda ka dib xasillooni-darrada kama dambaysta ah, aqoonsiyadu waxay ahaan doonaan kuwo lagu dalbado "heerar".

Calaamada noodhka

Tixgelinta xigta ee gacan ka geysan doonta hagaajinta waxqabadka waa - "caleemo" carruur ma dhalin karaan, taas oo ah, iyaga looma baahna in "hoos loo eego" gabi ahaanba. Samaynta hawshayada, tani waxay ka dhigan tahay haddii aan raacno silsiladda waaxaha oo aan gaarno shaqaale, markaa looma baahna in la sii eego laantan.

Miiskayaga aan galno dheeraad ah boolean-beeraha, kaas oo isla markiiba noo sheegi doona in galitaankan gaarka ah ee geedkeena uu yahay "node" - taas oo ah, haddii ay yeelan karto farcan kasta.

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

Wayn! Waxay soo baxday in wax yar oo kaliya 30% dhammaan walxaha geedku ay leeyihiin faracyo.

Hadda aynu isticmaalno makaanig wax yar ka duwan - isku xirka qaybta soo noqnoqda iyada oo loo marayo LATERAL, kaas oo noo ogolaan doona inaan isla markiiba galno beeraha "miiska" soo noqnoqda, oo aan isticmaalno shaqo isku-dar ah oo leh xaalad shaandhayn oo ku salaysan noodhka si loo yareeyo furayaasha:

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;

[fiiri sharaxaad.tensor.ru]

Waxaan awoodnay inaan yareyno hal wicitaan oo kale iyo ku guuleystay in ka badan 2 jeer mugga dib u akhrin

#2. Aan ku soo noqono xididada

Algorithm-kani waxa uu noqon doonaa mid faa'iido leh haddii aad u baahan tahay inaad ururiso diiwaannada dhammaan walxaha "korka geedka", intaad haynayso macluumaadka ku saabsan xaashida isha (iyo tilmaamayaasha) ayaa sababay in lagu daro muunada - tusaale ahaan, si loo soo saaro warbixin kooban iyadoo la isu geyn doono noodhadhka.

Waxa soo socda waa in loo qaato oo keliya caddayn-fikrad, mar haddii codsigu noqday mid aad u dhib badan. Laakin haddii ay gacanta ku hayso xogtaada xogta, waa inaad ka fikirtaa isticmaalka farsamooyinka la midka ah.

Aan ku bilowno dhowr hadal oo fudud:

Diiwaan la mid ah ka database Way fiicantahay inaad hal mar akhrido.
Diiwaanada laga soo bilaabo database-ka Way ka waxtar badan tahay in dufcadaha wax lagu akhriyokaligiis.

Hadda aan isku dayno inaan dhisno codsiga aan u baahanahay.

talaabo 1

Sida iska cad, marka la bilaabayo soo noqnoqoshada (halkeenu ahaan lahayn la'aanteed!) Waa in aan ka jarnaa diiwaanada caleemaha laftooda iyadoo lagu salaynayo jaangooyooyinka bilowga ah:

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

Haddii ay u muuqatay qof la yaab leh in "set" lagu kaydiyo sida xadhig oo aan ahayn qalab, ka dibna waxaa jira sharraxaad fudud oo tan ah. Waxaa jira isku-darka isku-darka shaqada "gluing" ee xargaha string_agg, laakiin aan loogu talagalin arrays. Inkastoo ay fududahay inaad adigu iskaa u hirgeliso.

talaabo 2

Hadda waxaan heli doonnaa qayb aqoonsiyo ah oo u baahan in la sii akhriyo. Ku dhowaad had iyo jeer waxaa lagu koobi doonaa diiwaanno kala duwan oo asalka asalka ah - sidaas ayaan sameyn doonnaa kooxahooda, iyadoo la ilaalinayo macluumaadka ku saabsan caleemaha isha.

Laakiin halkan saddex dhibaato ayaa ina sugaya:

Qaybta "subrecursive" ee weydiintu kuma koobnaan karto hawlo la isku daray GROUP BY.
Tixraaca "miiska" soo noqnoqda kuma jiri karo guri hoose oo buul leh.
Codsiga qaybta soo noqnoqda kuma koobna CTE.

Nasiib wanaag, dhibaatooyinkan oo dhan aad bay u fududahay in laga shaqeeyo. Aan ka bilowno dhamaadka.

CTE oo ah qayb soo noqnoqota

Sidan oo kale ma shaqeeya:

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

Oo sidaas daraaddeed way shaqeysaa, qoob-ka-ciyaarka ayaa sameeya faraqa!

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

Su'aal ka dhan ah "miiska" soo noqnoqda

Hmm... CTE-ga soo noqnoqda laguma geli karo dugsi hoose. Laakiin waxay noqon kartaa gudaha CTE! Codsiga la daboolayna wuxuu mar hore geli karaa CTE-gan!

GROUP BY gudaha soo noqnoqda

Waa wax aan fiicneyn, laakiin... Waxaan heysanaa hab fudud oo aan ugu dayan karno KOOXDA adigoo isticmaalaya DISTINCT ON iyo hawlaha daaqadaha!

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

Oo tani waa sida ay u shaqeyso!

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

Hadda waxaynu aragnaa sababta aqoonsigii nambarada loogu beddelay qoraal - si ay isugu xidhmaan oo ay u kala qaybiyaan hal-abuuro!

talaabo 3

Finalka waxba nagama hadhin:

waxaanu akhrinay diiwaanada "qayb" oo ku salaysan tiro aqoonsi oo kooxaysan
Waxaan is barbar dhignaa qaybaha la gooyay iyo "goobyada" xaashida asalka ah
"ballaadhi" xadhig-u-dhigista adoo isticmaalaya 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;

[fiiri sharaxaad.tensor.ru]

Source: www.habr.com

PostgreSQL Antipatterns: Intee ayuu qoto dheer yahay godka bakaylaha? aan soo marno kala sareynta

#1. Intee ayuu qoto dheer yahay godka bakaylaha?

Akhris badan oo index ah

Calaamada noodhka

#2. Aan ku soo noqono xididada

talaabo 1

talaabo 2

CTE oo ah qayb soo noqnoqota

Su'aal ka dhan ah "miiska" soo noqnoqda

GROUP BY gudaha soo noqnoqda

talaabo 3

Add a comment cancel reply