יצירה וניהול של רמזים עם שמות

בדף הזה מוסבר איך ליצור ולנהל רמזים עם שם ב-AlloyDB ל-PostgreSQL.

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

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

ההערות בעלות השם ב-AlloyDB תומכות בכל ההערות מתוסף קוד פתוח pg_hint_plan.

בנוסף, AlloyDB תומך ברמזים הבאים למנוע מבוסס-עמודות:

• ‫ColumnarScan(table): כופה סריקה של עמודות בטבלה.
• ‫NoColumnarScan(table): השבתה של סריקת עמודות בטבלה.

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

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

תהליך עבודה

כדי להשתמש ברמזים עם שמות, צריך לבצע את השלבים הבאים:

1. מזהים את השאילתה שעבורה רוצים ליצור רמזים עם שם.
2. יוצרים רמזים עם שם עם רמזים שיוחלו בפעם הבאה שהשאילתה תופעל.
3. אימות ההחלה של הרמזים עם השמות.

בדף הזה נעזרים בטבלה ובאינדקס הבאים כדי להציג דוגמאות:

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

חשוב: אם השתמשתם בגרסה קודמת של התכונה 'רמזים עם שמות', צריך להסיר את התוסף הקודם google_auto_hints מכל מסדי הנתונים באמצעות הפקודה הבאה:

  DROP EXTENSION IF EXISTS google_auto_hints;

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

לפני שמתחילים

• מפעילים את התכונה 'רמזים עם שם' במופע. מגדירים את הדגל alloydb.enable_named_hints לערך on. אפשר להפעיל את הדגל הזה ברמה של כל השרת או ברמת הפעילות באתר. כדי לצמצם את התקורה שעלולה לנבוע משימוש בתכונה הזו, מומלץ להפעיל את הדגל הזה רק ברמת הסשן.

  מידע נוסף זמין במאמר בנושא הגדרת דגלים של מסד נתונים במופע.

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

  כדי לוודא שהדגל מופעל, מריצים את הפקודה show alloydb.enable_named_hints;. אם התכונה הניסיונית מופעלת, הפלט יחזיר 'on'.

• לכל מסד נתונים שבו רוצים להשתמש ברמזים עם שמות, יוצרים תוסף במסד הנתונים ממופע AlloyDB הראשי בתור המשתמש alloydbsuperuser או המשתמש postgres:

  CREATE EXTENSION google_auto_hints CASCADE;
  

התפקידים הנדרשים

כדי לקבל את ההרשאות שדרושות ליצירה ולניהול של רמזים עם שם, צריך לבקש מהאדמין להקצות לכם את התפקידים הבאים בניהול זהויות והרשאות גישה (IAM):

• תפקיד alloydbsuperuser

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

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

זיהוי השאילתה

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

כדי לזהות את מזהה השאילתה, אפשר להשתמש בשיטות הבאות:

• מריצים את הפקודה EXPLAIN (VERBOSE), כמו בדוגמה הבאה:

  EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                          QUERY PLAN
  ----------------------------------------------------------
  Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
    Output: a, b
    Filter: (t.a = 99)
  Query Identifier: -6875839275481643436
  

  בפלט, מזהה השאילתה הוא -6875839275481643436.

• שליחת שאילתה לתצוגה pg_stat_statements.

  אם הפעלתם את התוסף pg_stat_statements, תוכלו למצוא את מזהה השאילתה על ידי שליחת שאילתה לתצוגה pg_stat_statements, כמו בדוגמה הבאה:

  select query, queryid from pg_stat_statements;
  

יצירת רמזים עם שם

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

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

מחליפים את מה שכתוב בשדות הבאים:

• ‫HINTS_NAME: שם לרמזים עם שם. הערך הזה חייב להיות ייחודי במסד הנתונים.

• ‫SQL_ID (אופציונלי): מזהה השאילתה של השאילתה שעבורה יוצרים את הרמזים עם השם.

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

• SQL_TEXT (אופציונלי): טקסט השאילתה של השאילתה שעבורה יוצרים את הרמזים עם השם.

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

• ‫APPLICATION_NAME (אופציונלי): השם של אפליקציית הלקוח של הסשן שרוצים להשתמש בה ברמזים עם שמות. מחרוזת ריקה מאפשרת להחיל את הרמזים עם השם על השאילתה, ללא קשר לאפליקציית הלקוח ששולחת את השאילתה.

• ‫HINTS: רשימה מופרדת ברווחים של רמזים לשאילתה.

• ‫DISABLED (אופציונלי): BOOL. אם TRUE, בתחילה יוצר את הרמזים בעלי השם כרמזים מושבתים.

דוגמה:

SELECT google_create_named_hints(
HINTS_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

השאילתה הזו יוצרת רמזים עם שם בשם my_hint1. ההערה IndexScan(t) מוחלת על ידי הכלי לתכנון כדי לאלץ סריקת אינדקס בטבלה t בהרצה הבאה של שאילתת הדוגמה הזו.

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

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

אחרי שיוצרים את ההצעות לשמות במופע הראשי, הן מוחלות באופן אוטומטי על השאילתות המשויכות במופע של מאגר הקריאה, בתנאי שהפעלתם את התכונה 'הצעות לשמות' גם במופע של מאגר הקריאה.

יצירת רמזים בעלי שם שרגישים לפרמטרים

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

לדוגמה, הרצת השאילתה הבאה מאפשרת לשאילתה אחרת, כמו SELECT * FROM t WHERE a = 99;, להשתמש ברמזים בשם my_hint2 כברירת מחדל.

SELECT google_create_named_hints(
  HINTS_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

לאחר מכן, שאילתה כמו SELECT * FROM t WHERE a = 99; יכולה להשתמש ברמזים עם שמות my_hint2 כברירת מחדל.

ב-AlloyDB אפשר גם ליצור רמזים עם שם לטקסטים של שאילתות לא פרמטריות, שבהם כל ערך מילולי וקבוע בטקסט השאילתה משמעותי כשמבצעים התאמה בין שאילתות.

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

כדי ליצור רמזים בעלי שם שרגישים לפרמטרים, מגדירים את הפרמטר SENSITIVE_TO_PARAM של הפונקציה google_create_named_hints() לערך TRUE, כמו בדוגמה הבאה:

SELECT google_create_named_hints(
HINTS_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

אי אפשר להשתמש ברמזים עם השמות my_hint3 בשאילתה SELECT * FROM t WHERE a = 99;, כי הערך המילולי '99' לא תואם לערך '88'.

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

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

אימות ההחלה של הרמזים עם השמות

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

• משתמשים בפקודה EXPLAIN או בפקודה EXPLAIN (ANALYZE).

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

  SET pg_hint_plan.debug_print = ON;
  SET client_min_messages = LOG;
  

• משתמשים בתוסף auto_explain.

ניהול רמזים עם שם

ב-AlloyDB אפשר להציג רמזים עם שם, להפעיל ולהשבית אותם ולמחוק אותם.

הצגת רמזים עם שמות

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

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

הפעלת רמזים עם שם

כדי להפעיל רמזים קיימים בעלי שם, משתמשים בפונקציה google_enable_named_hints(HINTS_NAME). כברירת מחדל, רמזים עם שם מופעלים כשיוצרים אותם.

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

SELECT google_enable_named_hints('my_hint1');

השבתת רמזים עם שמות

כדי להשבית רמזים קיימים עם שם, משתמשים בפונקציה google_disable_named_hints(HINTS_NAME).

לדוגמה, כדי למחוק את ההערות לדוגמה בשם hints my_hint1 מהמסד נתונים, מריצים את הפונקציה הבאה:

SELECT google_disable_named_hints('my_hint1');

מחיקת רמזים עם שמות

כדי למחוק רמזים עם שם, משתמשים בפונקציה google_delete_named_hints(HINTS_NAME).

לדוגמה, כדי למחוק את ההערות לדוגמה בשם hints my_hint1 מהמסד נתונים, מריצים את הפונקציה הבאה:

SELECT google_delete_named_hints('my_hint1');

השבתת התכונה 'רמזים עם שמות'

כדי להשבית את התכונה 'רמזים עם שמות' במופע, מגדירים את הדגל alloydb.enable_named_hints לערך off. מידע נוסף מופיע במאמר בנושא הגדרת דגלים של מסד נתונים במופע.

מגבלות

יש מגבלות על השימוש ברמזים עם שמות:

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

• שימוש ברמזים בעלי שם כדי להשפיע על בחירת התוכנית עלול להפריע לשיפורים עתידיים באופטימיזציה של AlloyDB. חשוב לבדוק שוב את הבחירה שלכם להשתמש ברמזים עם שמות, ולשנות את הרמזים עם השמות בהתאם כשמתרחשים האירועים הבאים:

  • יש שינוי משמעותי בעומס העבודה.
  • יש פריסה או שדרוג חדשים של AlloyDB שכוללים שינויים ושיפורים באופטימיזציה.
  • שיטות אחרות לשיפור שאילתות מוחלות על אותן שאילתות.
  • השימוש ברמזים עם שמות מוסיף תקורה משמעותית לביצועי המערכת.

מידע נוסף על מגבלות מופיע במאמרי העזרה בנושא pg_hint_plan.

המאמרים הבאים

• מידע נוסף על מנוע מבוסס-עמודות ב-AlloyDB
• שימוש בכלי להמלצות על אינדקסים עם Query Insights