PostgreSQL অ্যান্টিপ্যাটার্নস: খরগোশের গর্ত কতটা গভীর? এর শ্রেণীবিন্যাস মাধ্যমে যান

জটিল ইআরপি সিস্টেমে অনেক সত্ত্বা একটি শ্রেণিবদ্ধ প্রকৃতি আছেযখন সমজাতীয় বস্তুগুলি সারিবদ্ধ হয় পূর্বপুরুষ-বংশীয় সম্পর্কের গাছ - এটি এন্টারপ্রাইজের সাংগঠনিক কাঠামো (এই সমস্ত শাখা, বিভাগ এবং কাজের গ্রুপ), এবং পণ্যের ক্যাটালগ, এবং কাজের ক্ষেত্র এবং বিক্রয় পয়েন্টগুলির ভূগোল, ...

আসলে, সেখানে কেউ নেই ব্যবসা অটোমেশন এলাকা, যেখানে ফলস্বরূপ কোনো শ্রেণিবিন্যাস থাকবে না। কিন্তু আপনি "ব্যবসার জন্য" কাজ না করলেও, আপনি এখনও সহজেই শ্রেণীবদ্ধ সম্পর্কগুলির সম্মুখীন হতে পারেন। এটা ট্রাইট, এমনকি আপনার ফ্যামিলি ট্রি বা শপিং সেন্টারে প্রাঙ্গনের মেঝে পরিকল্পনা একই কাঠামো।

ডিবিএমএসে এই জাতীয় গাছ সংরক্ষণ করার অনেক উপায় রয়েছে, তবে আজ আমরা কেবল একটি বিকল্পের উপর ফোকাস করব:

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;

চলুন শুরু করা যাক সবচেয়ে সহজ কাজটি - একটি নির্দিষ্ট সেক্টরের মধ্যে কাজ করে এমন সমস্ত কর্মচারীদের খুঁজে বের করা, বা অনুক্রমের পরিপ্রেক্ষিতে - একটি নোডের সমস্ত শিশু খুঁজুন. এটি বংশধরের "গভীরতা" পেতেও ভাল হবে... এই সমস্ত কিছু প্রয়োজন হতে পারে, উদাহরণস্বরূপ, কিছু ধরণের নির্মাণের জন্য এই কর্মচারীদের আইডি তালিকার উপর ভিত্তি করে জটিল নির্বাচন.

যদি এই বংশধরদের মাত্র কয়েকটি স্তর থাকে এবং সংখ্যাটি এক ডজনের মধ্যে থাকে তবে সবকিছু ঠিক হবে, তবে যদি 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).

এবং এই ধরনের প্রতিটি আইডেন্টিফায়ার গ্রুপে আমরা "নোড" দ্বারা পূর্ববর্তী ধাপে পাওয়া সমস্ত আইডি নিতে পারি। অর্থাৎ, প্রতিটি পরবর্তী ধাপে আমরা করব একবারে একটি নির্দিষ্ট স্তরের সমস্ত বংশধরদের জন্য অনুসন্ধান করুন.

শুধু, এখানেই সমস্যা, পুনরাবৃত্ত নির্বাচনে, আপনি একটি নেস্টেড ক্যোয়ারীতে নিজেকে অ্যাক্সেস করতে পারবেন না, কিন্তু পূর্ববর্তী স্তরে যা পাওয়া গেছে তা আমাদেরকে কোনোভাবে নির্বাচন করতে হবে... দেখা যাচ্ছে যে সমগ্র নির্বাচনের জন্য একটি নেস্টেড ক্যোয়ারী করা অসম্ভব, কিন্তু এর নির্দিষ্ট ক্ষেত্রের জন্য এটি সম্ভব। এবং এই ক্ষেত্রটি একটি অ্যারেও হতে পারে - যা আমাদের ব্যবহার করতে হবে 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 অ্যাক্সেস করতে পারে!

পুনরাবৃত্তির ভিতরে গ্রুপ করে

এটা অপ্রীতিকর, কিন্তু... আমাদের কাছে 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

এখন আমরা দেখতে পাচ্ছি কেন সাংখ্যিক আইডিকে টেক্সটে পরিণত করা হয়েছিল - যাতে তারা কমা দিয়ে আলাদা করা যায়!

পইঠা 3

ফাইনালের জন্য আমাদের আর কিছুই বাকি নেই:

• আমরা গোষ্ঠীবদ্ধ আইডিগুলির সেটের উপর ভিত্তি করে "বিভাগ" রেকর্ড পড়ি
• আমরা মূল শীটগুলির "সেট" এর সাথে বিয়োগকৃত বিভাগগুলির তুলনা করি
• সেট-স্ট্রিং ব্যবহার করে "প্রসারিত করুন" 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