🥇PostgreSQL سوال جو پروفائلر: ڪيئن ملائي منصوبا ۽ سوال

ڪيترائي جيڪي اڳ ۾ ئي استعمال ڪري رهيا آهن وضاحت.tensor.ru - اسان جي PostgreSQL پلان بصري سروس شايد ان جي هڪ سپر پاور کان واقف نه هجي - سرور لاگ جي هڪ سخت پڙهڻ واري ٽڪري کي ڦيرايو ...

... هڪ سهڻي طرح سان ٺهيل سوال ۾ لاڳاپيل پلان نوڊس لاءِ لاڳاپيل اشارن سان:

سندس ٻئي حصي جي هن نقل ۾ PGConf.Russia 2020 تي رپورٽ مان توهان کي ٻڌايان ٿو ته اسان اهو ڪيئن ڪيو.

پهرين حصي جو نقل، عام سوالن جي ڪارڪردگي جي مسئلن ۽ انهن جي حل لاء وقف، مضمون ۾ ڳولهي سگهجي ٿو "بيمار SQL سوالن لاءِ ترڪيبون".

پهرين، اچو ته رنگ ڏيڻ شروع ڪريون - ۽ اسان هاڻي منصوبي کي رنگ نه ڏينداسين، اسان اڳ ۾ ئي ان کي رنگ ڏنو آهي، اسان وٽ اڳ ۾ ئي خوبصورت ۽ سمجھڻ وارو آهي، پر هڪ درخواست.

اهو اسان کي لڳي ٿو ته اهڙي غير فارميٽ ٿيل "شيٽ" سان درخواست لاگ مان ڪڍيو ويو آهي تمام بدصورت ۽ ان ڪري ناگزير آهي.

خاص طور تي جڏهن ڊولپرز ڪوڊ ۾ درخواست جي جسم کي "گلو" ڪن ٿا (اهو، يقينا، هڪ مخالف نمونو آهي، پر اهو ٿئي ٿو). خوفناڪ!

اچو ته هن کي ڪجهه وڌيڪ خوبصورت انداز سان ٺاهيو.

۽ جيڪڏهن اسان هن کي سهڻي نموني ٺاهي سگهون ٿا، اهو آهي، جدا ڪرڻ ۽ درخواست جي جسم کي گڏ ڪري، پوء اسان هن درخواست جي هر اعتراض ڏانهن اشارو "منسلڪ" ڪري سگهون ٿا - منصوبي ۾ لاڳاپيل نقطي تي ڇا ٿيو.

پڇا ڳاڇا جو وڻ

هن کي ڪرڻ لاء، درخواست کي پهريون ڀيرو پارس ڪيو وڃي.

ڇاڪاڻ ته اسان وٽ آهي سسٽم جو مرڪز NodeJS تي هلندو آهي، پوءِ اسان ان لاءِ هڪ ماڊل ٺاهيو، توهان ڪري سگهو ٿا ان کي ڳوليو GitHub تي. حقيقت ۾، اهي وڌايا ويا آهن "بائنڊنگز" پاڻ پوسٽ گري ايس ايس ايل پيسر جي اندروني ڏانهن. اهو آهي، گرامر صرف بائنري مرتب ڪيو ويو آهي ۽ ان کي نوڊ جي ايس کان پابند ڪيو ويو آهي. اسان ٻين ماڻهن جي ماڊلز کي بنياد طور ورتو - هتي ڪو وڏو راز ناهي.

اسان درخواست جي جسم کي پنهنجي فنڪشن ۾ انپٽ جي طور تي فيڊ ڪندا آهيون - آئوٽ پٽ تي اسان کي JSON اعتراض جي صورت ۾ هڪ پارس ٿيل نحو جو وڻ ملندو آهي.

ھاڻي اسان ھن وڻ جي ذريعي ھلائي سگھون ٿا مخالف طرف ۽ ھڪڙي درخواست کي گڏ ڪري سگھون ٿا انگن اکرن، رنگن ۽ فارميٽ سان جيڪو اسان چاهيون ٿا. نه، اهو حسب ضرورت نه آهي، پر اهو اسان کي لڳي ٿو ته اهو آسان هوندو.

ميپنگ سوال ۽ پلان نوڊس

هاڻي اچو ته ڏسون ته اسان ان منصوبي کي ڪيئن گڏ ڪري سگهون ٿا جنهن جو اسان پهرين قدم ۾ تجزيو ڪيو ۽ سوال جنهن جو اسان ٻئي مرحلي ۾ تجزيو ڪيو.

اچو ته هڪ سادو مثال وٺون - اسان وٽ هڪ سوال آهي جيڪو CTE ٺاهي ٿو ۽ ان مان ٻه ڀيرا پڙهي ٿو. هو اهڙو منصوبو ٺاهي ٿو.

سي ٽي

جيڪڏهن توهان ان کي غور سان ڏسو، نسخو 12 تائين (يا ان کان شروع ٿيندڙ لفظ سان MATERIALIZED) ٺهڻ سي ٽي اي پلانر لاءِ هڪ مطلق رڪاوٽ آهي.

هن جو مطلب اهو آهي ته جيڪڏهن اسان ڏسون ٿا هڪ CTE نسل ڪٿي درخواست ۾ ۽ هڪ نوڊ ڪٿي منصوبي ۾ CTE، پوءِ اهي نوڊس ضرور هڪ ٻئي سان ”وڙهن“ ٿا، اسان انهن کي فوري طور تي گڏ ڪري سگهون ٿا.

ستاري سان مسئلو: CTEs nested ڪري سگهجي ٿو.

اُتي تمام خراب نستيون آهن، ۽ ساڳي نالي جا به. مثال طور، توهان اندر ڪري سگهو ٿا CTE A ٺاهيو CTE X، ۽ ساڳئي سطح تي اندر CTE B ان کي ٻيهر ڪريو CTE X:

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

جڏهن مقابلو، توهان کي اهو سمجهڻ گهرجي. ھن کي سمجھڻ ”پنھنجي اکين سان“ - ايستائين جو منصوبو ڏسڻ، حتي درخواست جي جسم کي ڏسڻ - تمام ڏکيو آھي. جيڪڏهن توهان جو CTE نسل پيچيده، اندريون، ۽ درخواستون وڏيون آهن، پوء اهو مڪمل طور تي بي خبر آهي.

يونين

جيڪڏهن اسان وٽ سوال ۾ هڪ لفظ آهي 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-node، ۽ ٻئي ڏانهن - 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'om ٽيبل سيڪشن مان: 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 ڏانهن.

پر nested سوالن سان سڀڪنھن شيء کي وڌيڪ پيچيدو آهي - بدقسمتي سان، اهي هميشه ۾ تبديل نه ڪندا آھن InitPlan/SubPlan. ڪڏهن ڪڏهن اهي بدلجي ويندا آهن ... Join يا ... Anti Joinخاص طور تي جڏهن توهان ڪجهه لکندا آهيو WHERE NOT EXISTS .... ۽ هتي اهو هميشه ممڪن ناهي ته انهن کي گڏ ڪرڻ - منصوبي جي متن ۾ ڪو به آپريٽرز نه آهن جيڪي منصوبي جي نوڊس سان لاڳاپيل آهن.

ٻيهر ڪم "هڪ ستاري سان": ڪجهه VALUES درخواست ۾. هن معاملي ۾ ۽ منصوبي ۾ توهان ڪيترن ئي نوڊس حاصل ڪندا Values Scan.

"نمبر ٿيل" لافڪس انهن کي هڪ ٻئي کان ڌار ڪرڻ ۾ مدد ڏين ٿا - اهي بلڪل ترتيب ۾ شامل ڪيا ويا آهن جنهن ۾ لاڳاپيل آهن. VALUES-مٿي کان هيٺ تائين درخواست سان گڏ بلاڪ.

ڊيٽا پروسيسنگ

اهو لڳي ٿو ته اسان جي درخواست ۾ سڀ ڪجهه ترتيب ڏني وئي آهي - اهو سڀ ڪجهه ڇڏي ويو آهي Limit.

پر هتي هر شيء سادو آهي - جهڙوڪ نوڊس Limit, Sort, Aggregate, WindowAgg, Unique درخواست ۾ لاڳاپيل آپريٽرز کي ”نقشو“ ون ٽو ون، جيڪڏھن اھي موجود آھن. هتي ڪو به "ستارو" يا مشڪلات نه آهي.

JOIN

مشڪلاتون پيدا ٿينديون آهن جڏهن اسان گڏ ڪرڻ چاهيون ٿا 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.

پر، اڪثر ڪيسن ۾، لڳ ڀڳ سڀئي نوڊس ”اُٿل“ ٿي سگهن ٿا ۽ توهان هن قسم جي پروفائيلنگ کي وقت ۾ کاٻي پاسي حاصل ڪري سگهو ٿا - لفظي طور تي، گوگل ڪروم ۾ جڏهن توهان جاوا اسڪرپٽ ڪوڊ جو تجزيو ڪيو ٿا. توهان ڏسي سگهو ٿا ته هر لڪير ۽ هر بيان کي "عمل" ۾ ڪيترو ڊگهو آهي.

۽ توهان کي اهو سڀ ڪجهه استعمال ڪرڻ لاء وڌيڪ آسان بڻائڻ لاء، اسان اسٽوريج ٺاهيو آهي آرڪائيو، جتي توهان محفوظ ڪري سگهو ٿا ۽ بعد ۾ ڳولي سگهو ٿا پنهنجا منصوبا لاڳاپيل درخواستن سان گڏ يا ڪنهن سان لنڪ شيئر ڪريو.

جيڪڏهن توهان کي صرف هڪ اڻ پڙهيل سوال کي مناسب فارم ۾ آڻڻ جي ضرورت آهي، استعمال ڪريو اسان جو "نارملائزر".

جو ذريعو: www.habr.com

PostgreSQL سوالي پروفائلر: ڪيئن ملائي منصوبا ۽ سوال