PostgreSQL Query Profiler: نحوه مطابقت طرح و پرس و جو

بسیاری از کسانی که در حال حاضر استفاده می کنند توضیح.tensor.ru - سرویس تجسم طرح PostgreSQL ما ممکن است از یکی از ابرقدرت های خود آگاه نباشد - تبدیل یک قطعه سخت خوان از گزارش سرور...

... به یک پرس و جو طراحی شده زیبا با نکات متنی برای گره های پلان مربوطه:

در این متن از قسمت دوم او گزارش در PGConf.Russia 2020 من به شما خواهم گفت که چگونه توانستیم این کار را انجام دهیم.

رونوشت قسمت اول که به مشکلات عملکرد پرس و جوی معمولی و راه حل های آنها اختصاص دارد، در مقاله موجود است "دستور العمل هایی برای سوالات SQL بیمار".

ابتدا رنگ آمیزی را شروع می کنیم - و دیگر طرح را رنگ نمی کنیم، ما قبلا آن را رنگ کرده ایم، ما قبلا آن را زیبا و قابل درک داریم، اما یک درخواست.

به نظر ما این بود که درخواستی که با یک "برگ" بدون قالب از لاگ خارج شده است بسیار زشت و در نتیجه ناخوشایند به نظر می رسد.


به خصوص زمانی که توسعه دهندگان بدنه درخواست را در کد (البته این یک آنتی الگو است، اما اتفاق می افتد) در یک خط "چسب" می کنند. ناگوار!

بیایید این را به نحوی زیباتر ترسیم کنیم.


و اگر بتوانیم این را به زیبایی ترسیم کنیم، یعنی بدنه درخواست را از هم جدا کرده و دوباره کنار هم قرار دهیم، سپس می توانیم به هر شیء این درخواست اشاره ای "ضمیمه" کنیم - آنچه در نقطه مربوطه در طرح اتفاق افتاده است.

درخت نحو پرس و جو

برای این کار ابتدا درخواست باید تجزیه شود.


چون داریم هسته سیستم روی NodeJS اجرا می شود، سپس ما یک ماژول برای آن ساختیم، شما می توانید آن را در GitHub پیدا کنید. در واقع، اینها "پیوندهای" توسعه یافته به درونیات خود تجزیه کننده PostgreSQL هستند. یعنی گرامر به سادگی باینری کامپایل شده و از NodeJS پیوندهایی برای آن ایجاد می شود. ما ماژول های افراد دیگر را به عنوان پایه در نظر گرفتیم - هیچ راز بزرگی در اینجا وجود ندارد.

ما بدنه درخواست را به عنوان ورودی تابع خود تغذیه می کنیم - در خروجی یک درخت نحو تجزیه شده به شکل یک شی JSON دریافت می کنیم.


حالا می‌توانیم از این درخت در جهت مخالف عبور کنیم و یک درخواست با تورفتگی‌ها، رنگ‌آمیزی و قالب‌بندی مورد نظرمان جمع‌بندی کنیم. نه، این قابل تنظیم نیست، اما به نظر ما این راحت خواهد بود.

نقشه برداری پرس و جو و گره های طرح

حال بیایید ببینیم چگونه می توانیم طرحی را که در مرحله اول تحلیل کردیم و پرس و جوی را که در مرحله دوم تجزیه و تحلیل کردیم ترکیب کنیم.

بیایید یک مثال ساده در نظر بگیریم - ما یک پرس و جو داریم که یک CTE تولید می کند و دو بار از آن می خواند. او چنین طرحی را ایجاد می کند.

CTE

اگر با دقت به آن نگاه کنید، تا نسخه 12 (یا از آن با کلمه کلیدی شروع کنید MATERIALIZED) تشکیل CTE یک مانع مطلق برای برنامه ریز است.


این بدان معناست که اگر یک نسل CTE در جایی در درخواست و یک گره در جایی در طرح مشاهده کنیم CTE، سپس این گره ها قطعاً با یکدیگر "مبارزه" می کنند ، می توانیم بلافاصله آنها را ترکیب کنیم.

مشکل با ستاره: CTE ها را می توان تودرتو کرد.

تودرتوهای بسیار ضعیف و حتی آنهایی به همین نام وجود دارد. به عنوان مثال، شما می توانید در داخل CTE A ساختن CTE X، و در همان سطح در داخل CTE B دوباره انجامش بده CTE X:

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

هنگام مقایسه، باید این را درک کنید. درک این "با چشمان شما" - حتی دیدن طرح، حتی دیدن بدن درخواست - بسیار دشوار است. اگر نسل CTE شما پیچیده، تودرتو و درخواست‌ها زیاد باشد، کاملاً ناخودآگاه است.

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.


ما همچنین چنین گره هایی را درک می کنیم، آنها را "در یک توده" جمع می کنیم و می گوییم:هر چیزی که از megatable می خوانید اینجا و پایین درخت است".

گره های دریافت داده "ساده".

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 در صورت وجود اپراتورهای مربوطه در درخواست، یک به یک "نقشه" را انجام دهید. هیچ "ستاره" یا مشکلی در اینجا وجود ندارد.

بپیوندید

وقتی می خواهیم با هم ترکیب کنیم، مشکلات پیش می آید 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 وقتی کد جاوا اسکریپت را تجزیه و تحلیل می‌کنید. می توانید ببینید که هر خط و هر دستور چقدر طول می کشد تا "اجرا شود".


و برای اینکه استفاده از همه اینها برای شما راحت تر باشد، ما ذخیره سازی را ایجاد کرده ایم بایگانی، جایی که می توانید برنامه های خود را به همراه درخواست های مرتبط ذخیره کنید و بعداً پیدا کنید یا پیوند را با شخصی به اشتراک بگذارید.

اگر فقط نیاز دارید که یک پرس و جوی ناخوانا را به شکل مناسبی بیاورید، از آن استفاده کنید "نرمال ساز" ما.

منبع: www.habr.com