🥇درک برنامه های جستجوی PostgreSQL حتی راحت تر

نیم سال پیش ارائه کردیم توضیح.tensor.ru - عمومی سرویس برای تجزیه و تجسم طرح های پرس و جو به PostgreSQL.

در ماه های گذشته درباره او ساخته ایم گزارش در PGConf.Russia 2020، خلاصه ای را تهیه کرد مقاله در مورد افزایش سرعت پرس و جوهای SQL بر اساس توصیه هایی که ارائه می دهد ... اما مهمتر از همه، ما بازخورد شما را جمع آوری کردیم و موارد استفاده واقعی را بررسی کردیم.

و اکنون ما آماده ایم تا در مورد فرصت های جدیدی صحبت کنیم که می توانید از آنها استفاده کنید.

پشتیبانی از فرمت های مختلف طرح

برنامه‌ریزی از لاگ، همراه با درخواست

مستقیماً از کنسول، کل بلوک را از خط با شروع کنید متن پرس و جو، با تمام فضاهای پیشرو:

        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 بدست آورد، در خروجی EXPLAIN (زمانی که از پارامتر 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 حتی راحت تر