PostgreSQL Antipatterns: خرگوش جو سوراخ ڪيترو اونهو آهي؟ اچو ته درجابندي جي ذريعي وڃو

پيچيده ERP سسٽم ۾ ڪيترن ئي ادارن هڪ hierarchical فطرت آهيجڏهن هڪجهڙائي واريون شيون قطار ۾ لڳن ٿيون ابن ڏاڏن جي رشتن جو وڻ - هي آهي اداري جي تنظيمي ڍانچي (اهي سڀ شاخون، شعبا ۽ ڪم گروپ)، ۽ سامان جي فهرست، ۽ ڪم جا علائقا، ۽ سيلز پوائنٽس جي جاگرافي، ...

حقيقت ۾، ڪو به نه آهي ڪاروباري خودڪار علائقن، جتي نتيجي طور ڪو به درجو نه هوندو. پر جيتوڻيڪ جيڪڏهن توهان ڪم نه ڪندا آهيو "ڪاروبار لاءِ،" توهان اڃا تائين آساني سان حاوي رشتن کي منهن ڏئي سگهو ٿا. اهو تمام وڏو آهي، ايستائين جو توهان جي خانداني وڻ يا شاپنگ سينٽر ۾ احاطي جو فرش منصوبو ساڳيو آهي.

DBMS ۾ اهڙي وڻ کي ذخيرو ڪرڻ جا ڪيترائي طريقا آهن، پر اڄ اسان صرف هڪ اختيار تي ڌيان ڏينداسين:

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

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

۽ جڏهن توهان درجي بندي جي کوٽائي ۾ ڏسي رهيا آهيو، اهو صبر سان انتظار ڪري رهيو آهي ته ڪيئن [۾] مؤثر طريقي سان توهان جي "بيوقوف" طريقي سان ڪم ڪرڻ جا طريقا اهڙي جوڙجڪ سان هوندا.

اچو ته عام مسئلن تي نظر رکون جيڪي پيدا ٿين ٿا، SQL ۾ انهن جي عمل درآمد، ۽ انهن جي ڪارڪردگي کي بهتر ڪرڻ جي ڪوشش ڪريو.

#1. خرگوش جو سوراخ ڪيترو اونهو آهي؟

اچو ته، يقيني طور تي، قبول ڪريو ته هي ڍانچي تنظيم جي جوڙجڪ ۾ شعبن جي ماتحت کي ظاهر ڪندو: ڊپارٽمينٽ، ڊويزن، شعبا، شاخون، ڪم ڪندڙ گروپ ... - جيڪو توهان انهن کي سڏيندا آهيو.

پهرين، اچو ته اسان جي 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;

اچو ته آسان ترين ڪم سان شروع ڪريون - سڀني ملازمن کي ڳولڻ جيڪي هڪ مخصوص شعبي ۾ ڪم ڪن ٿا، يا درجه بندي جي لحاظ کان - ھڪڙي نوڊ جي سڀني ٻارن کي ڳولھيو. اهو پڻ سٺو هوندو ته اولاد جي ”گهرائي“ حاصل ڪرڻ لاءِ ... اهو سڀ ڪجهه ضروري ٿي سگهي ٿو، مثال طور، ڪنهن قسم جي تعمير لاءِ. پيچيده چونڊ انهن ملازمن جي سڃاڻپ جي فهرست جي بنياد تي.

سڀ ڪجھ ٺيڪ هوندو جيڪڏهن انهن نسلن جا فقط ٻه درجا هجن ۽ تعداد درجن جي اندر هجي، پر جيڪڏهن 5 درجن کان وڌيڪ آهن ۽ اڳ ۾ ئي درجن وارا اولاد آهن ته مسئلا ٿي سگهن ٿا. اچو ته ڏسو ته ڪيئن روايتي هيٺيون-دي-وڻ جي ڳولا جا اختيار لکيا ويا آهن (۽ ڪم). پر پهرين، اچو ته اهو طئي ڪريون ته ڪهڙا نوڊس اسان جي تحقيق لاءِ سڀ کان وڌيڪ دلچسپ هوندا.

سڀ کان وڌيڪ "گہرا" ذيلي وڻ

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

سڀ کان وڌيڪ "وسيع" ذيلي وڻ

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

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

انهن سوالن لاءِ اسان عام استعمال ڪيو ٻيهر شامل ٿيڻ:


ظاهر آهي، هن درخواست جي ماڊل سان تکرارن جو تعداد اولاد جي ڪل تعداد سان ملندو (۽ انهن مان ڪيترائي درجن آهن)، ۽ اهو ڪافي اهم وسيلا وٺي سگهي ٿو، ۽ نتيجي طور، وقت.

اچو ته چيڪ ڪريون ”وڏيون“ سب ٽري:

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;

[explanation.tensor.ru تي ڏسو]

جيئن توقع ڪئي وئي، اسان سڀ 30 رڪارڊ مليا. پر انهن هن تي ڪل وقت جو 60 سيڪڙو خرچ ڪيو - ڇاڪاڻ ته انهن انڊيڪس ۾ 30 ڳولها پڻ ڪيون. ڇا اهو گهٽ ڪرڻ ممڪن آهي؟

انڊيڪس طرفان بلڪ پروف ريڊنگ

ڇا اسان کي هر نوڊ لاءِ الڳ انڊيڪس سوال ڪرڻ جي ضرورت آهي؟ اهو ظاهر ٿئي ٿو ته نه - اسان انڊيڪس مان پڙهي سگهون ٿا هڪ ئي وقت ۾ هڪ ڪال ۾ ڪيترائي ڪنجيون استعمال ڪرڻ مدد سان = ANY(array).

۽ سڃاڻپ ڪندڙن جي هر اهڙي گروپ ۾ اسان "نوڊس" ذريعي پوئين مرحلي ۾ مليل سڀئي IDs وٺي سگهون ٿا. اهو آهي، هر ايندڙ قدم تي اسين ڪنداسين ھڪڙي خاص سطح جي سڀني اولادن کي ھڪڙي وقت ۾ ڳولھيو.

بس، هتي ئي مسئلو آهي، ٻيهر ورجائي چونڊ ۾، توهان پاڻ کي nested سوال ۾ رسائي نٿا ڪري سگهو, پر اسان کي ڪنهن نه ڪنهن طريقي سان صرف اهو ئي چونڊڻو آهي جيڪو اڳئين سطح تي مليو هو... اهو ظاهر ٿيو ته اهو ناممڪن آهي ته پوري چونڊ لاءِ nested پڇا ڳاڇا ڪرڻ، پر ان جي مخصوص فيلڊ لاءِ اهو ممڪن آهي. ۽ هي فيلڊ پڻ هڪ صف ٿي سگهي ٿو - جيڪو اسان کي استعمال ڪرڻ جي ضرورت آهي ANY.

اهو ٿورڙو چريو لڳي ٿو، پر ڊراگرام ۾ سڀ ڪجهه سادو آهي.

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;

[explanation.tensor.ru تي ڏسو]

۽ هتي سڀ کان اهم شيء به نه آهي وقت ۾ 1.5 ڀيرا کٽيو، ۽ اهو ته اسان گهٽ بفرز کي ختم ڪيو، ڇاڪاڻ ته اسان وٽ صرف 5 ڪالون آهن انڊيڪس جي بدران 30!

هڪ اضافي بونس حقيقت اها آهي ته حتمي unnest کان پوء، سڃاڻپ ڪندڙ "سطحن" جي ترتيب سان رهندا.

نوڊ جي نشاني

ايندڙ غور جيڪو ڪارڪردگي کي بهتر بنائڻ ۾ مدد ڪندو آهي - "پتن" کي اولاد نٿو ٿي سگھي، اهو آهي، انهن لاءِ ”هيٺ“ ڏسڻ جي ڪا ضرورت ناهي. اسان جي ڪم جي ٺهڻ ۾، هن جو مطلب اهو آهي ته جيڪڏهن اسان شعبن جي زنجير تي عمل ڪيو ۽ هڪ ملازم تائين پهچي، ته پوء هن شاخ سان وڌيڪ ڏسڻ جي ضرورت ناهي.

اچو ته اسان جي ٽيبل ۾ داخل ٿيو اضافي boolean- ميدان، جيڪو اسان کي فوري طور تي ٻڌائيندو ته ڇا اسان جي وڻ ۾ هي خاص داخلا ”نوڊ“ آهي - اهو آهي ، ڇا اهو سڀ ڪجهه اولاد ٿي سگهي ٿو.

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

زبردست! اهو ظاهر ٿئي ٿو ته سڀني وڻن جي عناصرن مان صرف 30 سيڪڙو کان ٿورو وڌيڪ اولاد آهي.

ھاڻي اچو ته ٿورڙو مختلف ميڪيڪڪ استعمال ڪريون - ڪنيڪشن ذريعي ورجائيندڙ حصو سان LATERAL، جيڪو اسان کي فوري طور تي ٻيهر ورجائيندڙ "ٽيبل" جي شعبن تائين رسائي ڪرڻ جي اجازت ڏيندو، ۽ چيڪ جي سيٽ کي گهٽائڻ لاءِ نوڊ جي بنياد تي فلٽرنگ حالت سان گڏ مجموعي فنڪشن استعمال ڪريو:

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;

[explanation.tensor.ru تي ڏسو]

اسان هڪ وڌيڪ انڊيڪس ڪال کي گهٽائڻ جي قابل هئا ۽ مقدار ۾ 2 ڀيرا کان وڌيڪ کٽيو ثبوت پڙهڻ.

#2. اچو ته روٽ ڏانهن واپس وڃو

هي الگورٿم ڪارآمد ثابت ٿيندو جيڪڏهن توهان کي سڀني عنصرن لاءِ رڪارڊ گڏ ڪرڻ جي ضرورت آهي ”وڻ جي مٿان“، جڏهن ته ان معلومات کي برقرار رکڻ لاءِ ڪهڙي ماخذ جي شيٽ (۽ ڪهڙي اشارن سان) ان کي نموني ۾ شامل ڪرڻ جو سبب بڻيو - مثال طور، خلاصو رپورٽ ٺاهي نوڊس ۾ گڏ ٿيڻ سان.

هيٺ ڏنل شيء کي صرف تصور جي ثبوت جي طور تي ورتو وڃي، ڇو ته درخواست تمام پيچيده ٿي سگهي ٿي. پر جيڪڏهن اهو توهان جي ڊيٽابيس تي ڇانيل آهي، توهان کي ساڳي ٽيڪنالاجي استعمال ڪرڻ بابت سوچڻ گهرجي.

اچو ته ڪجھ سادي بيانن سان شروع ڪريون:

• ڊيٽابيس مان ساڳيو رڪارڊ اهو صرف هڪ ڀيرو پڙهڻ لاء بهترين آهي.
• ڊيٽابيس مان رڪارڊ اهو بيچ ۾ پڙهڻ لاء وڌيڪ موثر آهياڪيلو کان.

ھاڻي اچو ته ڪوشش ڪريون ان درخواست کي ٺاھڻ جو اسان کي ضرورت آھي.

قدم 1

ظاھر آھي، جڏھن ريڪرشن جي شروعات ڪندا آھيون (اسان ان کان سواءِ ڪٿي ھونداسين!) اسان کي پنن جا رڪارڊ پاڻ کي گھٽائڻا پوندا شروعاتي سڃاڻپ ڪندڙن جي سيٽ جي بنياد تي:

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

جيڪڏهن اهو ڪنهن کي عجيب لڳي ٿو ته "سيٽ" هڪ اسٽرنگ جي طور تي ذخيرو ٿيل آهي ۽ هڪ صف نه، پوء هن لاء هڪ سادي وضاحت آهي. تارن لاءِ هڪ تعمير ٿيل مجموعي ”گلونگ“ فنڪشن آهي string_agg، پر صفن لاءِ نه. جيتوڻيڪ هوء پنهنجو پاڻ تي عمل ڪرڻ آسان.

قدم 2

هاڻي اسان کي سيڪشن IDs جو هڪ سيٽ ملندو جنهن کي اڳتي پڙهڻ جي ضرورت پوندي. تقريبن هميشه اهي اصل سيٽ جي مختلف رڪارڊن ۾ نقل ڪيا ويندا - تنهنڪري اسان ڪنداسين ان کي گروپ ڪريو, جڏهن ته ذريعن جي پنن بابت معلومات محفوظ ڪرڻ.

پر هتي ٽي مصيبتون اسان جي انتظار ۾ آهن:

1. پڇا ڳاڇا جو "ذاتي" حصو مجموعي افعال تي مشتمل نه ٿو ٿي سگھي GROUP BY.
2. ٻيهر ورجائيندڙ ”ٽيبل“ جو حوالو نسٽڊ سبڪوري ۾ نٿو ٿي سگهي.
3. ٻيهر ورجائيندڙ حصي ۾ هڪ درخواست CTE تي مشتمل نه ٿي سگھي.

خوشقسمتيء سان، انهن سڀني مسئلن جي چوڌاري ڪم ڪرڻ بلڪل آسان آهي. اچو ته آخر کان شروع ڪريون.

CTE recursive حصو ۾

هن وانگر نه ڪم ڪرڻ:

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

۽ تنهنڪري اهو ڪم ڪري ٿو، قوسون فرق ڪن ٿا!

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

نراسائيندڙ "ٽيبل" جي خلاف نيسٽ ٿيل سوال

Hmm... هڪ ورجائيندڙ CTE هڪ ذيلي پڇا ڳاڇا ۾ رسائي نه ٿو ڪري سگھجي. پر اهو CTE اندر ٿي سگهي ٿو! ۽ هڪ nested درخواست اڳ ۾ ئي هن CTE تائين رسائي ڪري سگهو ٿا!

GROUP BY اندر جي ورهاڱي

اھو ناخوشگوار آھي، پر... اسان وٽ ھڪڙو سادو طريقو آھي ايميوليٽ ڪرڻ جو GROUP BY استعمال ڪندي DISTINCT ON ۽ ونڊو افعال!

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

۽ اهو ڪيئن ڪم ڪري ٿو!

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

هاڻي اسان ڏسون ٿا ته عددي ID کي متن ۾ ڇو تبديل ڪيو ويو - ته جيئن انهن کي ڪاما سان الڳ ڪري گڏ ڪري سگهجي!

قدم 3

فائنل لاءِ اسان وٽ ڪجھ به نه بچيو آھي:

• اسان پڙهون ٿا ”سيڪشن“ رڪارڊ گروپ ٿيل IDs جي هڪ سيٽ جي بنياد تي
• اسان ختم ڪيل حصن کي اصل شيٽ جي ”سيٽ“ سان ڀيٽيون ٿا
• "وڌايو" سيٽ اسٽرنگ استعمال ڪندي 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;

[explanation.tensor.ru تي ڏسو]

جو ذريعو: www.habr.com