پوسٹگری ایس کیو ایل اینٹی پیٹرنز: خرگوش کا سوراخ کتنا گہرا ہے؟ آئیے درجہ بندی سے گزرتے ہیں۔

پیچیدہ ERP سسٹمز میں بہت سے اداروں کی درجہ بندی کی نوعیت ہوتی ہے۔جب یکساں اشیاء قطار میں لگ جاتی ہیں۔ آباؤ اجداد کے رشتوں کا درخت - یہ انٹرپرائز کا تنظیمی ڈھانچہ ہے (یہ تمام شاخیں، محکمے اور ورک گروپ)، اور سامان کا کیٹلاگ، اور کام کے شعبوں، اور سیلز پوائنٹس کا جغرافیہ،...

اصل میں، کوئی نہیں ہے کاروباری آٹومیشن کے علاقے، جہاں نتیجہ کے طور پر کوئی درجہ بندی نہیں ہوگی۔ لیکن یہاں تک کہ اگر آپ "کاروبار کے لیے" کام نہیں کرتے ہیں، تب بھی آپ درجہ بندی کے تعلقات کا آسانی سے سامنا کر سکتے ہیں۔ یہ بہت اچھی بات ہے، یہاں تک کہ آپ کے خاندانی درخت یا شاپنگ سینٹر کے احاطے کا فرش پلان بھی ایک جیسا ہے۔

ایسے درخت کو DBMS میں ذخیرہ کرنے کے بہت سے طریقے ہیں، لیکن آج ہم صرف ایک آپشن پر توجہ مرکوز کریں گے:

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

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

اور جب آپ درجہ بندی کی گہرائیوں میں جھانک رہے ہیں، تو یہ صبر کے ساتھ یہ دیکھنے کا انتظار کر رہا ہے کہ اس طرح کے ڈھانچے کے ساتھ کام کرنے کے آپ کے "بے ہودہ" طریقے کتنے مؤثر ہوں گے۔

آئیے عام مسائل کو دیکھتے ہیں جو پیدا ہوتے ہیں، ایس کیو ایل میں ان کے نفاذ، اور ان کی کارکردگی کو بہتر بنانے کی کوشش کرتے ہیں۔

#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;

آئیے سب سے آسان کام کے ساتھ شروع کرتے ہیں - تمام ملازمین کو تلاش کرنا جو ایک مخصوص شعبے میں کام کرتے ہیں، یا درجہ بندی کے لحاظ سے - نوڈ کے تمام بچوں کو تلاش کریں۔. اولاد کی "گہرائی" حاصل کرنا بھی اچھا ہو گا... یہ سب کچھ ضروری ہو سکتا ہے، مثال کے طور پر، کسی قسم کی تعمیر کے لیے ان ملازمین کی IDs کی فہرست کی بنیاد پر پیچیدہ انتخاب.

اگر ان اولادوں کے صرف دو درجے ہوں اور تعداد درجن کے اندر ہو تو سب کچھ ٹھیک ہو جائے گا، لیکن اگر 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;

[explain.tensor.ru پر دیکھیں]

جیسا کہ توقع کی گئی تھی، ہمیں تمام 30 ریکارڈ مل گئے۔ لیکن انہوں نے اس پر کل وقت کا 60% صرف کیا - کیونکہ انہوں نے انڈیکس میں 30 تلاشیں بھی کیں۔ کیا کم کرنا ممکن ہے؟

انڈیکس کے لحاظ سے بلک پروف ریڈنگ

کیا ہمیں ہر نوڈ کے لیے الگ انڈیکس استفسار کرنے کی ضرورت ہے؟ یہ پتہ چلتا ہے کہ نہیں - ہم انڈیکس سے پڑھ سکتے ہیں۔ ایک کال میں ایک ساتھ کئی کلیدیں استعمال کرنا مدد کے ساتھ = ANY(array).

اور شناخت کنندگان کے ایسے ہر گروپ میں ہم پچھلے مرحلے میں پائی جانے والی تمام IDs کو "نوڈز" کے ذریعے لے سکتے ہیں۔ یعنی ہر اگلے قدم پر ہم کریں گے۔ ایک بار میں ایک مخصوص سطح کے تمام اولادوں کو تلاش کریں۔.

بس، یہاں مسئلہ ہے، بار بار آنے والے انتخاب میں، آپ خود کو نیسٹڈ استفسار تک رسائی حاصل نہیں کر سکتے, لیکن ہمیں کسی نہ کسی طرح صرف وہی منتخب کرنے کی ضرورت ہے جو پچھلی سطح پر پایا گیا تھا... یہ پتہ چلتا ہے کہ پورے انتخاب کے لیے نیسٹڈ استفسار کرنا ناممکن ہے، لیکن اس کے مخصوص فیلڈ کے لیے یہ ممکن ہے۔ اور یہ فیلڈ ایک صف بھی ہو سکتی ہے - جو ہمیں استعمال کرنے کی ضرورت ہے۔ 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;

[explain.tensor.ru پر دیکھیں]

اور یہاں سب سے اہم بات یہ بھی نہیں ہے۔ وقت میں 1.5 بار جیتیں۔، اور یہ کہ ہم نے کم بفرز کو منہا کیا، کیونکہ ہمارے پاس 5 کی بجائے صرف 30 کالیں ہیں۔

ایک اضافی بونس یہ حقیقت ہے کہ حتمی انسٹ کے بعد، شناخت کنندگان کو "سطحوں" کے مطابق ترتیب دیا جائے گا۔

نوڈ کا نشان

اگلا غور جو کارکردگی کو بہتر بنانے میں مدد کرے گا وہ ہے - "پتے" کے بچے نہیں ہو سکتےیعنی، ان کے لیے "نیچے" دیکھنے کی ضرورت نہیں ہے۔ اپنے کام کی تشکیل میں، اس کا مطلب یہ ہے کہ اگر ہم محکموں کے سلسلے کی پیروی کرتے ہیں اور ایک ملازم تک پہنچ جاتے ہیں، تو اس شاخ کے ساتھ مزید دیکھنے کی ضرورت نہیں ہے.

آئیے اپنے ٹیبل میں داخل ہوں۔ اضافی 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;

[explain.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 مرحلہ

اب ہمیں سیکشن آئی ڈیز کا ایک سیٹ ملے گا جسے مزید پڑھنے کی ضرورت ہوگی۔ تقریبا ہمیشہ وہ اصل سیٹ کے مختلف ریکارڈوں میں نقل کیے جائیں گے - تو ہم کریں گے ان کا گروپ بنائیں, ماخذ کی پتیوں کے بارے میں معلومات کو محفوظ کرتے ہوئے

لیکن یہاں تین مصیبتیں ہمارے منتظر ہیں:

1. استفسار کا "سبریکورسیو" حصہ اس کے ساتھ مجموعی افعال پر مشتمل نہیں ہو سکتا GROUP BY.
2. تکراری "ٹیبل" کا حوالہ کسی نیسٹڈ ذیلی سوال میں نہیں ہو سکتا۔
3. تکراری حصے میں درخواست میں CTE شامل نہیں ہو سکتا۔

خوش قسمتی سے، ان تمام مسائل کے ارد گرد کام کرنے کے لئے بہت آسان ہیں. آئیے آخر سے شروع کرتے ہیں۔

تکراری حصے میں CTE

یہاں تو کوئی کام:

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

اور اس طرح یہ کام کرتا ہے، قوسین سے فرق پڑتا ہے!

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

ایک بار بار آنے والی "ٹیبل" کے خلاف نیسٹڈ استفسار

ہمم... ایک بار بار آنے والے CTE تک کسی ذیلی سوال میں رسائی نہیں کی جا سکتی۔ لیکن یہ CTE کے اندر ہو سکتا ہے! اور نیسٹڈ درخواست پہلے ہی اس 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;

[explain.tensor.ru پر دیکھیں]

ماخذ: www.habr.com