PostgreSQL استفسار پروفائلر: پلان اور استفسار کو کیسے ملایا جائے۔

بہت سے جو پہلے ہی استعمال کر رہے ہیں۔ explain.tensor.ru - ہماری PostgreSQL پلان ویژولائزیشن سروس اپنی سپر پاورز میں سے کسی ایک سے واقف نہیں ہو سکتی ہے - سرور لاگ کے ایک مشکل سے پڑھنے والے ٹکڑے کو تبدیل کرنا...

... متعلقہ پلان نوڈس کے لیے سیاق و سباق کے اشارے کے ساتھ خوبصورتی سے ڈیزائن کردہ سوال میں:

اس کے دوسرے حصے کے اس نقل میں PGConf.Russia 2020 میں رپورٹ میں آپ کو بتاؤں گا کہ ہم نے یہ کیسے کیا۔

پہلے حصے کی نقل، عام استفسار کی کارکردگی کے مسائل اور ان کے حل کے لیے وقف، مضمون میں پایا جا سکتا ہے "بیمار SQL سوالات کے لیے ترکیبیں".

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

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


خاص طور پر جب ڈویلپرز درخواست کے باڈی کو کوڈ میں "گلو" کرتے ہیں (یہ یقیناً ایک اینٹی پیٹرن ہے، لیکن ایسا ہوتا ہے) ایک لائن میں۔ ہولناک!

آئیے اسے کسی اور خوبصورتی سے کھینچتے ہیں۔


اور اگر ہم اس کو خوبصورتی سے کھینچ سکتے ہیں، یعنی الگ کر سکتے ہیں اور درخواست کی باڈی کو ایک ساتھ واپس رکھ سکتے ہیں، تو پھر ہم اس درخواست کے ہر شے کے لیے ایک اشارہ "منسلک" کر سکتے ہیں - جو پلان میں اسی مقام پر ہوا تھا۔

استفسار نحوی درخت

ایسا کرنے کے لیے، درخواست کو پہلے پارس کرنا ضروری ہے۔


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

ہم درخواست کی باڈی کو اپنے فنکشن میں بطور ان پٹ فیڈ کرتے ہیں - آؤٹ پٹ پر ہمیں JSON آبجیکٹ کی شکل میں ایک پارس شدہ نحوی درخت ملتا ہے۔


اب ہم اس درخت کے ذریعے مخالف سمت میں چل سکتے ہیں اور انڈینٹ، رنگ اور فارمیٹنگ کے ساتھ درخواست جمع کر سکتے ہیں جو ہم چاہتے ہیں۔ نہیں، یہ حسب ضرورت نہیں ہے، لیکن ہمیں لگتا ہے کہ یہ آسان ہوگا۔

میپنگ استفسار اور پلان نوڈس

اب دیکھتے ہیں کہ ہم اس منصوبے کو کیسے جوڑ سکتے ہیں جس کا ہم نے پہلے مرحلے میں تجزیہ کیا تھا اور اس سوال کو جس کا ہم نے دوسرے مرحلے میں تجزیہ کیا تھا۔

آئیے ایک سادہ مثال لیتے ہیں - ہمارے پاس ایک سوال ہے جو CTE تیار کرتا ہے اور اسے دو بار پڑھتا ہے۔ وہ ایسا منصوبہ بناتا ہے۔

سی ٹی ای۔

اگر آپ اسے غور سے دیکھیں تو ورژن 12 تک (یا کلیدی لفظ کے ساتھ اس سے شروع ہوتا ہے۔ MATERIALIZED) تشکیل سی ٹی ای منصوبہ ساز کے لیے ایک مکمل رکاوٹ ہے۔.


اس کا مطلب ہے کہ اگر ہم درخواست میں کہیں سی ٹی ای جنریشن اور پلان میں کہیں نوڈ دیکھتے ہیں۔ CTE، پھر یہ نوڈس یقینی طور پر ایک دوسرے کے ساتھ "لڑتے" ہیں، ہم انہیں فوری طور پر جوڑ سکتے ہیں۔

نجمہ کے ساتھ مسئلہ: CTEs کو نیسٹ کیا جا سکتا ہے۔

بہت ناقص گھونسلے ہیں، اور یہاں تک کہ ایک ہی نام کے۔ مثال کے طور پر، آپ اندر کر سکتے ہیں CTE A بناؤ CTE X، اور اندر ایک ہی سطح پر CTE B دوبارہ کریں CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

موازنہ کرتے وقت، آپ کو یہ سمجھنا چاہیے۔ اس کو "اپنی آنکھوں سے" سمجھنا - یہاں تک کہ منصوبہ دیکھنا، یہاں تک کہ درخواست کے جسم کو بھی دیکھنا - بہت مشکل ہے۔ اگر آپ کی CTE نسل پیچیدہ ہے، نیسٹڈ ہے، اور درخواستیں بڑی ہیں، تو یہ مکمل طور پر بے ہوش ہے۔

UNION

اگر ہمارے پاس استفسار میں کوئی کلیدی لفظ ہے۔ UNION [ALL] (دو نمونوں میں شامل ہونے کا آپریٹر)، پھر پلان میں یہ یا تو نوڈ کے مساوی ہے۔ Append، یا کچھ Recursive Union.


جو "اوپر" اوپر ہے۔ UNION - یہ ہمارے نوڈ کی پہلی اولاد ہے، جو "نیچے" ہے - دوسرا۔ اگر کے ذریعے UNION ہمارے پاس ایک ہی وقت میں کئی بلاکس "گلے ہوئے" ہیں، پھر Append-ابھی بھی صرف ایک نوڈ ہوگا، لیکن اس میں دو نہیں بلکہ بہت سے بچے ہوں گے - جس ترتیب سے وہ چلتے ہیں، بالترتیب:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

نجمہ کے ساتھ مسئلہ: بار بار آنے والے نمونے لینے کی نسل کے اندر (WITH RECURSIVE) ایک سے زیادہ بھی ہو سکتا ہے۔ UNION. لیکن آخری کے بعد صرف آخری بلاک ہمیشہ تکراری ہوتا ہے۔ UNION. اوپر سب کچھ ایک ہے، لیکن مختلف ہے۔ UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

آپ کو اس طرح کی مثالوں کو "رکھنے" کے قابل ہونے کی بھی ضرورت ہے۔ اس مثال میں ہم دیکھتے ہیں۔ UNIONہماری درخواست میں 3 حصے تھے۔ اس کے مطابق، ایک UNION سے مساوی Append-نوڈ، اور دوسرے کو - Recursive Union.

پڑھنے لکھنے کا ڈیٹا

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

سوال کے نقطہ نظر سے، ہم نہیں جانتے کہ یہ ٹیبل ہے یا CTE، لیکن وہ ایک ہی نوڈ کے ذریعہ نامزد کیے گئے ہیں۔ RangeVar. اور "پڑھنے کی اہلیت" کے لحاظ سے، یہ نوڈس کا کافی حد تک محدود سیٹ بھی ہے:

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

ہم پلان اور استفسار کی ساخت جانتے ہیں، ہم بلاکس کی خط و کتابت جانتے ہیں، ہم اشیاء کے نام جانتے ہیں - ہم ایک سے ایک موازنہ کرتے ہیں۔


دوبارہ کام "ایک ستارے کے ساتھ". ہم درخواست لیتے ہیں، اس پر عمل کرتے ہیں، ہمارے پاس کوئی عرفی نام نہیں ہے - ہم اسے صرف ایک ہی CTE سے دو بار پڑھتے ہیں۔


ہم منصوبہ دیکھتے ہیں - کیا مسئلہ ہے؟ ہمارے پاس عرف کیوں تھا؟ ہم نے اسے حکم نہیں دیا۔ اسے اتنا "نمبر نمبر" کہاں سے ملے گا؟

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

دوسری کام "ایک ستارے کے ساتھ": اگر ہم تقسیم شدہ ٹیبل سے پڑھ رہے ہیں، تو ہمیں ایک نوڈ ملے گا۔ Append یا Merge Append, جو "بچوں" کی ایک بڑی تعداد پر مشتمل ہو گا، اور جن میں سے ہر ایک کسی نہ کسی طرح ہو گا۔ Scan'اوم ٹیبل سیکشن سے: Seq Scan, Bitmap Heap Scan یا Index Scan. لیکن، کسی بھی صورت میں، یہ "بچے" پیچیدہ سوالات نہیں ہوں گے - اس طرح سے ان نوڈس کو الگ کیا جا سکتا ہے۔ Append میں UNION.


ہم ایسی گرہوں کو بھی سمجھتے ہیں، انہیں "ایک ڈھیر میں" جمع کرتے ہیں اور کہتے ہیں: "ہر چیز جو آپ میگاٹیبل سے پڑھتے ہیں وہ یہاں اور درخت کے نیچے ہے۔".

"سادہ" ڈیٹا وصول کرنے والے نوڈس

Values Scan منصوبہ بندی میں مطابقت رکھتا ہے VALUES درخواست میں.

Result کے بغیر ایک درخواست ہے FROM جیسے SELECT 1. یا جب آپ نے جان بوجھ کر غلط اظہار کیا ہو۔ WHERE-block (پھر وصف ظاہر ہوتا ہے۔ One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan اسی نام کے SRFs کا "نقشہ"۔

لیکن گھریلو سوالات کے ساتھ سب کچھ زیادہ پیچیدہ ہے - بدقسمتی سے، وہ ہمیشہ تبدیل نہیں ہوتے ہیں۔ InitPlan/SubPlan. کبھی کبھی وہ بدل جاتے ہیں۔ ... Join یا ... Anti Joinخاص طور پر جب آپ کچھ لکھتے ہیں۔ WHERE NOT EXISTS .... اور یہاں ان کو یکجا کرنا ہمیشہ ممکن نہیں ہوتا ہے - پلان کے متن میں پلان کے نوڈس کے مطابق کوئی آپریٹرز نہیں ہیں۔

دوبارہ کام "ایک ستارے کے ساتھ": کچھ VALUES درخواست میں. اس صورت میں اور منصوبے میں آپ کو کئی نوڈس ملیں گے۔ Values Scan.


"نمبر شدہ" لاحقوں کو ایک دوسرے سے ممتاز کرنے میں مدد ملے گی - وہ بالکل اسی ترتیب میں شامل کیے گئے ہیں جس میں متعلقہ لاحقے پائے جاتے ہیں۔ VALUES- اوپر سے نیچے تک درخواست کے ساتھ بلاکس۔

ڈیٹا پراسیسنگ

ایسا لگتا ہے کہ ہماری درخواست میں ہر چیز کو حل کر دیا گیا ہے - جو کچھ بچا ہے وہ ہے۔ Limit.


لیکن یہاں سب کچھ آسان ہے - جیسے نوڈس Limit, Sort, Aggregate, WindowAgg, Unique درخواست میں متعلقہ آپریٹرز کو "نقشہ" دیں، اگر وہ وہاں موجود ہیں۔ یہاں کوئی "ستارے" یا مشکلات نہیں ہیں۔

شمولیت

جب ہم جوڑنا چاہتے ہیں تو مشکلات پیدا ہوتی ہیں۔ JOIN اپنے درمیان. یہ ہمیشہ ممکن نہیں ہے، لیکن یہ ممکن ہے.


استفسار پارسر کے نقطہ نظر سے، ہمارے پاس ایک نوڈ ہے۔ JoinExpr، جس کے بالکل دو بچے ہیں - بائیں اور دائیں اس کے مطابق، یہ وہی ہے جو آپ کے شامل ہونے کے "اوپر" ہے اور درخواست میں "نیچے" کیا لکھا ہے۔

اور منصوبہ بندی کے نقطہ نظر سے یہ دونوں بعض کی اولادیں ہیں۔ * Loop/* Join-نوڈ Nested Loop, Hash Anti Join,... - ایسا کچھ.

آئیے سادہ منطق استعمال کریں: اگر ہمارے پاس میزیں A اور B ہیں جو پلان میں ایک دوسرے کو "شامل" کرتی ہیں، تو درخواست میں وہ یا تو واقع ہوسکتے ہیں۔ A-JOIN-Bیا B-JOIN-A. آئیے اس طرح سے جوڑنے کی کوشش کرتے ہیں، آئیے دوسرے راستے کو یکجا کرنے کی کوشش کرتے ہیں، اور اسی طرح جب تک کہ ہم اس طرح کے جوڑے ختم نہ ہوجائیں۔

آئیے اپنا نحو کا درخت لیں، اپنا منصوبہ لیں، انہیں دیکھیں... ایک جیسے نہیں!


آئیے اسے گراف کی شکل میں دوبارہ بنائیں - اوہ، یہ پہلے سے ہی کچھ کی طرح لگتا ہے!


آئیے نوٹ کریں کہ ہمارے پاس نوڈس ہیں جن میں بیک وقت بچے B اور C ہوتے ہیں - ہمیں اس کی پرواہ نہیں ہے کہ کس ترتیب میں۔ آئیے ان کو یکجا کریں اور نوڈ کی تصویر کو پلٹ دیں۔


آئیے دوبارہ دیکھتے ہیں۔ اب ہمارے پاس بچوں کے A اور جوڑوں (B + C) کے ساتھ نوڈس ہیں - ان کے ساتھ بھی ہم آہنگ۔


زبردست! معلوم ہوا کہ ہم یہ دونوں ہیں۔ JOIN درخواست سے پلان نوڈس کو کامیابی کے ساتھ جوڑ دیا گیا۔

افسوس، یہ مسئلہ ہمیشہ حل نہیں ہوتا۔


مثال کے طور پر، اگر کسی درخواست میں A JOIN B JOIN C، اور منصوبہ میں، سب سے پہلے، "بیرونی" نوڈس A اور C منسلک تھے۔ لیکن درخواست میں ایسا کوئی آپریٹر نہیں ہے، ہمارے پاس نمایاں کرنے کے لیے کچھ نہیں ہے، کوئی اشارہ منسلک کرنے کے لیے نہیں ہے۔ جب آپ لکھتے ہیں تو "کوما" کے ساتھ بھی ایسا ہی ہوتا ہے۔ A, B.

لیکن، زیادہ تر معاملات میں، تقریباً تمام نوڈس کو "کھلایا" جا سکتا ہے اور آپ وقت پر بائیں طرف اس قسم کی پروفائلنگ حاصل کر سکتے ہیں - لفظی طور پر، جیسے گوگل کروم میں جب آپ JavaScript کوڈ کا تجزیہ کرتے ہیں۔ آپ دیکھ سکتے ہیں کہ ہر لائن اور ہر بیان کو "عمل درآمد" میں کتنا وقت لگا۔


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

اگر آپ کو صرف ناقابل پڑھے ہوئے سوال کو مناسب شکل میں لانے کی ضرورت ہے تو استعمال کریں۔ ہمارا "نارملائزر".

ماخذ: www.habr.com