Menggunakan ekstensi pg_hint_plan - PolarDB - Alibaba Cloud Documentation Center

Anda dapat menggunakan ekstensi pg_hint_plan untuk menambahkan hint ke Pernyataan SQL guna menentukan cara eksekusinya, sehingga mengoptimalkan rencana eksekusi tersebut.

Informasi latar belakang

PostgreSQL menggunakan cost-based optimizer yang memanfaatkan statistik data alih-alih aturan statis. Pengoptimal mengevaluasi biaya semua rencana eksekusi yang mungkin untuk suatu Pernyataan SQL, lalu mengeksekusi rencana dengan biaya terendah. Meskipun pengoptimal telah berusaha semaksimal mungkin, rencana yang dipilih mungkin bukan yang terbaik karena tidak mempertimbangkan hubungan mendasar antar data.

Anda dapat menyesuaikan rencana eksekusi dengan mengatur variabel Grand Unified Configuration (GUC), tetapi perubahan tersebut akan berlaku untuk seluruh sesi. Jika Anda hanya ingin mengoptimalkan satu rencana eksekusi tertentu tanpa memengaruhi seluruh sesi, gunakan pg_hint_plan.

Perhatian

Data Management Service (DMS) tidak mendukung hint dalam komentar. Gunakan klien database lain untuk menghubungkan ke database Anda.
Ekstensi pg_hint_plan hanya membaca hint dari blok komentar pertama dalam suatu pernyataan.
Pemindai hint segera berhenti jika menemui karakter apa pun selain huruf, angka, spasi, garis bawah (_), koma (,), atau tanda kurung (()).
pg_hint_plan menangani nama objek secara berbeda dibandingkan PostgreSQL. Ekstensi ini melakukan perbandingan peka huruf besar/kecil (case-sensitive) terhadap nama objek. Misalnya, hint untuk objek bernama TBL hanya cocok dengan objek bernama TBL, bukan tbl atau Tbl.

Batasan

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

Hint hanya berlaku untuk jenis pernyataan berikut:
- Kueri yang mengembalikan satu baris (SELECT, INSERT, UPDATE, DELETE).
- Kueri yang mengembalikan beberapa baris (RETURN QUERY).
- Eksekusi Pernyataan SQL (EXECUTE QUERY).
- Pernyataan yang membuka kursor (OPEN).
- Loop atas hasil kueri (FOR).
Hint harus ditempatkan tepat setelah kata pertama dalam kueri. pg_hint_plan mengabaikan hint yang ditempatkan sebelum kata pertama.

Membuat dan memuat ekstensi pg_hint_plan

Buat ekstensi.
```
CREATE EXTENSION pg_hint_plan;
```
Muat ekstensi.
- Untuk memuat ekstensi secara otomatis bagi satu pengguna:
  - Jalankan perintah berikut untuk memuat ekstensi.
```
ALTER USER xxx set session_preload_libraries='pg_hint_plan';
```
    Catatan
    Ganti xxx dengan username aktual.
  - Jalankan perintah berikut untuk memuat ekstensi pada database tertentu.
```
ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
```
  Catatan
  Jika kesalahan konfigurasi mencegah Anda terhubung ke database, Anda harus menghubungkan ke instans PolarDB Anda dengan pengguna atau database berbeda, lalu jalankan perintah reset berikut:
```
ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
```
- Untuk memuat ekstensi secara otomatis bagi kluster database:
  
  Buka Quota Center. Pada baris kuota PolarDB PG pg_hint_plan usage, klik Apply di kolom Actions untuk meminta akses ke ekstensi pg_hint_plan.
- Verifikasi bahwa ekstensi telah dimuat.
  1. Jalankan perintah berikut untuk mengirim output debug ke client.
```
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
```
  2. Jalankan perintah berikut untuk memverifikasi bahwa ekstensi telah dimuat.
```
/*+Set(enable_seqscan 1)*/select 1;
```
    Jika ekstensi telah dimuat, perintah tersebut mengembalikan output berikut:
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. Jalankan perintah berikut untuk menonaktifkan output debug.
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

Catatan penggunaan

Comment hints

Blok komentar pg_hint_plan dimulai dengan /*+ dan diakhiri dengan */. Hint terdiri atas nama hint diikuti parameter dalam tanda kurung. Parameter dipisahkan oleh spasi. Untuk keterbacaan, Anda dapat menempatkan setiap hint pada baris baru.

Contoh

Contoh berikut menggunakan metode join HashJoin dan metode pemindaian SeqScan untuk 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;

Perintah tersebut mengembalikan output berikut:

                                      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 hint

Jenis petunjuk

Hint diklasifikasikan ke dalam enam jenis berikut berdasarkan cara memengaruhi rencana eksekusi:
- Petunjuk metode pemindaian
  
  Jenis hint ini menentukan metode yang digunakan untuk memindai tabel tertentu. Jika tabel yang ditentukan memiliki alias, ekstensi pg_hint_plan mengidentifikasi tabel berdasarkan alias tersebut. Metode pemindaian yang didukung mencakup SeqScan, IndexScan, dan lainnya.
  
  Hint metode pemindaian berlaku pada tabel biasa, tabel turunan, tabel unlogged, tabel temporary, dan tabel sistem. Namun, hint metode pemindaian tidak berlaku pada tabel eksternal, fungsi tabel, pernyataan yang menetapkan nilai konstanta, ekspresi universal, view, dan subkueri.
  Contoh:
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
- Petunjuk metode join
  
  Jenis hint ini menentukan metode yang digunakan untuk menggabungkan tabel-tabel tertentu. Hint metode join berlaku pada tabel biasa, tabel turunan, tabel unlogged, tabel temporary, tabel eksternal, tabel sistem, fungsi tabel, pernyataan yang menetapkan nilai konstanta, dan ekspresi universal. Hint metode join tidak berlaku pada view dan subkueri.
- Hint urutan join
  Hint urutan join menentukan urutan join untuk dua atau lebih tabel. Anda dapat menggunakan salah satu metode berikut untuk memaksakan urutan join:
  - Memaksakan urutan join tertentu tanpa membatasi arah pada setiap level join.
  - Memaksakan arah join.
  Contoh:
```
 /*+
    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
  Dalam contoh ini:
  
  NestLoop(t1 t2): menentukan metode join untuk tabel t1 dan t2.
  
  MergeJoin(t1 t2 t3): menentukan metode join untuk tabel t1, t2, dan t3.
  
  Leading(t1 t2 t3): menentukan urutan join untuk ketiga tabel tersebut.
- Petunjuk Koreksi Nomor Baris
  
  Jenis hint ini mengoreksi kesalahan jumlah baris yang disebabkan oleh keterbatasan pengoptimal.
```
 /*+ Rows(a b #10) */ SELECT... ; # Menetapkan 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.
```
- Hint eksekusi paralel
  
  Jenis hint ini menentukan rencana yang digunakan untuk mengeksekusi Pernyataan SQL secara paralel.
  
  Hint eksekusi paralel berlaku pada tabel biasa, tabel turunan, tabel unlogged, dan tabel sistem. Namun, hint eksekusi paralel tidak berlaku pada tabel eksternal, klausa yang menetapkan nilai konstanta, ekspresi universal, view, dan subkueri. Anda dapat menentukan tabel internal dalam suatu view menggunakan nama asli atau alias-nya.
  Contoh berikut menunjukkan cara menjalankan kueri dengan tingkat paralelisme berbeda pada setiap tabel:
  - Contoh 1: Tetapkan degree of parallelism (DOP) 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)
                                        
```
  - Contoh 2: Tetapkan degree of parallelism (DOP) 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)
```
- Hint pengaturan parameter GUC
  
  Jenis hint ini mengubah sementara nilai parameter GUC. Nilai parameter GUC hanya berlaku saat pelaksana menghasilkan rencana eksekusi. Nilai-nilai ini membantu Anda meningkatkan performa kueri tanpa memengaruhi seluruh sesi. Jika Anda menetapkan lebih dari satu hint pada parameter GUC, hint terbaru yang berlaku.
  Contoh:
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

Hint syntax

Tabel berikut menjelaskan sintaks untuk semua hint yang didukung. Anda dapat menambahkan hint-hint ini ke kueri Anda dalam blok komentar. Parameter opsional diapit tanda kurung siku [].

Jenis	Sintaks	Deskripsi
Hint metode pemindaian	SeqScan(table)	Memaksa pemindaian sekuensial pada tabel yang ditentukan.
	TidScan(table)	Memaksa pemindaian Tuple ID (TID) pada tabel yang ditentukan.
	IndexScan(table[ index...])	Memaksa pemindaian indeks pada tabel yang ditentukan. Anda dapat menentukan satu atau beberapa indeks yang akan digunakan.
	IndexOnlyScan(table[ index...])	Memaksa pemindaian hanya-indeks pada tabel yang ditentukan. Anda dapat menentukan satu atau beberapa indeks yang akan digunakan.
	BitmapScan(table[ index...])	Memaksa pemindaian indeks bitmap pada tabel yang ditentukan. Anda dapat menentukan satu atau beberapa indeks yang akan digunakan.
	NoSeqScan(table)	Melarang pemindaian sekuensial pada tabel yang ditentukan.
	NoTidScan(table)	Melarang pemindaian TID pada tabel yang ditentukan.
	NoIndexScan(table)	Melarang pemindaian indeks pada tabel yang ditentukan.
	NoIndexOnlyScan(table)	Melarang pemindaian hanya-indeks pada tabel yang ditentukan.
	NoBitmapScan(table)	Melarang pemindaian indeks bitmap pada tabel yang ditentukan.
Hint metode join	NestLoop(table table[ table...])	Memaksa join loop bersarang untuk tabel-tabel yang ditentukan.
	HashJoin(table table[ table...])	Memaksa join hash untuk tabel-tabel yang ditentukan.
	MergeJoin(table table[ table...])	Memaksa join merge untuk tabel-tabel yang ditentukan.
	NoNestLoop(table table[ table...])	Melarang join loop bersarang untuk tabel-tabel yang ditentukan.
	NoHashJoin(table table[ table...])	Melarang join hash untuk tabel-tabel yang ditentukan.
	NoMergeJoin(table table[ table...])	Melarang join merge untuk tabel-tabel yang ditentukan.
Hint urutan join	Leading(table table[ table...])	Menentukan urutan join untuk tabel-tabel tersebut.
Hint urutan join	Leading(<join pair>)	Menentukan urutan dan arah join untuk dua tabel.
Hint koreksi jumlah baris	Rows(table table[ table...] correction)	Mengoreksi perkiraan jumlah baris untuk hasil join. Metode koreksi yang tersedia meliputi nilai absolut (#<n>), penambahan (+<n>), pengurangan (-<n>), dan perkalian (*<n>), di mana <n> adalah jumlah baris.
Hint eksekusi paralel	Parallel(table <# of workers> [soft\|hard])	Memaksa atau menonaktifkan pemindaian paralel pada tabel yang ditentukan. Catatan <# of workers> menentukan tingkat paralelisme (DOP) yang diinginkan, yaitu jumlah proses worker paralel. Nilai 0 menonaktifkan paralelisme. Jika parameter ketiga adalah `soft` (default), hint hanya memodifikasi nilai parameter `max_parallel_workers_per_gather`, dan pengoptimal menentukan tingkat paralelisme aktual. Parameter `hard` memaksa tingkat paralelisme yang ditentukan.
	PX(<# of workers>)	Menentukan eksekusi paralel lintas node. Catatan <# of workers> menentukan tingkat paralelisme (DOP).
	NoPX()	Mencegah kueri menggunakan eksekusi paralel lintas node.
Hint pengaturan parameter GUC	Set(GUC-param value)	Menetapkan parameter GUC ke nilai yang ditentukan saat pengoptimal menghasilkan rencana eksekusi.

Catatan

Anda juga dapat menggunakan pg_hint_plan untuk menentukan rencana eksekusi untuk eksekusi paralel lintas node. Namun, dalam skenario eksekusi paralel lintas node, hint koreksi jumlah baris tidak didukung. Hint metode join hanya dapat diterapkan pada join antara dua tabel, dan hint urutan join hanya dapat menentukan urutan untuk semua tabel yang terlibat.