Mengoptimalkan kueri dengan kolom virtual untuk ekspresi

Kolom virtual untuk ekspresi mempercepat kueri columnar engine yang berulang kali mengevaluasi ekspresi yang sama, seperti filter pada atribut JSON. Fitur ini tersedia untuk PostgreSQL 16 dan yang lebih baru.

Columnar engine dapat mewujudkan dan meng-cache hasil ekspresi yang sering digunakan. Dengan melakukan pra-komputasi dan menyimpan hasil ini, AlloyDB menghindari penguraian dan evaluasi berulang dari ekspresi yang sama di beberapa kueri. Proses ini meningkatkan performa kueri dan mengurangi konsumsi CPU, terutama untuk workload analisis pada set data besar.

Beberapa kasus penggunaan kolom virtual untuk ekspresi adalah sebagai berikut:

  • Pemfilteran pada atribut JSON: saat Anda sering memfilter kueri berdasarkan key-value pair tertentu dalam kolom JSON.
  • Evaluasi ekspresi yang kompleks: untuk kueri yang melibatkan ekspresi yang kompleks atau intensif secara komputasi.

Saat Anda mengaktifkan kolom virtual untuk pemindaian ekspresi, kueri yang menggunakan columnar engine dan berisi ekspresi yang sering digunakan akan dioptimalkan secara otomatis. Columnar engine otomatis memuat ulang kolom virtual ini saat memuat ulang kolom dasar.

Ekspresi yang didukung di kolom virtual

AlloyDB mendukung operator -> dan ->> pada kolom JSON dan JSONB dalam rilis (Pratinjau).

Sebelum memulai

  1. Mengaktifkan columnar engine untuk cluster AlloyDB Anda. Untuk mengetahui informasi selengkapnya, lihat Mengonfigurasi columnar engine.

  2. Identifikasi ekspresi yang sering digunakan dalam kueri AlloyDB Anda menggunakan Query Insights.

  3. Menambahkan kolom dasar ekspresi ke columnar engine. Untuk mengetahui informasi selengkapnya, lihat Mengelola konten penyimpanan kolom secara manual.

Mengaktifkan kolom virtual untuk ekspresi

Dukungan kolom virtual untuk ekspresi diaktifkan secara default (google_columnar_engine.enable_virtual_columns_scan=on). Jika flag ini sebelumnya dinonaktifkan, Anda dapat mengaktifkannya untuk instance menggunakan perintah gcloud alloydb instances update.

Untuk menggunakan gcloud CLI, Anda dapat menginstal dan melakukan inisialisasi Google Cloud CLI, atau Anda dapat menggunakan 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"

Ganti kode berikut:

  • INSTANCE_ID: ID instance AlloyDB Anda.
  • REGION_ID: region instance AlloyDB Anda.
  • CLUSTER_ID: ID cluster instance AlloyDB Anda.
  • PROJECT_ID: Project ID Google Cloud Anda.

Untuk menetapkan flag ini di tingkat sesi, jalankan perintah berikut:

SET google_columnar_engine.enable_virtual_columns_scan=on;

Menambahkan kolom virtual untuk ekspresi

Untuk menambahkan ekspresi yang umum digunakan ke columnar engine, gunakan klien psql untuk memanggil fungsi google_columnar_engine_add. Ekspresi ini diisi ke dalam columnar engine sebagai kolom selain kolom yang sudah ada dalam database.

Anda dapat menentukan beberapa ekspresi sebagai nilai yang dipisahkan koma.

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

Ganti kode berikut:

  • DB.SCHEMA: skema database tempat tabel Anda disimpan.
  • TABLE_NAME: nama tabel tempat kolom Anda disimpan.
  • COLUMN_NAME: nama kolom yang menyertakan ekspresi.

  • EXP1, EXP2, EXP3: daftar ekspresi yang dipisahkan koma. Ekspresi ekstrak JSON yang didukung adalah -> dan ->>.

    Misalnya, untuk menambahkan ekspresi user ->> 'email' dan user ->> 'name' untuk tabel employee dalam skema public, gunakan kueri berikut:

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

    Sintaksis Ekspresi:

    • Sertakan seluruh nilai ekspresi dalam tanda kutip tunggal—misalnya, expressions => 'EXP1,EXP2,EXP3'.
    • Pisahkan beberapa ekspresi dengan koma.
    • Sertakan setiap ekspresi dalam tanda kutip ganda.
    • Gunakan tanda kutip tunggal lain untuk meng-escape tanda kutip tunggal dalam ekspresi.

    Misalnya, untuk menambahkan ekspresi col -> 'level1' dan col -> 'level1' ->> 'level2', gunakan format berikut:

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

Contoh kolom virtual untuk ekspresi

Contoh ini menunjukkan cara menggunakan fitur kolom virtual untuk ekspresi. Buat tabel users dengan kolom JSONB profile dan isi dengan data sampel. Kemudian, berdasarkan analisis kueri, tambahkan ekspresi profile ->> 'email' yang sering digunakan ke columnar engine dengan fungsi google_columnar_engine_add. Columnar engine kemudian menggunakan ekspresi yang sering digunakan ini untuk membantu mengoptimalkan kueri berikutnya.

Untuk menambahkan contoh ekspresi yang sering digunakan ini ke columnar engine, ikuti langkah-langkah berikut:

  1. Di konsol Google Cloud , buka halaman Clusters.

    Buka Cluster

  2. Klik nama cluster Anda di kolom Resource name.

  3. Di panel navigasi, klik AlloyDB Studio.

  4. Untuk membuat tabel users dengan kolom JSONB profile, jalankan perintah berikut:

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

  5. Untuk mengisi tabel users dengan data sampel, jalankan perintah berikut:

    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. Untuk meningkatkan performa kueri yang menyertakan ekspresi yang sering digunakan, tambahkan ekspresi profile ->> 'email' ke columnar engine:

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

  7. Nonaktifkan fitur kolom virtual untuk ekspresi guna menetapkan dasar pengukuran performa, lalu jalankan kueri yang menggunakan ekspresi yang sering digunakan dan amati waktu yang diperlukan kueri untuk selesai.

    SET google_columnar_engine.enable_virtual_columns_scan=off;

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

  8. Aktifkan fitur kolom virtual untuk ekspresi.

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. Jalankan kembali kueri yang menggunakan ekspresi yang sering digunakan dan amati waktu yang diperlukan kueri untuk selesai.

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

Runtime kueri akan lebih cepat setelah Anda mengaktifkan kolom virtual untuk ekspresi.

Melihat kolom virtual untuk ekspresi

Untuk menemukan semua ekspresi yang ditambahkan untuk tabel tertentu, buat kueri tampilan g_columnar_virtual_columns:

    SELECT * FROM g_columnar_virtual_columns;

Outputnya mirip dengan berikut ini, dengan kolom description menampilkan semua ekspresi yang ditambahkan untuk tabel (relasi):

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

Menghapus kolom virtual untuk ekspresi

Untuk menghapus ekspresi, panggil fungsi google_columnar_engine_drop():

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

Ganti EXP1, EXP2, EXP3 dengan daftar ekspresi yang dipisahkan koma dalam tabel dalam format yang sama seperti yang digunakan saat menambahkan ekspresi.