پرو ہوسٹر > بلاگ > انتظامیہ > پوسٹگری ایس کیو ایل اینٹی پیٹرنز: CTE x CTE

پوسٹگری ایس کیو ایل اینٹی پیٹرنز: CTE x CTE

میرے کام کی وجہ سے، مجھے ایسے حالات سے نمٹنا پڑتا ہے جب کوئی ڈویلپر درخواست لکھتا ہے اور سوچتا ہے کہ "بنیاد ہوشیار ہے، یہ خود ہی سب کچھ سنبھال سکتا ہے!«

بعض صورتوں میں (جزوی طور پر ڈیٹا بیس کی صلاحیتوں سے لاعلمی، جزوی طور پر قبل از وقت اصلاح سے)، یہ نقطہ نظر "Frankensteins" کے ظہور کا باعث بنتا ہے۔

سب سے پہلے، میں ایسی درخواست کی ایک مثال دوں گا:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

کسی درخواست کے معیار کا کافی حد تک جائزہ لینے کے لیے، آئیے کچھ صوابدیدی ڈیٹا سیٹ بنائیں:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

اس سے یہ نتیجہ نکلتا ہے کہ ڈیٹا کو پڑھنے میں ایک چوتھائی سے بھی کم وقت لگا استفسار پر عمل درآمد:

پوسٹگری ایس کیو ایل اینٹی پیٹرنز: CTE x CTE[explain.tensor.ru پر دیکھیں]

اسے ٹکڑے ٹکڑے کر کے الگ کرنا

آئیے درخواست پر گہری نظر ڈالیں اور حیران رہ جائیں:

  1. اگر کوئی تکراری CTEs نہیں ہیں تو یہاں WITH RECURSIVE کیوں ہے؟
  2. الگ سی ٹی ای میں کم سے کم/زیادہ سے زیادہ قدروں کو گروپ کیوں کریں اگر وہ پھر بھی اصل نمونے سے منسلک ہیں؟
    +25% وقت
  3. پچھلے CTE کو دہرانے کے لیے آخر میں غیر مشروط 'SELECT * FROM' کیوں استعمال کریں؟
    +14% وقت

اس معاملے میں، ہم بہت خوش قسمت تھے کہ کنکشن کے لیے Hash Join کا ​​انتخاب کیا گیا، نہ کہ Nested Loop، کیونکہ تب ہمیں صرف ایک CTE Scan پاس نہیں، بلکہ 10K مل جاتا!

سی ٹی ای اسکین کے بارے میں تھوڑا سایہاں ہمیں یہ یاد رکھنا چاہیے۔ سی ٹی ای اسکین سیق اسکین کی طرح ہے۔ - یعنی کوئی اشاریہ سازی نہیں، بلکہ صرف ایک مکمل تلاش، جس کی ضرورت ہوگی۔ 10K x 0.3ms = 3000ms سائیکل کے لیے بذریعہ cte_max یا 1K x 1.5ms = 1500ms cte_bind کے ذریعے لوپ کرتے وقت!
دراصل، آپ اس کے نتیجے میں کیا حاصل کرنا چاہتے تھے؟ ہاں، عام طور پر یہ وہ سوال ہے جو "تین منزلہ" سوالات کا تجزیہ کرنے کے 5ویں منٹ میں کہیں آتا ہے۔

ہم ہر منفرد کلیدی جوڑے کے لیے آؤٹ پٹ کرنا چاہتے تھے۔ key_a کے ذریعے گروپ سے کم از کم/زیادہ سے زیادہ.
تو آئیے اسے اس کے لیے استعمال کریں۔ ونڈو کے افعال:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

پوسٹگری ایس کیو ایل اینٹی پیٹرنز: CTE x CTE
[explain.tensor.ru پر دیکھیں]

چونکہ دونوں اختیارات میں ڈیٹا پڑھنے میں تقریباً 4-5ms لگتے ہیں، اس کے بعد ہمارا سارا وقت فائدہ ہوتا ہے۔ -32٪ - یہ اپنی خالص ترین شکل میں ہے۔ بیس CPU سے لوڈ ہٹا دیا گیا۔, اگر اس طرح کی درخواست کو کافی کثرت سے عمل میں لایا جاتا ہے۔

عام طور پر، آپ کو بیس کو "راؤنڈ ون لے جانے، مربع کو رول" کے لیے مجبور نہیں کرنا چاہیے۔

ماخذ: www.habr.com

نیا تبصرہ شامل کریں