gunakan ekstensi pg_hint_plan - PolarDB

Ekstensi pg_hint_plan menyesuaikan rencana eksekusi menggunakan petunjuk dalam komentar khusus.

Informasi latar belakang

PostgreSQL menggunakan pengoptimal berbasis biaya yang mengandalkan statistik data, bukan aturan tetap. Untuk setiap Pernyataan SQL, pengoptimal mengevaluasi semua rencana eksekusi yang mungkin dan memilih rencana dengan biaya terendah. Meskipun pengoptimal berusaha memilih rencana terbaik, hasilnya mungkin tidak sempurna karena pengoptimal tidak mengetahui hubungan data yang mendasarinya.

Anda dapat menyesuaikan rencana eksekusi dengan mengatur variabel Grand Unified Configuration (GUC), tetapi perubahan tersebut berlaku untuk seluruh sesi. Ekstensi pg_hint_plan memungkinkan Anda mengoptimalkan satu rencana eksekusi tanpa memengaruhi seluruh sesi.

Prasyarat

Versi PolarDB for PostgreSQL yang didukung adalah sebagai berikut:

PostgreSQL 16 (versi mesin minor 2.0.16.9.6.0 dan yang lebih baru)
PostgreSQL 14 (tidak ada batasan versi mesin minor)
PostgreSQL 11 (tidak ada batasan versi mesin minor)

Catatan

Anda dapat melihat nomor versi mesin minor di konsol atau dengan menjalankan pernyataan SHOW polardb_version;. Jika versi mesin minor tidak memenuhi persyaratan, tingkatkan versi mesin minor.

Peringatan

Data Management Service (DMS) saat ini tidak mendukung komentar petunjuk. Anda harus menggunakan metode lain untuk terhubung ke database.
Ekstensi pg_hint_plan hanya mengenali konten dalam blok komentar pertama.
Selama pemindaian, jika ditemukan karakter selain huruf, angka, spasi, garis bawah, koma, atau tanda kurung, pemindaian akan segera berhenti.
Ekstensi pg_hint_plan menangani objek secara berbeda dari PostgreSQL. Ekstensi ini melakukan perbandingan peka huruf besar/kecil pada nama objek. Misalnya, objek bernama TBL dalam pernyataan petunjuk hanya cocok dengan TBL, bukan tbl atau Tbl.

Batasan

Batasan berikut berlaku saat menggunakan ekstensi pg_hint_plan dalam prosedur tersimpan PL/pgSQL:

Petunjuk hanya berlaku untuk jenis pernyataan berikut:
- Pernyataan SELECT, INSERT, UPDATE, atau DELETE yang mengembalikan satu baris.
- Pernyataan RETURN QUERY yang mengembalikan beberapa baris.
- Pernyataan EXECUTE QUERY.
- Gunakan pernyataan OPEN untuk membuka kursor.
- Loop FOR yang melakukan iterasi atas hasil kueri.
Pernyataan petunjuk harus ditempatkan tepat setelah kata pertama dalam kueri. Petunjuk yang ditempatkan di tempat lain tidak dianggap sebagai bagian dari kueri.

Buat ekstensi

Buat ekstensi.
```
CREATE EXTENSION pg_hint_plan;
```
Muat ekstensi.
- Muat secara otomatis untuk satu pengguna.
  - Jalankan perintah berikut untuk memuat ekstensi.
```
ALTER USER xxx set session_preload_libraries='pg_hint_plan';
```
    Catatan
    Ganti xxx dengan nama pengguna login yang sebenarnya.
  - Jalankan perintah berikut untuk memuat secara otomatis untuk satu database.
```
ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
```
  Catatan
  Jika kesalahan konfigurasi mencegah Anda masuk ke database, Anda dapat masuk ke PolarDB sebagai pengguna lain atau terhubung ke database lain untuk mengatur ulang konfigurasi:
```
ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
```
- Muat secara otomatis untuk kluster database.
  Buka Quota Center. Temukan kuota PolarDB PG Pg_hint_plan Use dan klik Apply di kolom Operation untuk meminta ekstensi pg_hint_plan.
- Periksa apakah ekstensi telah dimuat.
  1. Jalankan perintah berikut untuk mengeluarkan informasi debug ke klien.
```
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
```
  2. Jalankan perintah berikut untuk memeriksa apakah pemuatan berhasil.
```
/*+Set(enable_seqscan 1)*/select 1;
```
    Hasil berikut menunjukkan pemuatan berhasil.
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. Jalankan perintah berikut untuk mematikan keluaran debug.
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

Penggunaan

Comment Hints

Komentar pg_hint_plan dimulai dengan /*+ dan diakhiri dengan */. Pernyataan petunjuk mencakup nama petunjuk dan parameter. Parameter tersebut diapit tanda kurung dan dipisahkan oleh spasi. Untuk keterbacaan, Anda dapat menempatkan setiap pernyataan petunjuk pada baris baru.

Contoh:

Gunakan HashJoin sebagai metode join dan SeqScan untuk memindai tabel pgbench_accounts:

/*+
   HashJoin(a b)
   SeqScan(a)
 */
EXPLAIN SELECT *
   FROM pgbench_branches b
   JOIN pgbench_accounts a ON b.bid = a.bid
   ORDER BY a.aid;

Hasil berikut dikembalikan:

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

Jenis petunjuk

Hint Types
Jenis petunjuk yang didukung dikategorikan berdasarkan pengaruhnya terhadap rencana eksekusi dan mencakup petunjuk metode pemindaian, petunjuk metode join, petunjuk urutan join, petunjuk koreksi jumlah baris, petunjuk eksekusi paralel, dan petunjuk pengaturan parameter GUC.
- Scan Method Hints
  Petunjuk metode pemindaian memaksa metode pemindaian tertentu pada tabel target. Ekstensi pg_hint_plan mengidentifikasi tabel target berdasarkan alias-nya, jika ada. Metode pemindaian mencakup opsi seperti pemindaian sekuensial dan pemindaian indeks.
  Petunjuk pemindaian efektif untuk tabel standar, tabel turunan, tabel unlogged, tabel sementara, dan tabel sistem. Petunjuk ini tidak efektif untuk tabel eksternal, fungsi tabel, pernyataan nilai konstan, ekspresi tabel umum, tampilan, atau subkueri.
  Contoh perintah:
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
- Join Method Hints
  Petunjuk metode join memaksa metode tertentu untuk menggabungkan tabel terkait. Petunjuk ini efektif untuk tabel standar, tabel turunan, tabel unlogged, tabel sementara, tabel eksternal, tabel sistem, fungsi tabel, pernyataan nilai konstan, dan ekspresi tabel umum. Petunjuk ini tidak efektif untuk tampilan atau subkueri.
- Join Order Hints
  Petunjuk urutan join menentukan urutan join untuk dua atau lebih tabel. Ada dua cara untuk memaksa urutan:
  - Memaksa urutan join tertentu tanpa membatasi arah pada setiap level join.
  - Memaksa arah join.
  Contoh perintah:
```
 /*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    Leading(t1 t2 t3)
  */
 SELECT * FROM table1 t1
     JOIN table table2 t2 ON (t1.key = t2.key)
     JOIN table table3 t3 ON (t2.key = t3.key);
```
  Catatan
  Di mana:
  NestLoop(t1 t2): Menentukan metode join untuk tabel t1 dan t2.
  MergeJoin(t1 t2 t3): Menentukan metode join antara tabel t1, t2, dan t3.
  Leading(t1 t2 t3): Menentukan urutan join ketiga tabel tersebut.
- Row Count Correction
  Petunjuk koreksi jumlah baris memperbaiki kesalahan jumlah baris yang disebabkan oleh keterbatasan pengoptimal kueri.
```
 /*+ Rows(a b #10) */ SELECT... ; # Mengatur jumlah baris dalam hasil join menjadi 10.
 /*+ Rows(a b +10) */ SELECT... ; # Menambah jumlah baris sebanyak 10.
 /*+ Rows(a b -10) */ SELECT... ; # Mengurangi jumlah baris sebanyak 10.
 /*+ Rows(a b *10) */ SELECT... ; # Mengalikan jumlah baris dengan 10.
```
- Parallel execution hints
  Petunjuk eksekusi paralel menentukan rencana eksekusi paralel.
  Petunjuk tingkat paralelisme efektif untuk tabel standar, tabel turunan, tabel unlogged, dan tabel sistem. Petunjuk ini tidak efektif untuk tabel eksternal, klausa konstan, ekspresi tabel umum, tampilan, atau subkueri. Tabel internal suatu tampilan dapat ditentukan sebagai objek target berdasarkan nama aslinya atau alias-nya.
  Dua contoh berikut menunjukkan bahwa kueri dieksekusi secara berbeda untuk setiap tabel:
  - Metode 1: Atur tingkat paralelisme untuk tabel c1 menjadi 3 dan untuk tabel c2 menjadi 5.
```
EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
```
    Hasil berikut dikembalikan:
```
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)
                                        
```
  - Metode 2: Atur tingkat paralelisme untuk tabel t1 menjadi 5.
```
EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
```
    Hasil berikut dikembalikan:
```
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)
```
- GUC Parameter Settings
  Petunjuk ini mengubah nilai parameter GUC selama eksekusi kueri. Nilai baru hanya berlaku saat pelaksana menghasilkan rencana kueri dan tidak memengaruhi pernyataan lain. Jika Anda mengatur parameter GUC yang sama beberapa kali, hanya pengaturan terakhir yang berlaku.
  Contoh perintah:
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

Hint Formats

Tabel berikut mencantumkan format yang didukung untuk semua petunjuk. Untuk menggunakan petunjuk, tambahkan ke komentar dalam format yang ditentukan. Tanda kurung [] menunjukkan parameter opsional.

Jenis	Format	Deskripsi
Metode pemindaian	SeqScan(table)	Memaksa pemindaian sekuensial pada tabel bernama 'table'.
	TidScan(table)	Memaksa pemindaian TID pada tabel bernama 'table'.
	IndexScan(table[ index...])	Memaksa pemindaian indeks pada tabel bernama 'table'. Anda dapat menentukan indeks mana yang digunakan dengan menambahkan nama indeks.
	IndexOnlyScan(table[ index...])	Memaksa pemindaian hanya-indeks pada tabel bernama 'table'. Anda dapat menentukan indeks mana yang digunakan dengan menambahkan nama indeks.
	BitmapScan(table[ index...])	Memaksa pemindaian indeks bitmap pada tabel bernama 'table'. Anda dapat menentukan indeks mana yang digunakan dengan menambahkan nama indeks.
	NoSeqScan(table)	Menonaktifkan pemindaian sekuensial pada tabel bernama 'table'.
	NoTidScan(table)	Menonaktifkan pemindaian TID pada tabel bernama 'table'.
	NoIndexScan(table)	Menonaktifkan pemindaian indeks pada tabel bernama 'table'.
	NoIndexOnlyScan(table)	Menonaktifkan pemindaian hanya-indeks pada tabel bernama 'table'.
	NoBitmapScan(table)	Menonaktifkan pemindaian indeks bitmap pada tabel bernama 'table'.
Metode join	NestLoop(table table[ table...])	Memaksa join loop bersarang untuk operasi join antara tabel yang ditentukan.
	HashJoin(table table[ table...])	Memaksa join hash untuk operasi join antara tabel yang ditentukan.
	MergeJoin(table table[ table...])	Memaksa join merge untuk operasi join antara tabel yang ditentukan.
	NoNestLoop(table table[ table...])	Menonaktifkan join loop bersarang untuk operasi join antara tabel yang ditentukan.
	NoHashJoin(table table[ table...])	Menonaktifkan join hash untuk operasi join antara tabel yang ditentukan.
	NoMergeJoin(table table[ table...])	Menonaktifkan join merge untuk operasi join antara tabel yang ditentukan.
Urutan join	Leading(table table[ table...])	Menentukan urutan join antara tabel.
Urutan join	Leading(<pasangan join>)	Menentukan urutan join antara dua tabel.
Koreksi jumlah baris	Rows(table table[ table...] correction)	Mengoreksi jumlah baris dalam hasil join tabel yang ditentukan. Metode koreksi yang tersedia meliputi nilai absolut (#<n>), penambahan (+<n>), pengurangan (-<n>), dan perkalian (*<n>), di mana <n> adalah jumlah baris.
Konfigurasi kueri paralel	Parallel(table <jumlah pekerja> [soft\|hard])	Memaksa atau menonaktifkan pemindaian paralel untuk tabel yang ditentukan. Catatan <jumlah pekerja> adalah tingkat paralelisme yang diinginkan (jumlah proses pekerja paralel). Nilai 0 menonaktifkan eksekusi paralel. Jika parameter ketiga adalah soft (default), hanya nilai parameter max_parallel_workers_per_gather yang diubah. Pengoptimal menentukan tingkat paralelisme aktual. hard memaksa tingkat paralelisme yang ditentukan.
	PX(<jumlah pekerja>)	Menunjukkan bahwa kueri paralel dilakukan selama eksekusi paralel lintas node. Catatan <jumlah pekerja> menentukan tingkat paralelisme.
	NoPX()	Memaksa kueri agar tidak menggunakan fitur eksekusi paralel lintas node.
Konfigurasi parameter GUC	Set(GUC-param value)	Menyetel parameter GUC ke nilai yang ditentukan pada waktu proses pengoptimal.

Catatan

pg_hint_plan juga dapat menentukan rencana kueri yang dihasilkan oleh eksekusi paralel lintas node. Dalam skenario eksekusi paralel lintas node, petunjuk koreksi jumlah baris tidak didukung. Petunjuk metode join hanya dapat diterapkan pada join antara dua tabel. Petunjuk urutan join hanya dapat menentukan urutan untuk semua tabel yang terlibat.