Membuat dan mengelola saran bernama

Halaman ini menjelaskan cara membuat dan mengelola petunjuk bernama di AlloyDB untuk PostgreSQL.

Petunjuk bernama adalah hubungan 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 diterapkan secara otomatis 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.

Petunjuk bernama AlloyDB mendukung semua petunjuk dari ekstensi open source pg_hint_plan.

Selain itu, AlloyDB mendukung petunjuk berikut untuk mesin kolom:

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

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

Alur kerja

Penggunaan saran bernama mencakup langkah-langkah berikut:

Identifikasi kueri yang ingin Anda buatkan petunjuk bernama.
Buat petunjuk bernama dengan petunjuk yang akan diterapkan saat kueri berikutnya dieksekusi.
Verifikasi penerapan petunjuk bernama.

Halaman ini menggunakan tabel dan indeks berikut untuk contoh:

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

  DROP EXTENSION IF EXISTS google_auto_hints;

Untuk terus menggunakan saran bernama yang Anda buat menggunakan versi sebelumnya, buat ulang saran tersebut dengan mengikuti petunjuk di halaman ini.

Sebelum memulai

Aktifkan fitur petunjuk bernama di instance Anda. Tetapkan flag alloydb.enable_named_hints 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 read pool, aktifkan fitur hint bernama di instance read pool.

Untuk memverifikasi bahwa tanda diaktifkan, jalankan perintah show alloydb.enable_named_hints;. Jika tanda diaktifkan, output akan menampilkan "on".
Untuk setiap database tempat Anda ingin menggunakan petunjuk bernama, 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 diperlukan untuk membuat dan mengelola petunjuk bernama, 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 saran bernama, Anda dapat secara opsional memberikan izin tulis kepada pengguna atau peran database lainnya agar mereka dapat membuat saran bernama.

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 akan 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 petunjuk bernama

Untuk membuat petunjuk bernama, gunakan fungsi google_create_named_hints(), yang membuat hubungan antara kueri dan petunjuk dalam database.

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);

Ganti kode berikut:

HINTS_NAME: nama untuk petunjuk bernama. ID ini harus unik dalam database.
SQL_ID (Opsional): ID kueri untuk kueri yang Anda buatkan saran bernama.

Anda dapat menggunakan ID kueri atau teks kueri—parameter SQL_TEXT—untuk membuat saran bernama. Namun, sebaiknya gunakan ID kueri untuk membuat petunjuk bernama karena AlloyDB secara otomatis menemukan teks kueri yang dinormalisasi berdasarkan ID kueri.
SQL_TEXT (Opsional): teks kueri dari kueri yang petunjuk bernama untuknya Anda buat.

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 saran bernama tidak diterapkan. Untuk mempelajari cara membuat petunjuk bernama untuk kueri dengan literal dan konstanta, lihat Membuat petunjuk bernama yang peka terhadap parameter.
APPLICATION_NAME (Opsional): nama aplikasi klien sesi yang ingin Anda gunakan untuk petunjuk bernama. String kosong memungkinkan Anda menerapkan petunjuk bernama ke kueri, terlepas dari aplikasi klien yang mengeluarkan kueri.
HINTS: daftar petunjuk yang dipisahkan spasi untuk kueri.
DISABLED (Opsional): BOOL. Jika TRUE, awalnya membuat saran bernama awalnya sebagai dinonaktifkan.

Contoh:

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

Kueri ini membuat petunjuk 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 petunjuk bernama, Anda dapat menggunakan google_named_hints_view untuk mengonfirmasi apakah petunjuk bernama dibuat, seperti yang ditunjukkan dalam contoh berikut:

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

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

Membuat saran nama yang peka terhadap parameter

Secara default, saat saran bernama dibuat untuk kueri, teks kueri terkait dinormalisasi dengan mengganti nilai literal dan konstanta dalam teks kueri dengan penanda parameter, seperti ?. Kemudian, petunjuk bernama 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 petunjuk bernama my_hint2 secara default.

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);

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

AlloyDB juga memungkinkan Anda membuat petunjuk bernama untuk teks kueri yang tidak memiliki parameter, yang setiap nilai literal dan konstanta dalam teks kueri signifikan saat mencocokkan kueri.

Saat Anda menerapkan saran bernama yang peka 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 petunjuk bernama terpisah untuk setiap kueri. Namun, Anda dapat menggunakan petunjuk yang berbeda untuk dua petunjuk bernama.

Untuk membuat petunjuk bernama yang sensitif terhadap parameter, setel parameter SENSITIVE_TO_PARAM fungsi google_create_named_hints() ke TRUE, seperti yang ditunjukkan dalam contoh berikut:

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);

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

Saat Anda menggunakan petunjuk bernama yang sensitif terhadap parameter, pertimbangkan hal berikut:

Petunjuk bernama yang peka terhadap parameter tidak mendukung campuran nilai literal dan konstanta serta penanda parameter dalam teks kueri.
Saat Anda membuat saran nama yang peka terhadap parameter dan saran nama default untuk kueri yang sama, saran nama yang peka terhadap parameter lebih diutamakan daripada saran nama default.
Jika Anda ingin menggunakan ID kueri untuk membuat petunjuk bernama yang peka terhadap parameter, pastikan kueri dijalankan di sesi saat ini. Nilai parameter dari eksekusi terbaru (dalam sesi saat ini) digunakan untuk membuat petunjuk bernama.

Memverifikasi penerapan petunjuk bernama

Setelah membuat petunjuk bernama, gunakan metode berikut untuk memverifikasi bahwa rencana kueri dipaksakan dengan tepat.

Gunakan perintah EXPLAIN atau perintah EXPLAIN (ANALYZE).

Untuk melihat petunjuk yang coba diterapkan 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 saran bernama

AlloyDB memungkinkan Anda melihat, mengaktifkan dan menonaktifkan, serta menghapus petunjuk bernama.

Melihat petunjuk bernama

Untuk melihat saran bernama yang ada, gunakan fungsi google_named_hints_view, seperti yang ditunjukkan dalam contoh berikut:

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

Mengaktifkan petunjuk bernama

Untuk mengaktifkan saran bernama yang ada, gunakan fungsi google_enable_named_hints(HINTS_NAME). Secara default, saran bernama diaktifkan saat Anda membuatnya.

Misalnya, untuk mengaktifkan kembali petunjuk bernama my_hint1 yang sebelumnya dinonaktifkan dari database, jalankan fungsi berikut:

SELECT google_enable_named_hints('my_hint1');

Menonaktifkan petunjuk bernama

Untuk menonaktifkan petunjuk bernama yang ada, gunakan fungsi google_disable_named_hints(HINTS_NAME).

Misalnya, untuk menghapus contoh petunjuk bernama my_hint1 dari database, jalankan fungsi berikut:

SELECT google_disable_named_hints('my_hint1');

Menghapus petunjuk bernama

Untuk menghapus saran bernama, gunakan fungsi google_delete_named_hints(HINTS_NAME).