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

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

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

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

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

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

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


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

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


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

شجرة بناء جملة الاستعلام

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


لأن لدينا يعمل جوهر النظام على NodeJS، ثم قمنا بعمل وحدة نمطية لها ، يمكنك ذلك تجده على جيثب. في الواقع ، هذه "ارتباطات" ممتدة إلى العناصر الداخلية لمحلل 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

إذا كان لدينا كلمة رئيسية في الطلب 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-منع (ثم تظهر السمة 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 "Mapyatsya" على SRF الذي يحمل نفس الاسم.

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

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


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

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

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


لكن كل شيء بسيط هنا - مثل العقد مثل Limit, Sort, Aggregate, WindowAgg, Unique إنهم "يربطون" واحدًا برأس إلى المشغلين المطابقين في الطلب ، إذا كانوا هناك. لا توجد "نجوم" ولا صعوبات.

الانضمام

تنشأ الصعوبات عندما نريد الجمع 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. يمكنك أن ترى كم من الوقت "نفذ" كل سطر وكل عبارة.


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

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

المصدر: www.habr.com