Membuat dan mengelola patch rencana kueri

Halaman ini menjelaskan cara membuat dan mengelola patch rencana kueri di AlloyDB untuk PostgreSQL.

Patch paket kueri adalah asosiasi antara kueri dan sekumpulan petunjuk yang memungkinkan Anda menentukan detail paket kueri. Petunjuk menentukan informasi tambahan tentang rencana eksekusi akhir yang disukai untuk kueri. Misalnya, saat Anda memindai tabel dalam kueri, gunakan pemindaian indeks, bukan jenis pemindaian lainnya, seperti pemindaian berurutan.

Untuk membatasi pilihan rencana akhir dalam spesifikasi petunjuk, perencana kueri terlebih dahulu menerapkan petunjuk ke kueri saat membuat rencana eksekusinya. Kemudian, petunjuk akan otomatis diterapkan setiap kali kueri dikeluarkan. Pendekatan ini memungkinkan Anda memaksakan rencana kueri yang berbeda dari perencana. Misalnya, Anda dapat menggunakan petunjuk untuk memaksa pemindaian indeks pada tabel tertentu atau untuk memaksa urutan gabungan tertentu di antara beberapa tabel.

Patch rencana kueri AlloyDB mendukung semua petunjuk dari ekstensi pg_hint_plan open source.

Selain itu, AlloyDB mendukung petunjuk berikut untuk mesin columnar:

• ColumnarScan(table): Memaksa pemindaian kolom pada tabel.
• NoColumnarScan(table): Menonaktifkan pemindaian kolom pada tabel.

AlloyDB memungkinkan Anda membuat patch rencana untuk kueri berparameter dan kueri tidak berparameter. Di halaman ini, kueri yang tidak memiliki parameter disebut sebagai kueri yang sensitif terhadap parameter.

Penting: Sebelum menggunakan fitur patch rencana kueri, pertimbangkan potensi overhead yang dapat ditambahkan ke performa sistem. Meskipun patch rencana kueri dapat menyelesaikan rencana kueri yang bermasalah, patch ini menambahkan waktu ekstra dalam pengoptimalan kueri untuk mencari rencana yang cocok dalam katalog dan meminta pengoptimal untuk menerapkan beberapa petunjuk dalam patch. Meskipun overhead sering kali dapat diabaikan dalam kueri yang kompleks, overhead mungkin signifikan dalam kueri sederhana. Misalnya, dalam pengujian internal kami, waktu kompilasi kueri tambahan melebihi 10% untuk kueri perencanaan singkat (kurang dari 1 milidetik).

Alur kerja

Penggunaan patch rencana kueri melibatkan langkah-langkah berikut:

1. Identifikasi kueri yang ingin Anda buat patch rencana.
2. Buat patch rencana dengan petunjuk yang akan diterapkan saat kueri berikutnya dieksekusi.
3. Verifikasi penerapan patch paket.

Halaman ini menggunakan tabel dan indeks berikut untuk contoh:

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

Penting: Jika Anda menggunakan fitur patch rencana kueri versi sebelumnya, Anda harus menghapus ekstensi google_auto_hints sebelumnya di semua database menggunakan perintah berikut:

  DROP EXTENSION IF EXISTS google_auto_hints;

Untuk terus menggunakan patch rencana kueri yang Anda buat menggunakan versi sebelumnya, buat ulang patch tersebut dengan mengikuti petunjuk di halaman ini.

Sebelum memulai

• Aktifkan fitur patch paket kueri di instance Anda. Tetapkan flag alloydb.enable_query_plan_patch ke on. Anda dapat mengaktifkan tanda ini di tingkat seluruh server atau di tingkat sesi. Untuk meminimalkan overhead yang mungkin terjadi akibat penggunaan fitur ini, aktifkan tanda ini hanya di tingkat sesi.

  Untuk mengetahui informasi selengkapnya, lihat Mengonfigurasi flag database instance.

  Catatan: Untuk memaksakan rencana kueri yang sama pada instance kumpulan baca, aktifkan fitur patch rencana kueri pada instance kumpulan baca.

  Untuk memverifikasi bahwa tanda diaktifkan, jalankan perintah show alloydb.enable_query_plan_patch;. Jika tanda diaktifkan, output akan menampilkan "on".

• Untuk setiap database tempat Anda ingin menggunakan patch rencana kueri, buat ekstensi di database dari instance utama AlloyDB sebagai pengguna alloydbsuperuser atau postgres:

  CREATE EXTENSION google_auto_hints CASCADE;
  

Peran yang diperlukan

Untuk mendapatkan izin yang Anda perlukan untuk membuat dan mengelola patch rencana kueri, minta administrator untuk memberi Anda peran Identity and Access Management (IAM) berikut:

• Peran alloydbsuperuser

Meskipun izin default hanya mengizinkan pengguna dengan peran alloydbsuperuser untuk membuat patch rencana, Anda dapat memberikan izin tulis kepada pengguna atau peran lain di database agar mereka dapat membuat patch rencana.

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;

Mengidentifikasi kueri

Anda dapat menggunakan ID kueri untuk mengidentifikasi kueri yang rencana defaultnya perlu disesuaikan. ID kueri tersedia setelah setidaknya satu eksekusi kueri.

Gunakan metode berikut untuk mengidentifikasi ID kueri:

• Jalankan perintah EXPLAIN (VERBOSE), seperti yang ditunjukkan pada contoh berikut:

  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
  

  Dalam output, ID kueri adalah -6875839275481643436.

• Buat kueri tampilan pg_stat_statements.

  Jika mengaktifkan ekstensi pg_stat_statements, Anda dapat menemukan ID kueri dengan membuat kueri tampilan pg_stat_statements, seperti yang ditunjukkan dalam contoh berikut:

  select query, queryid from pg_stat_statements;
  

Membuat patch rencana kueri

Untuk membuat patch rencana kueri, gunakan fungsi google_create_plan_patch(), yang membuat hubungan antara kueri dan petunjuk dalam database.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Ganti kode berikut:

• PLAN_PATCH_NAME: nama untuk patch paket. ID ini harus unik dalam database.

• SQL_ID (Opsional): ID kueri untuk kueri yang Anda buat patch rencananya.

  Anda dapat menggunakan ID kueri atau teks kueri—parameter SQL_TEXT—untuk membuat patch paket. Namun, sebaiknya gunakan ID kueri untuk membuat patch rencana karena AlloyDB secara otomatis menemukan teks kueri yang dinormalisasi berdasarkan ID kueri.

• SQL_TEXT (Opsional): teks kueri dari kueri yang akan Anda buat patch rencananya.

  Saat Anda menggunakan teks kueri, teks harus sama dengan kueri yang dimaksud, kecuali untuk nilai literal dan konstanta dalam kueri. Ketidakcocokan apa pun, termasuk perbedaan huruf besar/kecil, dapat menyebabkan patch rencana tidak diterapkan. Untuk mempelajari cara membuat patch rencana untuk kueri dengan literal dan konstanta, lihat Membuat patch rencana kueri yang sensitif terhadap parameter.

• APPLICATION_NAME (Opsional): nama aplikasi klien sesi yang ingin Anda gunakan untuk patch paket. String kosong memungkinkan Anda menerapkan patch rencana ke kueri, terlepas dari aplikasi klien yang mengeluarkan kueri.

• HINTS: daftar petunjuk yang dipisahkan spasi untuk kueri.

• DISABLED (Opsional): BOOL. Jika TRUE, awalnya membuat patch paket awalnya sebagai dinonaktifkan.

Contoh:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

Kueri ini membuat patch paket bernama my_hint1. Petunjuk IndexScan(t) diterapkan oleh perencana untuk memaksa pemindaian indeks pada tabel t pada eksekusi berikutnya dari contoh kueri ini.

Setelah membuat patch paket, Anda dapat menggunakan google_query_plan_patch_view untuk mengonfirmasi apakah patch paket dibuat, seperti yang ditunjukkan dalam contoh berikut:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Setelah patch rencana kueri dibuat di instance utama, patch tersebut akan otomatis diterapkan ke kueri terkait di instance kumpulan baca, asalkan Anda juga mengaktifkan fitur patch rencana kueri di instance kumpulan baca.

Membuat patch rencana kueri yang sensitif terhadap parameter

Secara default, saat patch rencana dibuat untuk kueri, teks kueri terkait dinormalisasi dengan mengganti nilai literal dan konstanta apa pun dalam teks kueri dengan penanda parameter, seperti ?. Patch rencana kemudian digunakan untuk kueri yang dinormalisasi tersebut meskipun dengan nilai yang berbeda untuk penanda parameter.

Misalnya, menjalankan kueri berikut memungkinkan kueri lain, seperti SELECT * FROM t WHERE a = 99;, menggunakan patch rencana my_hint2 secara default.

SELECT google_create_plan_patch(
  PLAN_PATCH_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

Kemudian, kueri, seperti SELECT * FROM t WHERE a = 99;, dapat menggunakan patch rencana my_hint2 secara default.

AlloyDB juga memungkinkan Anda membuat patch rencana untuk teks kueri yang tidak memiliki parameter, yang mana setiap nilai literal dan konstanta dalam teks kueri penting saat mencocokkan kueri.

Saat Anda menerapkan patch rencana yang sensitif terhadap parameter, dua kueri yang hanya berbeda dalam nilai literal atau konstanta yang sesuai juga dianggap berbeda. Jika Anda ingin memaksakan rencana untuk kedua kueri, Anda harus membuat patch rencana terpisah untuk setiap kueri. Namun, Anda dapat menggunakan petunjuk yang berbeda untuk dua patch rencana.

Untuk membuat patch paket yang sensitif terhadap parameter, tetapkan parameter SENSITIVE_TO_PARAM dari fungsi google_create_plan_patch() ke TRUE, seperti yang ditunjukkan dalam contoh berikut:

SELECT google_create_plan_patch(
PLAN_PATCH_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);

Kueri SELECT * FROM t WHERE a = 99; tidak dapat menggunakan patch paket my_hint3, karena nilai literal "99" tidak cocok dengan "88".

Saat Anda menggunakan patch rencana yang sensitif terhadap parameter, pertimbangkan hal berikut:

• Patch rencana yang peka terhadap parameter tidak mendukung campuran nilai literal dan konstan serta penanda parameter dalam teks kueri.
• Saat Anda membuat patch rencana yang sensitif terhadap parameter dan patch rencana default untuk kueri yang sama, patch rencana yang sensitif terhadap parameter lebih diutamakan daripada patch default.
• Jika Anda ingin menggunakan ID kueri untuk membuat patch rencana yang sensitif terhadap parameter, pastikan kueri dijalankan di sesi saat ini. Nilai parameter dari eksekusi terbaru (dalam sesi saat ini) digunakan untuk membuat patch paket.

Memverifikasi penerapan patch rencana kueri

Setelah membuat patch rencana, gunakan metode berikut untuk memverifikasi bahwa rencana kueri dipaksakan sebagaimana mestinya.

• Gunakan perintah EXPLAIN atau perintah EXPLAIN (ANALYZE).

  Untuk melihat saran yang coba diterapkan oleh perencana, Anda dapat menetapkan flag berikut di tingkat sesi sebelum menjalankan perintah EXPLAIN:

  SET pg_hint_plan.debug_print = ON;
  SET client_min_messages = LOG;
  

• Gunakan ekstensi auto_explain.

Mengelola patch rencana kueri

AlloyDB memungkinkan Anda melihat, mengaktifkan dan menonaktifkan, serta menghapus patch rencana kueri.

Melihat patch rencana kueri

Untuk melihat patch rencana yang ada, gunakan fungsi google_query_plan_patch_view, seperti yang ditunjukkan dalam contoh berikut:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Mengaktifkan patch rencana kueri

Untuk mengaktifkan patch paket yang sudah ada, gunakan fungsi google_enable_plan_patch(PLAN_PATCH_NAME). Secara default, patch rencana diaktifkan saat Anda membuatnya.

Misalnya, untuk mengaktifkan kembali patch paket my_hint1 yang sebelumnya dinonaktifkan dari database, jalankan fungsi berikut:

SELECT google_enable_plan_patch('my_hint1');

Menonaktifkan patch rencana kueri

Untuk menonaktifkan patch paket yang ada, gunakan fungsi google_disable_plan_patch(PLAN_PATCH_NAME).

Misalnya, untuk menghapus patch rencana contoh my_hint1 dari database, jalankan fungsi berikut:

SELECT google_disable_plan_patch('my_hint1');

Menghapus patch rencana kueri

Untuk menghapus patch paket, gunakan fungsi google_delete_plan_patch(PLAN_PATCH_NAME).

Misalnya, untuk menghapus patch rencana contoh my_hint1 dari database, jalankan fungsi berikut:

SELECT google_delete_plan_patch('my_hint1');

Menonaktifkan fitur patch paket kueri

Untuk menonaktifkan fitur patch rencana kueri di instance Anda, tetapkan flag alloydb.enable_query_plan_patch ke off. Untuk mengetahui informasi selengkapnya, lihat Mengonfigurasi flag database instance.

Batasan

Penggunaan patch rencana kueri memiliki batasan berikut:

• Saat Anda menggunakan ID kueri untuk membuat patch rencana kueri, teks kueri asli memiliki batasan panjang 2.048 karakter.
• Mengingat semantik kueri yang kompleks, tidak semua petunjuk dan kombinasinya dapat diterapkan sepenuhnya. Sebaiknya uji petunjuk yang dimaksud pada kueri Anda sebelum men-deploy patch rencana kueri dalam produksi.
• Urutan gabungan paksa untuk kueri kompleks terbatas.

• Menggunakan patch rencana kueri untuk memengaruhi pemilihan rencana dapat mengganggu peningkatan pengoptimal AlloyDB di masa mendatang. Pastikan Anda meninjau kembali pilihan penggunaan patch rencana kueri dan menyesuaikan patch dengan tepat saat peristiwa berikut terjadi:

  • Ada perubahan signifikan dalam workload.
  • Peluncuran atau upgrade AlloyDB baru yang melibatkan perubahan dan peningkatan pengoptimalan sudah tersedia.
  • Metode penyesuaian kueri lainnya diterapkan ke kueri yang sama.
  • Penggunaan patch rencana kueri menambah overhead yang signifikan pada performa sistem.

Untuk mengetahui informasi selengkapnya tentang batasan, lihat dokumentasi pg_hint_plan.

Langkah Berikutnya

• Pelajari mesin berbasis kolom AlloyDB
• Menggunakan index advisor dengan Query Insights