אופטימיזציה של שאילתות באמצעות עמודות וירטואליות לביטויים

עמודות וירטואליות לביטויים מאיצות שאילתות של מנוע מבוסס-עמודות שמעריכות שוב ושוב את אותם ביטויים, כמו מסננים במאפייני JSON. התכונה הזו זמינה ב-PostgreSQL מגרסה 16 ואילך.

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

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

  • סינון לפי מאפייני JSON: כשמסננים שאילתות לעיתים קרובות על סמך צמדי מפתח/ערך ספציפיים בתוך עמודת JSON.
  • הערכת ביטויים מורכבים: לשאילתות שכוללות ביטויים מורכבים או ביטויים שדורשים משאבי מחשוב רבים.

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

ביטויים נתמכים בעמודות וירטואליות

בגרסת התצוגה המקדימה, ‏AlloyDB תומך באופרטורים -> ו-->> בעמודות JSON ו-JSONB.

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

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

  2. מזהים ביטויים נפוצים בשאילתות של AlloyDB באמצעות תובנות לגבי שאילתות.

  3. הוספת עמודות הבסיס של הביטויים למנוע מבוסס-העמודות. מידע נוסף זמין במאמר ניהול ידני של תוכן בחנות העמודות.

הפעלת עמודות וירטואליות לביטויים

התמיכה בעמודות וירטואליות לביטויים מופעלת כברירת מחדל (google_columnar_engine.enable_virtual_columns_scan=on). אם הדגל הזה הושבת בעבר, אפשר להפעיל אותו למכונה באמצעות הפקודה gcloud alloydb instances update.

כדי להשתמש ב-CLI של gcloud, אתם יכולים להתקין ולהפעיל את Google Cloud CLI או להשתמש ב-Cloud Shell.

gcloud alloydb instances update INSTANCE_ID \
    --region=REGION_ID \
    --cluster=CLUSTER_ID \
    --project=PROJECT_ID \
    --database-flags="google_columnar_engine.enable_virtual_columns_scan=on"

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

  • ‫INSTANCE_ID: מזהה המכונה ב-AlloyDB.
  • ‫REGION_ID: האזור של מכונת AlloyDB.
  • ‫CLUSTER_ID: מזהה האשכול של המכונה ב-AlloyDB.
  • ‫PROJECT_ID: מזהה הפרויקט ב- Google Cloud .

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

SET google_columnar_engine.enable_virtual_columns_scan=on;

הוספת עמודות וירטואליות לביטויים

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

אפשר לציין כמה ביטויים כערכים מופרדים בפסיקים.

    SELECT google_columnar_engine_add(
    relation => 'DB.SCHEMA.TABLE_NAME',
    columns => 'COLUMN_NAME, COLUMN_NAME',
    expressions => 'EXP1, EXP2, EXP3'
    );

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

  • ‫DB.SCHEMA: הסכימה של מסד הנתונים שבו הטבלה מאוחסנת.
  • ‫TABLE_NAME: שם הטבלה שבה העמודה שמורה.
  • COLUMN_NAME: שם העמודות שכוללות את הביטויים.

  • ‫EXP1, EXP2, EXP3: רשימה מופרדת בפסיקים של ביטויים. ביטויי החילוץ של JSON שנתמכים הם -> ו-->>.

    לדוגמה, כדי להוסיף ביטויים של user ->> 'email' ו-user ->> 'name' לטבלה employee בסכימה public, משתמשים בשאילתה הבאה:

        SELECT google_columnar_engine_add(
    relation => 'postgres.public.employee',
    expressions => '"user ->> ''email''", "user ->> ''name''"'
    );

    תחביר הביטוי:

    • תוחמים את כל ערך הביטוי במירכאות בודדות, לדוגמה, expressions => 'EXP1,EXP2,EXP3'.
    • אם יש כמה ביטויים, צריך להפריד ביניהם באמצעות פסיקים.
    • צריך להוסיף מירכאות כפולות לכל ביטוי בנפרד.
    • כדי להוסיף תו בריחה למירכאות בודדות בביטוי, צריך להשתמש במירכאות בודדות נוספות.

    לדוגמה, כדי להוסיף את הביטוי col -> 'level1' ו-col -> 'level1' ->> 'level2', משתמשים בפורמט הבא:

        expressions => '"col -> ''level1''", "col -> ''level1'' ->> ''level2''"'

דוגמה לעמודות וירטואליות לביטויים

הדוגמה הזו ממחישה איך להשתמש בתכונה "עמודות וירטואליות לביטויים". יוצרים טבלת users עם עמודת JSONB מסוג profile וממלאים אותה בנתונים לדוגמה. לאחר מכן, על סמך ניתוח השאילתות, מוסיפים את הביטוי profile ->> 'email' שמשתמשים בו לעיתים קרובות למנוע מבוסס-העמודות באמצעות הפונקציה google_columnar_engine_add. המנוע מבוסס-העמודות משתמש בביטוי הנפוץ הזה כדי לבצע אופטימיזציה של השאילתות הבאות.

כדי להוסיף את הביטוי הנפוץ הזה למנוע מבוסס-העמודות:

  1. נכנסים לדף Clusters במסוף Google Cloud .

    כניסה לדף Clusters

  2. בעמודה Resource name, לוחצים על שם האשכול.

  3. בחלונית הניווט, לוחצים על AlloyDB Studio.

  4. כדי ליצור את הטבלה users עם עמודת JSONB מסוג profile, מריצים את הפקודה הבאה:

    CREATE TABLE users (
id int,
username TEXT,
profile JSONB
);

  5. כדי לאכלס את הטבלה users בנתונים לדוגמה, מריצים את הפקודה הבאה:

    INSERT INTO users (id, username, profile)
SELECT
    i,
    'user' || i,
    jsonb_build_object(
        'name', 'User ' || i,
        'email', 'user' || i || '@example.com',
        'active', (i % 2 = 0)
    )
FROM generate_series(1, 100000) AS i;

  6. כדי לשפר את הביצועים של השאילתה שכוללת את הביטוי שמשתמשים בו לעיתים קרובות, מוסיפים את הביטוי profile ->> 'email' למנוע מבוסס-העמודות:

    SELECT google_columnar_engine_add(
    relation => 'users',
    columns => 'username, profile',
    expressions => '"profile ->> ''email''"'
);

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

    SET google_columnar_engine.enable_virtual_columns_scan=off;

SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

  8. הפעלת התכונה "עמודות וירטואליות לביטויים".

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. מריצים מחדש את השאילתה שמשתמשת בביטוי הנפוץ ובודקים כמה זמן לוקח להשלים את השאילתה.

    SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

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

הצגת עמודות וירטואליות של ביטויים

כדי למצוא את כל הביטויים שנוספו לטבלה ספציפית, שולחים שאילתה לתצוגה g_columnar_virtual_columns:

    SELECT * FROM g_columnar_virtual_columns;

הפלט דומה לפלט הבא, שבו השדה description מציג את כל הביטויים שנוספו לטבלה (יחס):

category           | expression
database_name      | testdb
schemas            | {public}
relations          | {users}
description        | profile->>'email'
column_data_type   | text
status             | Usable
last_accessed_time | 2026-02-04 06:25:32.499601+00
num_times_accessed | 1

הסרת עמודות וירטואליות של ביטויים

כדי להסיר ביטוי, קוראים לפונקציה google_columnar_engine_drop():

    SELECT google_columnar_engine_drop(
      relation => 'DB.SCHEMA.TABLE_NAME',
      expressions => 'EXP1, EXP2, EXP3'
    );

מחליפים את EXP1, EXP2, EXP3 ברשימה מופרדת בפסיקים של ביטויים בטבלה, באותו פורמט שבו משתמשים כשמוסיפים ביטויים.