Menyesuaikan kueri SQL menggunakan AI

Untuk mengoptimalkan performa kueri database AlloyDB untuk PostgreSQL, Anda dapat menyesuaikan kueri SQL menggunakan insight berbasis AI. Dengan menganalisis rencana eksekusi kueri Anda, fungsi ai.tune_sql—yang didukung oleh model bahasa besar (LLM)—mengidentifikasi bottleneck yang kompleks dan memberikan rekomendasi yang dapat Anda tindak lanjuti untuk pengoptimalan.

Saat Anda memanggil fungsi ai.tune_sql, fungsi ini akan menganalisis kueri Anda dan memberikan insight serta rekomendasi kueri di area berikut:

  • Ringkasan kueri: menganalisis bottleneck performa kueri.
  • Penulisan ulang kueri: menyarankan teknik pengoptimalan untuk menulis ulang kueri asli menjadi kueri yang setara dan lebih efisien.
  • Rekomendasi indeks: merekomendasikan jenis indeks seperti indeks pada ekspresi, indeks Generalized Inverted Index (GIN), dan indeks Scalable Nearest Neighbors (ScaNN).
  • Rekomendasi pengumpulan statistik: merekomendasikan tabel ANALYZE dan pengumpulan statistik di beberapa kolom berdasarkan rencana eksekusi kueri.
  • Penyesuaian flag Grand Unified Configuration (GUC): merekomendasikan nilai GUC baru dengan menganalisis rencana eksekusi dan informasi sistem seperti penggunaan dan ukuran memori serta buffer.
  • Analyze and vacuum: merekomendasikan menjalankan ANALYZE dan VACUUM pada tabel berdasarkan histori analisis dan vacuum serta jumlah tuple aktif dan tidak aktif.

Batasan

Penyesuaian kueri SQL memiliki batasan berikut:

  • Pernyataan yang disiapkan dan berparameter: untuk pernyataan yang disiapkan dan berparameter, ai.tune_sql tidak memberikan rekomendasi penulisan ulang kueri, meskipun masih memberikan rekomendasi penyesuaian lainnya. Untuk menerima rekomendasi penulisan ulang kueri untuk kueri yang berisi placeholder parameter, seperti $1 atau $2 yang disalin dari Query Insights atau pg_stat_statements, ganti placeholder dengan nilai literal yang representatif sebelum menjalankan ai.tune_sql.
  • Pernyataan SELECT hanya baca hanya jika explain_analyze adalah TRUE: hanya pernyataan SELECT hanya baca tunggal yang didukung jika explain_analyze ditetapkan ke TRUE. Menjalankan ai.tune_sql dengan explain_analyze yang ditetapkan ke TRUE pada pernyataan lain—seperti INSERT, UPDATE, DELETE, SELECT INTO, atau SELECT FOR UPDATE—akan menampilkan pesan error.

Sebelum memulai

Pastikan Anda memiliki hal berikut:

Mengaktifkan penyesuaian SQL

Mendaftarkan LLM dan mengaktifkan flag database yang diperlukan.

  1. Buat endpoint model Gemini untuk fungsi ai.tune_sql dengan mendaftarkan endpoint model global atau endpoint model regional di region instance Anda:

    • Untuk mendaftarkan endpoint model global saat model tidak tersedia di region instance Anda—misalnya, gemini-3.5-flash di locations/global—jalankan pernyataan berikut:

      CALL google_ml.create_model(
  model_id => 'gemini-3.5-flash-global',
  model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
  model_qualified_name => 'gemini-3.5-flash',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      Ganti PROJECT_ID dengan ID project tempat model tersedia.

    • Untuk mendaftarkan endpoint model regional di region instance Anda—misalnya, gemini-2.5-pro di locations/REGION_ID—jalankan pernyataan berikut:

      CALL google_ml.create_model(
  model_id => 'gemini-2.5-pro',
  model_request_url => 'https://REGION_ID-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/REGION_ID/publishers/google/models/gemini-2.5-pro:generateContent',
  model_qualified_name => 'gemini-2.5-pro',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      Ganti kode berikut:

      • REGION_ID: region instance AlloyDB Anda—misalnya, us-central1.
      • PROJECT_ID: ID project tempat model tersedia.

  2. Tetapkan ID model untuk rekomendasi penyesuaian kueri SQL di sesi database Anda saat ini. Tentukan model_id yang Anda daftarkan di langkah sebelumnya, seperti 'gemini-3.5-flash-global' atau 'gemini-2.5-pro':

    SET google_db_advisor.llm_model_id = 'gemini-3.5-flash-global';

  3. Aktifkan penyesuaian kueri SQL untuk sesi database Anda saat ini dengan menetapkan flag google_db_advisor.enable_ai_tune_sql:

    SET google_db_advisor.enable_ai_tune_sql = on;

    Karena SET hanya berlaku untuk sesi database saat ini, Anda dapat mempertahankan setelan ini di seluruh sesi dengan menjalankan ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; untuk kedua flag, atau dengan mengonfigurasi flag database instance.

Menyesuaikan kueri SQL secara manual

Untuk menyesuaikan kueri SQL secara manual, jalankan fungsi ai.tune_sql menggunakan pernyataan SELECT.

  1. Hubungkan klien psql ke instance AlloyDB atau buka AlloyDB Studio.

    Jika Anda menggunakan SET untuk mengonfigurasi google_db_advisor.llm_model_id dan google_db_advisor.enable_ai_tune_sql, jalankan pernyataan SET tersebut dalam sesi database yang sama sebelum memanggil ai.tune_sql.

  2. Untuk menyesuaikan kueri SQL, jalankan fungsi ai.tune_sql. Fungsi ini mengambil skema yang terkait dengan kueri dan mengirimkannya, bersama dengan kueri, ke LLM untuk analisis performa:

    SELECT ai.tune_sql(query => 'SQL_QUERY', explain_analyze => TRUE);

    Ganti SQL_QUERY dengan kueri SQL yang ingin Anda analisis.

    Secara default, parameter eksekusi (explain_analyze) ditetapkan ke TRUE. Jika ditetapkan ke TRUE, fungsi akan menjalankan EXPLAIN ANALYZE untuk mengeksekusi kueri dan mengambil rencana eksekusi. Pendekatan ini memberikan saran penyesuaian kueri yang lebih akurat dan berguna. Jika Anda menetapkan explain_analyze ke FALSE, fungsi hanya akan menjalankan EXPLAIN untuk membuat rencana tanpa menjalankan kueri.

Melihat rencana kueri dengan rekomendasi performa

Output pertama-tama menampilkan rencana kueri standar, diikuti dengan rekomendasi mendetail yang dihasilkan AI. Rekomendasi tersebut mencakup ringkasan kueri, penulisan ulang yang disarankan, dan rekomendasi untuk pengumpulan statistik, penyesuaian flag GUC, dan vacuuming. Fungsi ai.tune_sql dapat memerlukan waktu dua hingga empat menit untuk menghasilkan rekomendasi.

Contoh berikut menunjukkan output ai.tune_sql untuk kueri anti-join:

SELECT ai.tune_sql(
  'SELECT *
   FROM store_sales
   WHERE ss_customer_sk NOT IN (
     SELECT sr_customer_sk
     FROM store_returns
     WHERE sr_item_sk = ss_item_sk)');

                     tune_sql
--------------------------------------------------
 NOTE
 --------------------------------------------------
 Advices generated with the assistance of an LLM. Independent verification of these advices is required due to the potential for inaccuracies.
 --------------------------------------------------
 ORIGINAL QUERY PLAN
 --------------------------------------------------
 Seq Scan on store_sales  (cost=0.00..111292742.52 rows=1440384 width=115) (actual time=0.092..103093.207 rows=1306312.00 loops=1)
   Filter: (NOT (ANY (ss_customer_sk = (SubPlan 1).col1)))
   Rows Removed by Filter: 1574198
   Buffers: shared hit=72987539
   SubPlan 1
     ->  Index Scan using idx_sr_item_sk on store_returns  (cost=0.42..76.74 rows=18 width=4) (actual time=0.005..0.034 rows=19.38 loops=2880510)
           Index Cond: (sr_item_sk = store_sales.ss_item_sk)
           Index Searches: 2880510
           Buffers: shared hit=72933670
 Planning Time: 0.257 ms
 --------------------------------------------------
 QUERY SUMMARY
 --------------------------------------------------
 The original query plan shows a Sequential Scan on store_sales with a correlated SubPlan for the NOT IN condition. For each of the 1.44 million rows in store_sales, it executes an Index Scan on store_returns, resulting in over 2.88 million index searches. This nested loop execution is the primary bottleneck, taking over 103 seconds. Converting the correlated NOT IN subquery to an anti-join (NOT EXISTS) will allow the optimizer to use a Hash Anti Join, processing both tables in bulk and significantly reducing execution time.
 --------------------------------------------------
 AI SUGGESTED QUERY 1
 --------------------------------------------------
 SELECT * FROM store_sales WHERE NOT EXISTS (SELECT 1 FROM store_returns WHERE store_returns.sr_item_sk = store_sales.ss_item_sk AND (store_returns.sr_customer_sk = store_sales.ss_customer_sk OR store_returns.sr_customer_sk IS NULL OR store_sales.ss_customer_sk IS NULL));
 Reason: The original query uses a NOT IN subquery which is executed as a correlated subquery (SubPlan), leading to millions of index scans. Rewriting to NOT EXISTS allows the optimizer to use a Hash Anti Join, which is much more efficient for large datasets. The OR IS NULL conditions ensure strict semantic equivalence with NOT IN when dealing with NULL values.
 --------------------------------------------------
 REWRITTEN QUERY PLAN
 --------------------------------------------------
 Gather  (cost=8541.96..139088.40 rows=154194 width=115)
   Workers Planned: 2
   ->  Parallel Hash Anti Join  (cost=7541.96..122669.00 rows=64248 width=115)
         Hash Cond: (store_sales.ss_item_sk = store_returns.sr_item_sk)
         Join Filter: ((store_returns.sr_customer_sk = store_sales.ss_customer_sk) OR (store_returns.sr_customer_sk IS NULL) OR (store_sales.ss_customer_sk IS NULL))
         ->  Parallel Seq Scan on store_sales  (cost=0.00..65872.20 rows=1200320 width=115)
         ->  Parallel Hash  (cost=6044.76..6044.76 rows=119776 width=8)
               ->  Parallel Seq Scan on store_returns  (cost=0.00..6044.76 rows=119776 width=8)
 --------------------------------------------------
 AI SUGGESTED INDEX 1
 --------------------------------------------------
 CREATE INDEX idx_sr_item_customer ON store_returns (sr_item_sk, sr_customer_sk);
 Reason: A composite index on (sr_item_sk, sr_customer_sk) allows index-only scans for the subquery or early aggregation, reducing table access and speeding up the anti-join condition evaluation.
 Note: Indexes are suggested solely based on the given query, but they don't consider the overall system workload.
           The suggested indexes improve the performance of the original query.
 --------------------------------------------------
 GUC FLAGS TUNING
 --------------------------------------------------
 SET work_mem = '64MB'; The current work_mem of 4MB is too small for a Hash Anti Join on tables with hundreds of thousands of rows. Increasing it to 64MB allows the hash table for store_returns to fit entirely in memory, preventing costly disk spills during the join.

 Execution Time: 103167.475 ms
(50 rows)

Langkah berikutnya