🥇מה EXPLAIN לא מספר לכם, ואיך לגרום לו לדבר

השאלה הקלאסית שמפתח שואל את מנהל ה-DBA שלו או בעל עסק שואל יועץ PostgreSQL היא כמעט תמיד זהה: "מדוע שאילתות לוקחות כל כך הרבה זמן לביצוע במסד הנתונים?"

קבוצה מסורתית של סיבות:

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

ואם חסימה קשה מספיק לתפיסה ולניתוח, אז לכל השאר אנחנו צריכים רק תוכנית שאילתה, אשר ניתן להשיג באמצעות אופרטור EXPLAIN (עדיף, כמובן, להסביר מיד (לנתח, לחסום)...) או מודול auto_explain.

אבל, כפי שצוין באותו תיעוד,

"הבנת תוכנית היא אמנות, וכדי לשלוט בה נדרשת מידה מסוימת של ניסיון,..."

אבל אפשר להסתדר בלעדיו אם משתמשים בכלי הנכון!

איך נראית בדרך כלל תוכנית שאילתה? משהו כזה:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

או ככה:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

אבל קריאת תוכנית מאפס היא קשה מאוד ולא אינטואיטיבית:

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

כשניסינו להסביר את כל זה לכמה מאות מהמפתחים שלנו, הבנו שמבחוץ זה נראה בערך כך:

וזה אומר שאנחנו צריכים…

כלי

ניסינו לאסוף בו את כל המכניקות המרכזיות שעוזרות לנו להבין, בהתבסס על תוכנית ובקשה, "מי אשם ומה לעשות". וכמובן, שיתפנו חלק מהניסיון שלנו עם הקהילה.
להיפגש ולהנות - explain.tensor.ru

נראות התוכניות

האם קל להבין תוכנית כשהיא נראית כך?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

לא ממש.

אבל ככה זה, בצורה מקוצרת, כאשר מפרידים בין האינדיקטורים המרכזיים, זה הרבה יותר ברור:

אבל אם התוכנית מסובכת יותר, תגיע עזרה תרשים עוגה של התפלגות זמן לפי צמתים:

ובכן, עבור האפשרויות הקשות ביותר, הוא ממהר להציל דיאגרמת ביצוע:

לדוגמה, ישנם מצבים לא טריוויאליים למדי שבהם לתוכנית עשוי להיות יותר משורש עובדתי אחד:

רמזים מבניים

ובכן, אם כל מבנה התוכנית ונקודות התורפה שלה כבר מסודרות ונראות לעין, מדוע לא להדגיש אותן בפני היזם ולהסביר אותן באנגלית פשוטה?

כבר אספנו כמה עשרות מתבניות ההמלצה האלה.

פרופיל שאילתות שורה אחר שורה

כעת, אם תניחו את השאילתה המקורית על התוכנית המנותחת, תוכלו לראות כמה זמן הושקע בכל אופרטור בנפרד - משהו כזה:

...או אפילו ככה:

החלפת פרמטרים בשאילתה

אם צירפת לתוכנית לא רק את השאילתה אלא גם את הפרמטרים שלה משורת ה-DETAIL של היומן, תוכל להעתיק אותה בנוסף באמצעות אחת מהאפשרויות הבאות:

עם החלפת ערכים בשאילתה
לביצוע ישיר על בסיס משלו וליצירת פרופיל נוסף
```
SELECT 'const', 'param'::text;
```
עם החלפת ערכים דרך PREPARE/EXECUTE
כדי לחקות את עבודת המתזמן, כאשר ניתן להתעלם מהחלק הפרמטרי - לדוגמה, בעת עבודה על טבלאות מחולקות למחיצות
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```