cara menggunakan ekstensi penyesuaian rencana kueri - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL menyediakan ekstensi pg_hint_plan, yang memungkinkan Anda menggunakan pernyataan komentar khusus sebagai petunjuk untuk memodifikasi rencana eksekusi PostgreSQL.

Prasyarat

Instans menjalankan RDS PostgreSQL 10 atau yang lebih baru.
Catatan
Jika Anda tidak dapat membuat ekstensi, tingkatkan versi mesin minor terlebih dahulu. Sebagai contoh, untuk instans RDS PostgreSQL 17, versi mesin minor harus 20241030 atau yang lebih baru.
Anda telah membuat akun istimewa untuk instans RDS PostgreSQL. Untuk informasi lebih lanjut, lihat Buat akun.

Informasi latar belakang

PostgreSQL menggunakan pengoptimal berbasis biaya yang memanfaatkan statistik data daripada aturan statis. Untuk sebuah Pernyataan SQL, pengoptimal mengevaluasi biaya dari setiap rencana eksekusi yang mungkin dan memilih yang memiliki biaya paling rendah. Pengoptimal mencoba memilih rencana eksekusi terbaik, tetapi karena tidak memahami beberapa hubungan inheren yang mungkin ada dalam data, rencana eksekusi ini mungkin tidak sempurna. Anda dapat menggunakan ekstensi pg_hint_plan untuk menambahkan petunjuk ke pernyataan SQL. Petunjuk tersebut menentukan bagaimana Anda ingin mengeksekusi pernyataan SQL. Dengan cara ini, Anda dapat mengoptimalkan rencana eksekusi dari pernyataan SQL.

Instal dan uninstall ekstensi

Kelola ekstensi di Konsol

Instal Ekstensi
1. Kunjungi daftar instans RDS, pilih Wilayah di bilah navigasi atas, lalu klik ID instans target.
2. Di panel navigasi sisi kiri, klik Plug-ins.
3. Di halaman Manage Plug-ins, klik tab Not Installed, cari ekstensi pg_hint_plan, dan klik kolom Operation pada tombol Install.
4. Dalam kotak dialog yang muncul, pilih database target dan akun istimewa, lalu klik Install untuk menginstal ekstensi ke database target.
  Saat status instans berubah dari Maintaining Instance menjadi Running, ekstensi berhasil diinstal.
Perbarui dan Uninstall Ekstensi
- Di halaman Manage Plug-ins, klik tab Installed, dan klik kolom Operation pada tombol Upgrade Version untuk memperbarui ekstensi target ke versi terbaru.
  Catatan
  Jika kolom Operation tidak memiliki tombol Upgrade Version, ekstensi sudah merupakan versi terbaru.
- Di halaman Manage Plug-ins, klik tab Installed, dan klik kolom Operation pada tombol Uninstall untuk menghapus ekstensi target.

Kelola ekstensi menggunakan perintah SQL

Atur parameter instans, dan tambahkan pg_hint_plan ke Running Value dari shared_preload_libraries. Sebagai contoh, ubah Running Value menjadi 'pg_stat_statements,auto_explain,pg_hint_plan'.
Gunakan akun istimewa untuk terhubung ke database tempat Anda ingin menginstal ekstensi, dan jalankan pernyataan SQL berikut untuk mengelola ekstensi.
- Instal Ekstensi
```
CREATE EXTENSION pg_hint_plan;
```
- Uninstall Ekstensi
```
DROP EXTENSION pg_hint_plan;
```

Petunjuk komentar

Petunjuk komentar pg_hint_plan dimulai dengan /*+ dan diakhiri dengan */.

Sebuah pernyataan petunjuk terdiri dari nama petunjuk dan parameternya dalam tanda kurung, dengan parameter dipisahkan oleh spasi. Untuk keterbacaan yang lebih baik, setiap pernyataan petunjuk dapat dimulai pada baris baru.

Contoh

Gunakan HashJoin sebagai metode join dan gunakan SeqScan untuk memindai tabel test_table01.

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM test_table02 b
    JOIN test_table01 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 test_table01 a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on test_table02 b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

Dalam kueri SQL, jika alias digunakan untuk nama tabel, alias yang sama juga harus digunakan dalam pernyataan petunjuk pg_hint_plan.

Contoh

Pernyataan petunjuk dengan alias:

/*+ IndexScan(t) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

Hasil berikut dikembalikan:

             QUERY PLAN              
-------------------------------------
 Index Scan using tbl_a_idx on tbl t
   Index Cond: (a = 1)

Pernyataan petunjuk tanpa alias:

/*+ IndexScan(tbl) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

Hasil berikut dikembalikan:

    QUERY PLAN     
-------------------
 Seq Scan on tbl t
   Filter: (a = 1)

Tabel petunjuk

Petunjuk dapat digunakan untuk mengoptimalkan rencana eksekusi pernyataan SQL. Namun, ini hanya nyaman ketika pernyataan SQL dapat diedit. Jika pernyataan SQL tidak dapat diedit, Anda dapat menempatkan petunjuk dalam tabel bernama hint_plan.hints. Tabel hint_plan.hints berisi kolom-kolom yang dijelaskan dalam tabel berikut.

Catatan

Secara default, pengguna yang membuat ekstensi pg_hint_plan memiliki izin pada tabel hint_plan.hints. Petunjuk dalam tabel hint_plan.hints memiliki prioritas lebih tinggi daripada petunjuk yang Anda tambahkan menggunakan ekstensi pg_hint_plan.

Kolom	Deskripsi
id	ID petunjuk. ID ini unik dan dibuat secara otomatis.
norm_query_string	Pola yang cocok dengan pernyataan SQL tempat Anda ingin menambahkan petunjuk. Konstanta dalam kueri harus diganti dengan `?`. Spasi adalah bagian penting dari pola.
application_name	Nama aplikasi tempat petunjuk diterapkan. Jika parameter ini dibiarkan kosong, petunjuk diterapkan ke semua aplikasi.
hints	Komentar yang berisi petunjuk. Anda tidak perlu menyertakan tanda komentar.

Aktifkan tabel petunjuk

SET pg_hint_plan.enable_hint_table = on;

Masukkan data ke dalam tabel petunjuk

INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
    'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
    '',
    'SeqScan(t1)'
);

Perbarui data dalam tabel petunjuk

UPDATE hint_plan.hints
SET hints = 'IndexScan(t1)'
WHERE id = 1;

Hapus data dari tabel petunjuk

DELETE FROM hint_plan.hints
WHERE id = 1;

Jenis petunjuk

Berdasarkan bagaimana frasa petunjuk memengaruhi rencana eksekusi, mereka dapat dikategorikan ke dalam enam jenis berikut:

Petunjuk untuk Metode Pemindaian
Jenis petunjuk ini menentukan metode yang digunakan untuk memindai tabel yang ditentukan. Jika tabel yang ditentukan memiliki alias, ekstensi pg_hint_plan mengidentifikasi tabel berdasarkan alias. Metode pemindaian termasuk SeqScan, IndexScan, NoSeqScan, dan lainnya.
Petunjuk untuk metode pemindaian valid pada tabel biasa, tabel warisan, tabel tanpa log, tabel sementara, dan tabel sistem. Petunjuk untuk metode pemindaian tidak valid pada tabel eksternal, fungsi tabel, pernyataan di mana nilai konstan ditentukan, ekspresi universal, tampilan, dan subkueri.
Contoh
```
/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
```
Petunjuk untuk Metode Join
Jenis petunjuk ini menentukan metode yang digunakan untuk menggabungkan tabel yang ditentukan.
Petunjuk untuk metode join valid pada tabel biasa, tabel warisan, tabel tanpa log, tabel sementara, tabel eksternal, tabel sistem, fungsi tabel, pernyataan di mana nilai konstan ditentukan, dan ekspresi universal. Petunjuk untuk metode join tidak valid pada tampilan dan subkueri.
Petunjuk untuk Urutan Join
Jenis petunjuk ini menentukan urutan di mana dua atau lebih tabel digabungkan. Anda dapat menggunakan salah satu dari metode berikut untuk menentukan petunjuk yang menentukan urutan join:
- Tentukan urutan di mana Anda ingin menggabungkan tabel yang ditentukan tanpa perlu membatasi arah di setiap level join.
- Tentukan urutan di mana Anda ingin menggabungkan tabel yang ditentukan dan arah di setiap level join.
Contoh
```
-- Tentukan urutan join tanpa membatasi arah di setiap level join
/*+
    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);

-- Tentukan urutan join dan arah
/*+
    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);
```

Petunjuk untuk Koreksi Jumlah Baris

Jenis petunjuk ini mengoreksi kesalahan jumlah baris yang disebabkan oleh pengoptimal.

Contoh

/*+ Rows(a b #10) */ SELECT... ;     //Set jumlah baris hasil join menjadi 10.
/*+ Rows(a b +10) */ SELECT... ;     //Tambahkan jumlah baris sebesar 10.
/*+ Rows(a b -10) */ SELECT... ;     //Kurangi jumlah baris sebesar 10.
/*+ Rows(a b *10) */ SELECT... ;     //Kalikan jumlah baris dengan 10.

Petunjuk untuk Eksekusi Paralel

Jenis petunjuk ini menentukan rencana yang digunakan untuk mengeksekusi pernyataan SQL secara paralel.

Petunjuk untuk eksekusi paralel valid pada tabel biasa, tabel warisan, tabel tanpa log, dan tabel sistem. Petunjuk untuk eksekusi paralel tidak valid pada tabel eksternal, klausa di mana nilai konstan ditentukan, ekspresi universal, tampilan, dan subkueri. Anda dapat menentukan tabel internal dari tampilan berdasarkan nama asli atau alias mereka.

Contoh berikut menunjukkan bagaimana pernyataan SQL dieksekusi dengan cara yang berbeda pada setiap tabel:

Contoh

Metode 1

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

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)

Petunjuk untuk Pengaturan Parameter GUC
Jenis petunjuk ini sementara mengubah nilai parameter GUC. Nilai parameter GUC dalam rencana eksekusi membantu Anda mencapai efek yang Anda harapkan. Namun, ini tidak berlaku jika petunjuk yang ditentukan bertentangan dengan rencana eksekusi pernyataan SQL lainnya. Jika Anda mengonfigurasi parameter GUC lebih dari satu kali, nilai terbaru yang diatur akan berlaku.
Contoh
```
/*+ Set(random_page_cost 2.0) */
SELECT * FROM table1 t1 WHERE key = 'value';
```

Format petunjuk yang didukung

Kategori	Format	Deskripsi
Petunjuk untuk metode pemindaian	SeqScan(table)	Memaksa pemindaian berurutan pada tabel.
	TidScan(table)	Memaksa pemindaian identifier tupel (TID) pada tabel.
	IndexScan(table[ index...])	Menentukan pemindaian indeks. Anda dapat menentukan indeks.
	IndexOnlyScan(table[ index...])	Menentukan pemindaian indeks saja. Anda dapat menentukan indeks.
	BitmapScan(table[ index...])	Menentukan pemindaian bitmap.
	NoSeqScan(table)	Melarang pemindaian berurutan.
	NoTidScan(table)	Melarang pemindaian TID.
	NoIndexScan(table)	Melarang pemindaian indeks.
	NoIndexOnlyScan(table)	Melarang pemindaian indeks. Hanya tabel yang dipindai.
	NoBitmapScan(table)	Melarang pemindaian bitmap.
Petunjuk untuk metode join	NestLoop(table table[ table...])	Menentukan nested loop join.
	HashJoin(table table[ table...])	Menentukan hash join.
	MergeJoin(table table[ table...])	Menentukan merge join.
	NoNestLoop(table table[ table...])	Melarang nested loop join.
	NoHashJoin(table table[ table...])	Melarang hash join.
	NoMergeJoin(table table[ table...])	Melarang merge join.
Petunjuk untuk urutan join	Leading(table table[ table...])	Menentukan urutan join.
Petunjuk untuk urutan join	Leading(<pasangan join>)	Menentukan urutan dan arah join.
Petunjuk untuk koreksi jumlah baris	Rows(table table[ table...] koreksi)	Mengoreksi jumlah baris hasil join yang diperoleh dari tabel yang ditentukan. Metode koreksi yang tersedia termasuk nilai absolut `#<n>`, penambahan `+ <n>`, pengurangan `-<n>`, dan perkalian `* <n>`. `<n>` adalah angka yang dapat dibaca oleh fungsi strtod.
Petunjuk untuk eksekusi paralel	Parallel(table <# pekerja> [soft\|hard])	Menentukan atau melarang eksekusi paralel tabel yang ditentukan. `<pekerja#>` adalah jumlah pekerja paralel yang ingin Anda gunakan. Jika Anda mengatur parameter ini ke 0, eksekusi paralel dilarang. Jika Anda mengatur parameter ketiga ke soft, hanya nilai parameter max_parallel_workers_per_gather yang diubah dan parameter lainnya ditentukan oleh pengoptimal. Jika Anda mengatur parameter ketiga ke hard, nilai semua parameter terkait diubah. Nilai default parameter ketiga adalah soft.
Petunjuk untuk pengaturan parameter GUC	Set(GUC-param value)	Menentukan nilai parameter GUC saat pengoptimal berjalan.

Untuk informasi lebih lanjut, lihat pg_hint_plan.