آپ کو یاد ہے کہ یہ سب کیسے شروع ہوا؟ سب کچھ پہلی بار اور بار بار تھا۔

اس بارے میں کہ ہمیں PostgreSQL استفسار کو کس طرح بہتر بنانا تھا اور اس سے کیا نکلا۔
آپ کو کیوں کرنا پڑا؟ ہاں، کیونکہ پچھلے 4 سالوں سے سب کچھ خاموشی سے، سکون سے، گھڑی کی ٹک ٹک کی طرح کام کر رہا تھا۔
ایک ایپی گراف کے طور پر۔

حقیقی واقعات پر مبنی۔
تمام نام بدل گئے ہیں، اتفاقات بے ترتیب ہیں۔

جب آپ کوئی خاص نتیجہ حاصل کرتے ہیں، تو یہ یاد رکھنا ہمیشہ دلچسپ ہوتا ہے کہ آغاز کا محرک کیا تھا، جہاں سے یہ سب شروع ہوا۔

لہذا، اس کے نتیجے میں کیا ہوا مختصر طور پر مضمون میں بیان کیا گیا ہے "PostgreSQL کارکردگی کو بہتر بنانے کے طریقوں میں سے ایک کے طور پر ترکیب'.

پچھلے واقعات کے سلسلے کو دوبارہ بنانا شاید دلچسپ ہوگا۔
تاریخ نے صحیح آغاز کی تاریخ محفوظ کی ہے - 2018-09-10 18:02:48۔
اس کے علاوہ، کہانی میں ایک درخواست ہے جس سے یہ سب شروع ہوا:
مسئلہ کی درخواستکا انتخاب کریں
p. "PARAMETER_ID" بطور parameter_id،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber،
w. "LRM" AS LRM،
w. "LOTID" AS lotid،
w.“RTD_VALUE” AS RTD_value،
w."LOWER_SPEC_LIMIT" AS low_spec_limit،
w."UPPER_SPEC_LIMIT" AS upper_spec_limit،
p"TYPE_CALCUL" AS type_calcul،
s."SPENT_NAME" AS spent_name،
s۔"SPENT_DATE" بطور خرچ_تاریخ،
اقتباس ("SPENT_DATE" سے سال) AS سال،
اقتباس ("SPENT_DATE" سے مہینہ) بطور مہینے،
s."REPORT_NAME" AS رپورٹ_نام،
p"STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" AS customerparam_name
wdata w سے،
خرچ کیا،
پی ایم ٹی آر پی،
spent_pd sp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
اور p."PARAMETER_ID" = w."PARAMETER_ID"
اور s۔"SPENT_ID" = sp۔"SPENT_ID"
اور pd."PD_ID" = sp"PD_ID"
AND s. “SPENT_DATE” >= '2018-07-01' اور s. “SPENT_DATE” <= '2018-09-30'
اور s۔"SPENT_DATE" = (SELECT MAX(s2. "SPENT_DATE")
خرچ کردہ s2 سے،
wdata w2
WHERE s2."SPENT_ID" = w2۔"SPENT_ID"
اور w2. "LRM" = w. "LRM")؛

مسئلہ کی تفصیل متوقع طور پر معیاری ہے - "سب کچھ خراب ہے۔ بتاؤ کیا مسئلہ ہے؟"
مجھے فوری طور پر ساڑھے 3 انچ کی ڈرائیو کے وقت کا ایک واقعہ یاد آگیا:

لامر ہیکر کے پاس آتا ہے۔
میرے لیے کچھ بھی کام نہیں کرتا، مجھے بتائیں کہ مسئلہ کہاں ہے؟
ڈی این اے میں...

لیکن ظاہر ہے، کارکردگی کے واقعات کو حل کرنے کا یہ طریقہ نہیں ہے۔ "ہو سکتا ہے وہ ہمیں سمجھ نہ سکیں"(کے ساتھ)۔ ہمیں اس کا پتہ لگانے کی ضرورت ہے۔
ٹھیک ہے، آئیے کھودتے ہیں۔ شاید اس کے نتیجے میں کچھ جمع ہوجائے۔

تفتیش شروع کردی

لہذا، کھلی آنکھ سے فوری طور پر کیا دیکھا جا سکتا ہے، یہاں تک کہ وضاحت کا سہارا لئے بغیر.
1) JOIN استعمال نہیں کیا جاتا ہے۔ یہ برا ہے، خاص طور پر اگر کنکشن کی تعداد ایک سے زیادہ ہو۔
2) لیکن جو چیز اس سے بھی بدتر ہے وہ مربوط ذیلی سوالات ہیں، مزید یہ کہ جمع کے ساتھ۔ یہ بہت بری بات ہے۔
یہ یقیناً برا ہے۔ لیکن یہ صرف ایک طرف ہے۔ دوسری طرف، یہ بہت اچھا ہے، کیونکہ مسئلہ واضح طور پر ایک حل اور درخواست ہے جسے بہتر بنایا جا سکتا ہے.
کسی مستقبل کا بتانے والے (C) کے پاس نہ جائیں۔
استفسار کا منصوبہ اتنا پیچیدہ نہیں ہے، لیکن یہ کافی اشارہ ہے:
عمل درآمد کا منصوبہ

سب سے زیادہ دلچسپ اور مفید، ہمیشہ کی طرح، شروع اور آخر میں ہے.
نیسٹڈ لوپ (قیمت=935.84..479763226.18 قطاریں=3322 چوڑائی=135) (اصل وقت=31.536..8220420.295 قطاریں=8111656 لوپس=1)
منصوبہ بندی کا وقت: 3.807 ایم ایس
عملدرآمد کا وقت: 8222351.640 ms
تکمیل کا وقت 2 گھنٹے سے زیادہ ہے۔

غلط مفروضے جن میں وقت لگا

مفروضہ 1 - اصلاح کرنے والا غلطی کرتا ہے اور غلط منصوبہ بناتا ہے۔

عمل درآمد کے منصوبے کو دیکھنے کے لیے، ہم سائٹ کا استعمال کریں گے۔ https://explain.depesz.com/. تاہم، سائٹ نے کوئی دلچسپ یا مفید چیز نہیں دکھائی۔ پہلی اور دوسری نظر میں، ایسی کوئی چیز نہیں ہے جو واقعی مدد کر سکے۔ کیا یہ ممکن ہے کہ مکمل اسکین کم سے کم ہو۔ آگے بڑھو.

مفروضہ 2-آٹو ویکیوم کی طرف سے بنیاد پر اثر، آپ کو بریک سے چھٹکارا حاصل کرنے کی ضرورت ہے.

لیکن آٹو ویکیوم ڈیمونز اچھی طرح سے برتاؤ کرتے ہیں، کوئی طویل مدتی عمل نہیں ہوتا ہے۔ کوئی سنجیدہ بوجھ نہیں۔ ہمیں کچھ اور تلاش کرنے کی ضرورت ہے۔

مفروضہ 3 - اعداد و شمار پرانے ہیں، ہر چیز کو دوبارہ شمار کرنے کی ضرورت ہے۔

دوبارہ، ایسا نہیں۔ اعداد و شمار تازہ ترین ہیں۔ جو، آٹو ویکیوم کے ساتھ مسائل کی کمی کو دیکھتے ہوئے، حیرت انگیز نہیں ہے.

آئیے اصلاح کرنا شروع کریں۔

مین ٹیبل 'wdata' یقینی طور پر چھوٹا نہیں ہے، تقریباً 3 ملین ریکارڈز۔
اور یہ وہ جدول ہے جس کی پیروی مکمل اسکین کرتی ہے۔

ہیش کنڈ: ((w."SPENT_ID" = s."SPENT_ID") اور ((SubPlan 1) = s."SPENT_DATE"))
-> سیک اسکین ڈبلیو ڈیٹا پر
ہم معیاری کام کرتے ہیں: "چلو، ایک انڈیکس بنائیں اور سب کچھ اڑ جائے گا۔"
"SPENT_ID" فیلڈ پر ایک انڈیکس بنایا
اس کے نتیجے میں:
انڈیکس کا استعمال کرتے ہوئے عمل درآمد کے منصوبے سے استفسار کریں۔

ٹھیک ہے، کیا اس نے مدد کی؟
تھا: 8 222 351.640 ms (2 گھنٹے سے تھوڑا زیادہ)
بن گیا: 6 985 431.575 ms (تقریباً 2 گھنٹے)
عام طور پر، ایک ہی سیب، طرف نقطہ نظر.
آئیے کلاسیکی یاد رکھیں:
"کیا آپ کے پاس وہی ہے، لیکن پروں کے بغیر؟ تلاش کریں گے۔"

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

اور اب سب سے دلچسپ بات - ہم اصلاح کرتے رہتے ہیں، ہم درخواست کو پالش کریں گے۔

پہلا مرحلہ - جوائن کا استعمال کریں۔

دوبارہ لکھی گئی درخواست اب اس طرح نظر آتی ہے (ٹھیک ہے کم از کم زیادہ خوبصورت):
JOIN کا استعمال کرتے ہوئے استفسار کریں۔کا انتخاب کریں
p. "PARAMETER_ID" بطور parameter_id،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber،
w. "LRM" AS LRM،
w. "LOTID" AS lotid،
w.“RTD_VALUE” AS RTD_value،
w."LOWER_SPEC_LIMIT" AS low_spec_limit،
w."UPPER_SPEC_LIMIT" AS upper_spec_limit،
p"TYPE_CALCUL" AS type_calcul،
s."SPENT_NAME" AS spent_name،
s۔"SPENT_DATE" بطور خرچ_تاریخ،
اقتباس ("SPENT_DATE" سے سال) AS سال،
اقتباس ("SPENT_DATE" سے مہینہ) بطور مہینے،
s."REPORT_NAME" AS رپورٹ_نام،
p"STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" AS customerparam_name
اندرونی شمولیت کے wdata سے "SPENT_ID"=s.""SPENT_ID" پر خرچ کیا گیا
اندرونی شمولیت pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN spent_pd sp ON s. "SPENT_ID" = sp۔ "SPENT_ID"
اندرونی شمولیت pd pd ON pd. "PD_ID" = sp. "PD_ID"
کہاں
s. “SPENT_DATE” >= '2018-07-01' اور s. "SPENT_DATE" <= '2018-09-30'اور
s. "SPENT_DATE" = (منتخب MAX(s2. "SPENT_DATE")
W2 ڈیٹا سے W2 اندرونی شمولیت s2 پر w2۔“SPENT_ID”=sXNUMX۔“SPENT_ID”
اندرونی شمولیت wdata w
ON w2. "LRM" = w. "LRM" );
منصوبہ بندی کا وقت: 2.486 ایم ایس
عملدرآمد کا وقت: 1223680.326 ms

تو، پہلا نتیجہ.
تھا: 6 ms (تقریباً 985 گھنٹے)۔
بن گیا: 1 223 680.326 ms (صرف 20 منٹ سے زیادہ)۔
اچھا نتیجہ۔ اصولی طور پر، دوبارہ، ہم وہاں رک سکتے ہیں۔ لیکن یہ بہت غیر دلچسپ ہے، آپ روک نہیں سکتے۔
کے لیے

دوسرا مرحلہ - متعلقہ ذیلی سوالات سے چھٹکارا حاصل کریں۔

درخواست کا متن تبدیل کیا گیا:
متعلقہ ذیلی استفسار کے بغیرکا انتخاب کریں
p. "PARAMETER_ID" بطور parameter_id،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber،
w. "LRM" AS LRM،
w. "LOTID" AS lotid،
w.“RTD_VALUE” AS RTD_value،
w."LOWER_SPEC_LIMIT" AS low_spec_limit،
w."UPPER_SPEC_LIMIT" AS upper_spec_limit،
p"TYPE_CALCUL" AS type_calcul،
s."SPENT_NAME" AS spent_name،
s۔"SPENT_DATE" بطور خرچ_تاریخ،
اقتباس ("SPENT_DATE" سے سال) AS سال،
اقتباس ("SPENT_DATE" سے مہینہ) بطور مہینے،
s."REPORT_NAME" AS رپورٹ_نام،
p"STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" AS customerparam_name
اندرونی شمولیت کے wdata سے s ON s. "SPENT_ID" = w. "SPENT_ID"
اندرونی شمولیت pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN spent_pd sp ON s. "SPENT_ID" = sp۔ "SPENT_ID"
اندرونی شمولیت pd pd ON pd. "PD_ID" = sp. "PD_ID"
اندرونی شمولیت (w2 کو منتخب کریں۔"LRM"، MAX(s2."SPENT_DATE")
خرچ کردہ s2 سے INNER جوائن کریں wdata w2 ON s2۔ "SPENT_ID" = w2۔ "SPENT_ID"
گروپ بذریعہ w2۔"LRM"
) md on w. "LRM" = md. "LRM"
کہاں
s."SPENT_DATE" >= '2018-07-01' اور s."SPENT_DATE" <= '2018-09-30';
منصوبہ بندی کا وقت: 2.291 ایم ایس
عملدرآمد کا وقت: 165021.870 ms

تھا: 1 223 680.326 ms (صرف 20 منٹ سے زیادہ)۔
بن گیا: 165 021.870 ms (صرف 2 منٹ سے زیادہ)۔
یہ پہلے ہی کافی اچھا ہے۔
تاہم، جیسا کہ انگریز کہتے ہیں "لیکن، ہمیشہ ایک لیکن ہوتا ہے۔" ایک نتیجہ جو بہت اچھا ہے خود بخود شک کو جنم دے گا۔ یہاں کچھ گڑبڑ ہے۔

متعلقہ ذیلی استفسار سے جان چھڑانے کے لیے استفسار کو درست کرنے کے بارے میں مفروضہ درست ہے۔ لیکن حتمی نتیجہ درست ہونے کے لیے آپ کو اسے تھوڑا سا موافقت کرنے کی ضرورت ہے۔
نتیجے کے طور پر، پہلا انٹرمیڈیٹ نتیجہ:
مربوط ذیلی استفسار کے بغیر ترمیم شدہ استفسارکا انتخاب کریں
p. "PARAMETER_ID" بطور parameter_id،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber،
w. "LRM" AS LRM،
w. "LOTID" AS lotid،
w.“RTD_VALUE” AS RTD_value،
w."LOWER_SPEC_LIMIT" AS low_spec_limit،
w."UPPER_SPEC_LIMIT" AS upper_spec_limit،
p"TYPE_CALCUL" AS type_calcul،
s."SPENT_NAME" AS spent_name،
s۔"SPENT_DATE" بطور خرچ_تاریخ،
اقتباس (سال "SPENT_DATE" سے) بطور سال،
اقتباس (s. “SPENT_DATE” سے مہینہ) بطور مہینے،
s."REPORT_NAME" AS رپورٹ_نام،
p"STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" AS customerparam_name
اندرونی شمولیت کے wdata سے s ON s. "SPENT_ID" = w. "SPENT_ID"
اندرونی شمولیت pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN spent_pd sp ON s. "SPENT_ID" = sp۔ "SPENT_ID"
اندرونی شمولیت pd pd ON pd. "PD_ID" = sp. "PD_ID"
اندرونی شمولیت ( منتخب کریں w2. "LRM"، MAX(s2. "SPENT_DATE") بطور "SPENT_DATE"
خرچ کردہ s2 سے INNER جوائن کریں wdata w2 ON s2۔ "SPENT_ID" = w2۔ "SPENT_ID"
گروپ بذریعہ w2۔"LRM"
) md ON md. "SPENT_DATE" = s. "SPENT_DATE" اور md. "LRM" = w. "LRM"
کہاں
s."SPENT_DATE" >= '2018-07-01' اور s."SPENT_DATE" <= '2018-09-30';
منصوبہ بندی کا وقت: 3.192 ایم ایس
عملدرآمد کا وقت: 208014.134 ms

لہذا، ہم جس چیز کے ساتھ اختتام کرتے ہیں وہ پہلا قابل قبول نتیجہ ہے، جو گاہک کو دکھانے میں شرم کی بات نہیں ہے:
کے ساتھ شروع ہوا: 8 222 351.640 ms (2 گھنٹے سے زیادہ)
ہم حاصل کرنے میں کامیاب ہوئے: 1 ms (223 منٹ سے تھوڑا زیادہ)۔
نتیجہ (عبوری): 208 014.134 ms (صرف 3 منٹ سے زیادہ)۔

بہترین نتیجہ۔

کل

ہم وہاں رک سکتے تھے۔
لیکن…
بھوک کھانے سے لگتی ہے۔ جو چلتا ہے وہ سڑک پر عبور حاصل کرے گا۔ کوئی بھی نتیجہ انٹرمیڈیٹ ہے۔ رک گیا اور مر گیا۔ وغیرہ
آئیے اصلاح جاری رکھیں۔
عظیم خیال. خاص طور پر اس بات پر غور کرتے ہوئے کہ گاہک کو کوئی اعتراض بھی نہیں تھا۔ اور اس کے لیے سختی سے بھی۔

لہذا، ڈیٹا بیس کو دوبارہ ڈیزائن کرنے کا وقت آگیا ہے۔ استفسار کے ڈھانچے کو مزید بہتر نہیں کیا جاسکتا ہے (حالانکہ، جیسا کہ بعد میں پتہ چلا، اس بات کو یقینی بنانے کے لیے ایک آپشن موجود ہے کہ سب کچھ درحقیقت ناکام ہوجاتا ہے)۔ لیکن ڈیٹا بیس ڈیزائن کو بہتر بنانا اور تیار کرنا شروع کرنا پہلے سے ہی ایک بہت امید افزا خیال ہے۔ اور سب سے اہم دلچسپ۔ ایک بار پھر، اپنی جوانی کو یاد کریں۔ میں فوری طور پر DBA نہیں بن گیا، میں ایک پروگرامر کے طور پر بڑا ہوا (BASIC، assembler، C، ڈبل پلس C، Oracle، plsql)۔ ایک دلچسپ موضوع، یقیناً، ایک الگ یادداشت کے لیے ؛-)۔
تاہم، آئیے پریشان نہ ہوں۔

اس طرح،

یا شاید تقسیم ہماری مدد کرے گی؟
سپوئلر - "ہاں، اس سے مدد ملی، بشمول کارکردگی کو بہتر بنانے میں۔"

لیکن یہ بالکل مختلف کہانی ہے...

جاری ہے…

ماخذ: www.habr.com