הבנת תכניות שאילתות PostgreSQL ביתר נוחות

לפני חצי שנה הצגנו explain.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, או בלי - אנחנו זורקים את זה לאותו שדה, והפלט הוא יופי:

ויזואליזציה מתקדמת

זמן תכנון/זמן ביצוע

כעת תוכל לראות טוב יותר היכן הושקע הזמן הנוסף בביצוע השאילתה:

תזמון I/O

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

כאן עלינו לומר: "אה, כנראה שבאותו רגע הדיסק בשרת היה עמוס מדי, זו הסיבה שלקח כל כך הרבה זמן לקרוא!"אבל איכשהו זה לא מאוד מדויק...

אבל זה יכול להיקבע באופן אמין לחלוטין. העובדה היא שבין אפשרויות התצורה של שרת PG יש track_io_timing:

מאפשר תזמון של פעולות I/O. אפשרות זו מושבתת כברירת מחדל מכיוון שהיא דורשת שאילתה מתמדת במערכת ההפעלה לזמן הנוכחי, מה שעלול להאט משמעותית את הביצועים בפלטפורמות מסוימות. כדי להעריך את עלות התזמון בפלטפורמה שלך, אתה יכול להשתמש בכלי השירות pg_test_timing. ניתן לקבל נתונים סטטיסטיים של I/O דרך תצוגת pg_stat_database, בפלט EXPLAIN (כאשר נעשה שימוש בפרמטר BUFFERS) ודרך תצוגת pg_stat_statements.

ניתן להפעיל אפשרות זו גם בהפעלה מקומית:

SET track_io_timing = TRUE;

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

כאן אתה יכול לראות שמתוך 0.790ms מזמן הביצוע הכולל, 0.718ms נדרשו לקרוא דף נתונים אחד, 0.044ms נדרשו לכתוב אותו, ורק 0.028ms הושקעו על כל שאר הפעילויות השימושיות!

העתיד עם 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