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

בדף הזה מוסבר איך ליצור ולנהל רמזים עם שמות ב-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).

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

SELECT google_disable_named_hints('my_hint1');

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

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

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

SELECT google_delete_named_hints('my_hint1');

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

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

מגבלות

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

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

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

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

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

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

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