🥇 فهم خطط استعلام PostgreSQL بسهولة أكبر

قبل نصف عام قدمنا شرح - عام خدمة لتحليل وتصور خطط الاستعلام إلى PostgreSQL.

لقد فعلنا بشأنه خلال الأشهر الماضية تقرير في PGConf.Russia 2020، أعد ملخصًا مقال عن تسريع استعلامات SQL بناءً على التوصيات التي يقدمها ... ولكن الأهم من ذلك أننا جمعنا تعليقاتك ونظرنا في حالات الاستخدام الحقيقية.

والآن نحن جاهزون لإخبارك بالميزات الجديدة التي يمكنك استخدامها.

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

التخطيط من السجل ، مع الطلب

مباشرة من وحدة التحكم ، نختار الكتلة بأكملها ، بدءًا من السطر بـ Query Text، مع جميع المساحات الرئيسية:

        Query Text: INSERT INTO  dicquery_20200604  VALUES ($1.*) ON CONFLICT (query)
                           DO NOTHING;
        Insert on dicquery_20200604  (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: dicquery_20200604_pkey
          Tuples Inserted: 1
          Conflicting Tuples: 0
          Buffers: shared hit=9 read=1 dirtied=1
          ->  Result  (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)

.. ورمي كل شيء منسوخًا مباشرة في الحقل للخطة ، دون فصل أي شيء:

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

JSON و YAML

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;

"[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "pg_class",
      "Alias": "pg_class",
      "Startup Cost": 0.00,
      "Total Cost": 1336.20,
      "Plan Rows": 13804,
      "Plan Width": 539,
      "Actual Startup Time": 0.006,
      "Actual Total Time": 1.838,
      "Actual Rows": 10266,
      "Actual Loops": 1,
      "Shared Hit Blocks": 646,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 5.135,
    "Triggers": [
    ],
    "Execution Time": 2.389
  }
]"

حتى مع الاقتباسات الخارجية ، كنسخ pgAdmin ، حتى بدون - نلقي في نفس المجال ، فإن الإخراج هو الجمال:

التصور المتقدم

وقت التخطيط / وقت التنفيذ

يمكنك الآن أن ترى بشكل أفضل أين ذهب الوقت الإضافي عند تنفيذ الاستعلام:

توقيت الإدخال / الإخراج

في بعض الأحيان ، يتعين عليك التعامل مع موقف ، من حيث الموارد ، يبدو أنه لم تتم قراءة وكتابة الكثير ، ولكن يبدو أن وقت التنفيذ كبير بشكل غير ملائم لسبب ما.

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

ولكن يمكن تحديده بشكل موثوق به تمامًا. الحقيقة هي أنه من بين خيارات التكوين لخادم PG هناك track_io_timing:

لتمكين عمليات الإدخال / الإخراج الموقوتة. يتم تعطيل هذا الإعداد افتراضيًا ، حيث يتطلب من نظام التشغيل الاستعلام باستمرار عن الوقت الحالي ، مما قد يؤدي إلى إبطاء الأمور بشكل كبير على بعض الأنظمة الأساسية. يمكنك استخدام الأداة المساعدة pg_test_timing لتقدير النفقات العامة للتوقيت على النظام الأساسي الخاص بك. يمكن الحصول على إحصائيات الإدخال / الإخراج من خلال طريقة عرض pg_stat_database ، في إخراج شرح (عند استخدام معلمة BUFFERS) ومن خلال طريقة العرض pg_stat_statements.

يمكن أيضًا تمكين هذا الخيار داخل جلسة محلية:

SET track_io_timing = TRUE;

حسنًا ، أفضل جزء الآن هو أننا تعلمنا فهم هذه البيانات وعرضها ، مع مراعاة جميع تحولات شجرة التنفيذ:

هنا يمكنك أن ترى أنه من بين 0.790 مللي ثانية من إجمالي وقت التنفيذ ، استغرقت 0.718 مللي ثانية قراءة صفحة واحدة من البيانات ، و 0.044 مللي ثانية - كتابتها ، وتم إنفاق 0.028 مللي ثانية فقط على جميع الأنشطة المفيدة الأخرى!

المستقبل مع PostgreSQL 13

للحصول على نظرة عامة كاملة على ما هو جديد ، راجع في مقال مفصل، ونتحدث على وجه التحديد عن التغييرات في الخطط.

مخازن التخطيط

تنعكس محاسبة الموارد المخصصة للمجدول في تصحيح آخر غير متعلق بـ pg_stat_statements. شرح مع خيار BUFFERS سيبلغ عن عدد المخازن المؤقتة المستخدمة أثناء مرحلة التخطيط:
 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

فرز تزايدي

في الحالات التي يلزم فيها الفرز بواسطة العديد من المفاتيح (k1 ، k2 ، k3 ...) ، يمكن للمخطط الآن الاستفادة من معرفة أن البيانات مرتبة بالفعل بواسطة العديد من المفاتيح الأولى (مثل k1 و k2). في هذه الحالة ، لا يمكنك إعادة فرز جميع البيانات مرة أخرى ، ولكن يمكنك تقسيمها إلى مجموعات متتالية بنفس قيم k1 و k2 ، و "إعادة الفرز" بواسطة المفتاح k3.

وبالتالي ، فإن الفرز بأكمله ينقسم إلى عدة عمليات فرز متتالية ذات حجم أصغر. هذا يقلل من حجم الذاكرة المطلوبة ، ويسمح لك أيضًا بإرجاع البيانات الأولى قبل اكتمال كل الفرز.
 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

تحسينات UI / UX

لقطات الشاشة في كل مكان!

الآن في كل علامة تبويب هناك فرصة بسرعة خذ لقطة شاشة من علامة التبويب إلى الحافظة لكامل عرض وعمق علامة التبويب - "البصر" أعلى اليمين:

في الواقع ، تم الحصول على معظم صور هذا المنشور بهذه الطريقة.

توصيات بشأن العقد

لا يوجد المزيد منها فقط ، ولكن يمكنك الحصول على كل منها اقرأ المقال بالتفصيلباتباع الرابط:

الإزالة من الأرشيف

لقد طلب البعض القدرة على ذلك حذف "تماما" حتى الخطط التي لم يتم نشرها في الأرشيف - من فضلك ، فقط انقر فوق الرمز المقابل:

حسنًا ، دعونا لا ننسى أن لدينا مجموعة الدعمحيث يمكنك كتابة تعليقاتك واقتراحاتك.

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

فهم خطط استعلام PostgreSQL بسهولة أكبر