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

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

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

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

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

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

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

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

תהליך עבודה

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

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

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

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

  DROP EXTENSION IF EXISTS google_auto_hints;

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

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

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

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

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

כדי לוודא שהדגל מופעל, מריצים את הפקודה 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).