PostgreSQL Query Profiler: كيفية مطابقة الخطة والاستعلام

قد لا يدرك العديد ممن يستخدمون موقع expl.tensor.ru - خدمتنا لتصور خطط PostgreSQL - إحدى قدراتها الفائقة - لتحويل جزء يصعب قراءته من سجل الخادم ...





... في استعلام مصمم بشكل جيد مع تلميحات سياقية لعقد الخطة المقابلة:





في هذا النص للجزء الثاني من حديثي في ​​PGConf.Russia 2020 ، سأخبرك كيف تمكنا من القيام بذلك.

يمكن العثور على نص الجزء الأول ، الذي يتعامل مع مشكلات أداء الاستعلام النموذجية وحلولها ، في مقالة "وصفات لاستعلامات SQL المريضة" .

أولاً ، سوف نرسم - ولن نرسم الخطة بعد الآن ، لقد رسمناها بالفعل ، ولديناها بالفعل جميلة ومفهومة ، ولكنها طلب.



بدا لنا أن الاستعلام الذي تم سحبه من السجل باستخدام "ورقة" غير منسقة يبدو قبيحًا للغاية وبالتالي غير مريح.





خاصة عندما يقوم المطورون في الكود "بلصق" جسم الطلب (هذا بالطبع مضاد للنمط ، لكنه يحدث) في سطر واحد. رعب!



دعونا نرسمها بطريقة أكثر جمالاً.





وإذا استطعنا رسمه بشكل جميل ، أي تفكيك جسم الطلب وإعادة تجميعه ، فيمكننا بعد ذلك إرفاق تلميح لكل كائن من هذا الطلب - ما حدث في النقطة المقابلة في الخطة.

شجرة الاستعلام النحوية

للقيام بذلك ، يجب أولاً تحليل الطلب.





نظرًا لأن نظامنا الأساسي يعمل على NodeJS ، فقد صنعنا وحدات له ، يمكنك العثور عليه على GitHub . في الواقع ، هذه "ارتباطات" ممتدة إلى العناصر الداخلية لمحلل PostgreSQL نفسه. وهذا يعني أن القواعد يتم تجميعها ببساطة في نظام ثنائي ويتم إجراء عمليات ربط لها من جانب NodeJS. أخذنا وحدات الآخرين كأساس - لا يوجد سر كبير هنا.



نقوم بتغذية جسم الطلب بإدخال وظيفتنا - عند الإخراج نحصل على شجرة بناء جملة محللة في شكل كائن JSON.





يمكنك الآن المرور بهذه الشجرة في الاتجاه المعاكس وجمع الطلب مع المسافات البادئة والتلوين والتنسيق الذي نريده. لا ، إنه غير قابل للتكوين ، لكن بدا لنا أن هذا سيكون مناسبًا.

استعلام التعيين وعقد الخطة

لنرى الآن كيف يمكننا الجمع بين الخطة التي حللناها في الخطوة الأولى والاستعلام الذي حللناه في الخطوة الثانية.



لنأخذ مثالًا بسيطًا - لدينا طلبًا يولد CTE ويقرأه مرتين. يولد مثل هذه الخطة.

CTE

إذا نظرت إليه بعناية ، قبل الإصدار الثاني عشر (أو تبدأ منه بالكلمة الأساسية MATERIALIZED) ، فإن تشكيل CTE هو حاجز مطلق للمخطط .





هذا يعني أنه إذا رأينا توليد CTE في مكان ما في الطلب وفي مكان ما في الخطة CTE، فإن هذه العقد بالتأكيد "تقاتل" مع بعضها البعض ، فيمكننا دمجها على الفور.



مشكلة النجمة : يمكن أن تتداخل CTEs.



هناك تداخل سيء للغاية ، وحتى نفس الأسماء. على سبيل المثال ، يمكنك CTE Aالقيام بذلك في الداخل CTE X، والقيام CTE Bبذلك مرة أخرى على نفس المستوى من الداخل CTE X:

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

يجب أن تفهم هذا عند المقارنة. من الصعب جدًا فهم هذا "بالعيون" - حتى رؤية الخطة ، وحتى رؤية نص الطلب. إذا كان توليد الاعتلال الدماغي الرضحي المزمن لديك معقدًا ومتداخلاً والطلبات كبيرة - فهذا يعني أنه غير واع تمامًا.

اتحاد

إذا كانت لدينا كلمة أساسية في الاستعلام 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"الجزء من الجدول: 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"تعيين" إلى SRF الذي يحمل نفس الاسم.



ولكن مع الاستعلامات المتداخلة ، يصبح كل شيء أكثر تعقيدًا - لسوء الحظ ، لا تتحول دائمًا إلى InitPlan/ SubPlan. في بعض الأحيان يتحولون إلى ... Joinأو ... Anti Join، خاصة عندما تكتب شيئًا مثل WHERE NOT EXISTS .... وليس من الممكن دائمًا الدمج هناك - لا توجد عوامل تشغيل تتوافق مع عقد الخطة في نص الخطة.



مرة أخرى ، مهمة بعلامة النجمة : عدة VALUESفي الطلب. في هذه الحالة وفي الخطة ، ستتلقى عدة عقد Values Scan.





ستساعد اللواحق "المرقمة" على تمييزها عن بعضها البعض - تتم إضافتها بالضبط بترتيب العثور على VALUESالكتل المقابلة على طول الطلب من أعلى إلى أسفل.

معالجة البيانات

يبدو أنه تم فرز كل شيء في طلبنا - لم يتبق سوى واحد Limit.





ولكن كل ما هو بسيط - مثل العقد Limit، Sort، Aggregate، WindowAgg، Unique"mapyatsya" واحد الى واحد من البيانات المناظرة في الطلب، وإذا كانت هناك. لا توجد "نجوم" ولا صعوبات.

انضم

تنشأ الصعوبات عندما نريد أن نتحد مع JOINبعضنا البعض. هذا لا يتم دائمًا ، لكن يمكنك ذلك.





من وجهة نظر محلل الاستعلام ، لدينا عقدة JoinExprبها فرعين بالضبط - اليسار واليمين. هذا ، على التوالي ، هو ما هو "أعلاه" الخاص بك JOIN وما هو "تحته" في الطلب مكتوب.



ومن وجهة نظر الخطة ، فهذان سليلان لبعض * 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.



ولكن ، في معظم الحالات ، يمكن "فك جميع العقد" تقريبًا وتحصل على هذا النوع من التنميط على اليسار في الوقت المناسب - حرفيًا ، كما هو الحال في Google Chrome ، عند تحليل شفرة JavaScript. يمكنك أن ترى كم من الوقت تم "تنفيذ" كل سطر وكل عبارة.





ولجعل استخدام كل هذا أكثر ملاءمة لك ، قمنا بعمل تخزين للأرشيف ، حيث يمكنك الحفظ ثم العثور على خططك جنبًا إلى جنب مع الطلبات المرتبطة أو مشاركة رابط مع شخص ما.



إذا كنت تحتاج فقط إلى إحضار استعلام غير قابل للقراءة إلى نموذج مناسب ، فاستخدم أداة "normalizer" الخاصة بنا .