PostgreSQL Query Profiler: כיצד להתאים תוכנית לשאילתה

רבים שכבר משתמשים explain.tensor.ru - ייתכן ששירות ההדמיה של תוכנית PostgreSQL שלנו אינו מודע לאחת מכוחות העל שלו - הפיכת חלק קשה לקריאה ביומן השרת...

... לשאילתה מעוצבת להפליא עם רמזים הקשריים לצמתי התוכנית המתאימים:

בתמליל זה של החלק השני שלו דיווח ב-PGConf.Russia 2020 אני אגיד לך איך הצלחנו לעשות את זה.

את התמליל של החלק הראשון, המוקדש לבעיות ביצועי שאילתות טיפוסיות ופתרונותיהן, ניתן למצוא במאמר "מתכונים לשאילתות SQL חולות".

ראשית, נתחיל לצבוע – וכבר לא נצבע את התוכנית, כבר צבענו אותה, כבר יש לנו אותה יפה ומובנת, אלא בקשה.

נראה היה לנו שעם "גיליון" כל כך לא מעוצב הבקשה שנשלפה מהיומן נראית מאוד מכוערת ולכן לא נוחה.


במיוחד כאשר מפתחים "מדביקים" את גוף הבקשה בקוד (זהו, כמובן, אנטי-תבנית, אבל זה קורה) בשורה אחת. מַחרִיד!

בואו נצייר את זה איכשהו יפה יותר.


ואם נוכל לצייר את זה בצורה יפה, כלומר לפרק ולהרכיב את גוף הבקשה, אז נוכל "לצרף" רמז לכל אובייקט של בקשה זו - מה קרה בנקודה המקבילה בתוכנית.

עץ תחביר שאילתות

לשם כך, תחילה יש לנתח את הבקשה.


בגלל שיש לנו ליבת המערכת פועלת על NodeJS, אז יצרנו מודול בשביל זה, אתה יכול מצא אותו ב-GitHub. למעשה, אלו הן "כריכות" מורחבות לחלק הפנימי של מנתח PostgreSQL עצמו. כלומר, הדקדוק פשוט מורכב מהידור בינארי ומבוצעות אליו כריכות מ-NodeJS. לקחנו את המודולים של אחרים כבסיס - אין כאן סוד גדול.

אנו מאכילים את גוף הבקשה כקלט לפונקציה שלנו - בפלט נקבל עץ תחביר ממנותח בצורה של אובייקט JSON.


עכשיו אנחנו יכולים לרוץ דרך העץ הזה בכיוון ההפוך ולהרכיב בקשה עם הזחות, צביעה ועיצוב שאנחנו רוצים. לא, זה לא ניתן להתאמה אישית, אבל נראה לנו שזה יהיה נוח.

מיפוי צמתי שאילתה ותוכנית

כעת נראה כיצד נוכל לשלב בין התוכנית שניתחנו בשלב הראשון לבין השאילתה שניתחנו בשלב השני.

ניקח דוגמה פשוטה - יש לנו שאילתה שמייצרת CTE וקוראת ממנו פעמיים. הוא מייצר תוכנית כזו.

CTE

אם אתה מסתכל על זה בזהירות, עד גרסה 12 (או מתחילים ממנה עם מילת המפתח 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 ...
)
...

כאשר משווים, עליך להבין זאת. להבין את זה "בעיניים" - אפילו לראות את התוכנית, אפילו לראות את גוף הבקשה - קשה מאוד. אם דור ה-CTE שלך מורכב, מקונן ויש בקשות גדולות, אז זה לגמרי לא מודע.

אגוד

אם יש לנו מילת מפתח בשאילתה 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 "מפה" ל-SRFs באותו שם.

אבל עם שאילתות מקוננות הכל מסובך יותר - למרבה הצער, הן לא תמיד הופכות 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. ננסה לשלב כך, ננסה לשלב הפוך, וכך הלאה עד שייגמרו לנו זוגות כאלה.

בואו ניקח את עץ התחביר שלנו, ניקח את התוכנית שלנו, נסתכל עליהם... לא דומים!


בואו נצייר את זה מחדש בצורה של גרפים - הו, זה כבר נראה כמו משהו!


נשים לב שיש לנו צמתים שיש להם בו זמנית ילדים ב' ו-ג' - לא אכפת לנו באיזה סדר. בואו נשלב אותם ונהפוך את התמונה של הצומת.


בואו נסתכל שוב. כעת יש לנו צמתים עם ילדים A וזוגות (B + C) - תואמים גם אליהם.


גדול! מסתבר שאנחנו השניים האלה JOIN מהבקשה עם התוכנית צמתים שולבו בהצלחה.

אבוי, בעיה זו לא תמיד נפתרת.


למשל, אם במסגרת בקשה A JOIN B JOIN C, ובתכנית קודם כל התחברו הצמתים ה"חיצוניים" A ו- C. אבל אין מפעיל כזה בבקשה, אין לנו מה להדגיש, אין מה לצרף רמז. זה אותו דבר עם ה"פסיק" כשאתה כותב A, B.

אבל, ברוב המקרים, כמעט כל הצמתים ניתנים ל"התרת קשר" ותוכלו לקבל סוג זה של פרופיל בצד שמאל בזמן - פשוטו כמשמעו, כמו בגוגל כרום כשאתם מנתחים קוד JavaScript. אתה יכול לראות כמה זמן לקח כל שורה וכל הצהרה "להוציא לפועל".


וכדי שיהיה לך נוח יותר להשתמש בכל זה, יצרנו אחסון ארכיון, שבו תוכל לשמור ולמצוא מאוחר יותר את התוכניות שלך יחד עם בקשות הקשורות או לשתף את הקישור עם מישהו.

אם אתה רק צריך להביא שאילתה בלתי קריאה לצורה נאותה, השתמש ה"נרמליזר" שלנו.

מקור: www.habr.com