全部产品
Search

搜索本产品

    PolarDB：pg_hint_plan

    文档中心

    PolarDB：pg_hint_plan

    更新时间：Jul 02, 2025

    Anda dapat menggunakan ekstensi pg_hint_plan untuk menambahkan petunjuk ke pernyataan SQL, yang menentukan cara menjalankan pernyataan tersebut. Dengan demikian, Anda dapat mengoptimalkan rencana eksekusinya.

    Informasi latar belakang

    PostgreSQL menggunakan Optimalisasi berbasis biaya yang mengandalkan statistik data dibandingkan aturan statis. Optimizer mengevaluasi biaya dari semua rencana eksekusi yang mungkin untuk sebuah pernyataan SQL dan menjalankan rencana dengan biaya terendah. Meskipun optimizer berusaha semaksimal mungkin, rencana eksekusi yang dipilih mungkin bukan yang terbaik karena tidak mempertimbangkan hubungan mendasar antara data.

    Anda dapat menentukan variabel Grand Unified Scheme (GUC) untuk menyesuaikan rencana eksekusi, tetapi ini akan memengaruhi seluruh sesi. Jika Anda ingin menghindari dampak pada seluruh sesi, gunakan pg_hint_plan untuk mengoptimalkan satu rencana eksekusi tunggal.

    Peringatan

    • Data Management (DMS) tidak mendukung petunjuk. Anda harus menggunakan metode lain untuk mengakses database.
    • pg_hint_plan hanya membaca petunjuk dari blok komentar pertama.
    • pg_hint_plan hanya menerima huruf, angka, spasi, dan karakter khusus berikut: _ , ( ). Karakter lainnya akan langsung menghentikan proses penguraian.
    • pg_hint_plan membandingkan nama objek secara sensitif terhadap huruf besar/kecil, berbeda dengan PostgreSQL yang tidak sensitif terhadap huruf besar/kecil. Sebagai contoh, objek bernama TBL dalam petunjuk hanya cocok dengan TBL di database. Objek bernama tbl atau Tbl akan diabaikan.

    Batasan

    Penggunaan pg_hint_plan untuk prosedur penyimpanan yang didefinisikan menggunakan PL/pgSQL memiliki batasan berikut:
    • Petunjuk hanya berlaku untuk jenis pernyataan berikut:
      • Pernyataan SELECT, INSERT, UPDATE, dan DELETE.
      • Pernyataan RETURN QUERY.
      • Pernyataan EXECUTE QUERY.
      • Pernyataan OPEN.
      • Pernyataan FOR.
    • Petunjuk harus ditempatkan langsung setelah kata pertama dari pernyataan SQL. Jika petunjuk ditempatkan sebelum kata pertama, maka tidak akan dianggap sebagai bagian dari query.

    Membuat dan memuat ekstensi pg_hint_plan

    1. Buat ekstensi. 
      CREATE EXTENSION pg_hint_plan;
    2. Muat ekstensi.
      • Muat ekstensi secara otomatis untuk satu pengguna.
        • Jalankan pernyataan berikut untuk memuat ekstensi. 
          ALTER USER xxx set session_preload_libraries='pg_hint_plan';
          Catatan Ganti xxx dalam pernyataan tersebut dengan nama pengguna aktual.
        • Jalankan pernyataan berikut untuk memuat ekstensi untuk satu database. 
          ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
        Catatan Jika akun tidak dapat masuk ke database karena konfigurasi yang tidak tepat, jalankan salah satu pernyataan berikut untuk masuk ke PolarDB melalui akun atau database lain dan reset konfigurasinya.
        ALTER USER xxx reset session_preload_libraries;
ALTER DATABASE xxx reset session_preload_libraries;
      • Muat ekstensi secara otomatis untuk kluster database.

        Untuk menggunakan ekstensi pg_hint_plan, kunjungi Quota Center. Klik Apply di kolom Actions yang sesuai dengan polardb_pg_pg_hint_plan.

      • Periksa apakah ekstensi telah dimuat.
        1. Jalankan pernyataan berikut untuk mengizinkan informasi debug dikirim ke klien. 
          SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
        2. Jalankan pernyataan berikut untuk memeriksa apakah ekstensi telah dimuat. 
          /*+Set(enable_seqscan 1)*/select 1;
          Jika ekstensi telah dimuat, informasi berikut akan dikembalikan. 
          NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
        3. Jalankan pernyataan berikut untuk menghentikan pengiriman informasi debug ke klien. 
          RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;

    Catatan penggunaan

    • Basic usage

      Petunjuk dimulai dengan kombinasi garis miring maju, tanda bintang, dan tanda tambah (/*+) serta diakhiri dengan kombinasi tanda bintang dan garis miring maju (*/). Petunjuk terdiri dari nama petunjuk dan parameter. Parameter diapit oleh sepasang tanda kurung () dan dipisahkan oleh spasi. Untuk meningkatkan keterbacaan, Anda dapat memulai setiap petunjuk pada baris baru.

      Contoh:

      Dalam contoh ini, HashJoin digunakan sebagai metode penggabungan, dan tabel pgbench_accounts dipindai menggunakan metode SeqScan.
      /*+
   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)
    • Hint table
      Petunjuk dapat digunakan untuk mengoptimalkan rencana eksekusi pernyataan SQL. Namun, cara ini hanya nyaman jika pernyataan SQL dapat diedit. Jika pernyataan SQL tidak dapat diedit, Anda dapat menempatkan petunjuk dalam tabel bernama hint_plan.hints. Tabel tersebut terdiri dari kolom-kolom berikut.
      KolomDeskripsi
      idID petunjuk. ID ini unik dan dibuat secara otomatis.
      norm_query_stringPola yang cocok dengan pernyataan SQL tempat Anda ingin menambahkan petunjuk. Konstanta dalam pernyataan SQL harus diganti dengan tanda tanya (?) yang berfungsi sebagai wildcard. Karakter spasi adalah bagian penting dari pola.
      application_nameNama aplikasi tempat petunjuk diterapkan. Jika parameter ini dibiarkan kosong, petunjuk diterapkan ke semua aplikasi.
      hintsKomentar yang berisi petunjuk. Anda tidak perlu menyertakan tanda komentar.
      Pernyataan berikut menunjukkan contoh tabel petunjuk. Secara default, pengguna yang membuat ekstensi pg_hint_plan memiliki izin pada tabel petunjuk. Ketika petunjuk ditambahkan ke komentar pernyataan dan tabel petunjuk, petunjuk dalam tabel petunjuk lebih diutamakan daripada petunjuk dalam komentar.
      INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
    VALUES (
        'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
        '',
        'SeqScan(t1)'
    );
INSERT 0 1
 UPDATE hint_plan.hints
    SET hints = 'IndexScan(t1)'
  WHERE id = 1;
UPDATE 1
 DELETE FROM hint_plan.hints
  WHERE id = 1;
DELETE 1

    Jenis petunjuk

    • Hint types

      Petunjuk dikategorikan menjadi enam jenis berdasarkan pengaruhnya terhadap rencana eksekusi:

      • Hints for scan methods

        Jenis petunjuk ini menentukan metode yang digunakan untuk memindai tabel tertentu. Jika tabel memiliki alias, ekstensi pg_hint_plan mengidentifikasi tabel berdasarkan alias tersebut. Metode pemindaian yang didukung mencakup SeqScan, IndexScan, dan lainnya.

        Petunjuk untuk metode pemindaian efektif pada tabel biasa, tabel warisan, tabel tanpa log, tabel sementara, dan tabel sistem. Namun, petunjuk ini tidak berlaku untuk tabel eksternal, fungsi tabel, pernyataan dengan nilai konstan, ekspresi universal, tampilan, serta subquery.

        Contoh:
        /*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
      • Hints for join methods

        Jenis petunjuk ini menentukan metode yang digunakan untuk menggabungkan tabel tertentu. Petunjuk untuk metode penggabungan efektif pada tabel biasa, tabel warisan, tabel tanpa log, tabel sementara, tabel eksternal, tabel sistem, fungsi tabel, pernyataan dengan nilai konstan, dan ekspresi universal. Namun, petunjuk ini tidak berlaku untuk tampilan dan subquery.

      • Hints for join order
        Jenis petunjuk ini menentukan urutan penggabungan dua atau lebih tabel. Anda dapat menggunakan salah satu metode berikut untuk menentukan petunjuk urutan penggabungan:
        • Tentukan urutan penggabungan tabel tanpa membatasi arah pada setiap tingkat penggabungan.
        • Tentukan urutan penggabungan tabel beserta arah pada setiap tingkat penggabungan.
        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 Komponen:
        • NestLoop(t1 t2): menentukan metode penggabungan tabel t1 dan t2.
        • MergeJoin(t1 t2 t3): menentukan metode penggabungan tabel t1, t2, dan t3.
        • Leading(t1 t2 t3): menentukan urutan penggabungan ketiga tabel.
      • Hints for row number correction

        Jenis petunjuk ini mengoreksi kesalahan jumlah baris yang disebabkan oleh pembatasan optimizer.

         /*+ Rows(a b #10) */ SELECT... ; # Mengatur jumlah baris hasil penggabungan menjadi 10.
 /*+ Rows(a b +10) */ SELECT... ; # Meningkatkan jumlah baris sebesar 10.
 /*+ Rows(a b -10) */ SELECT... ; # Mengurangi jumlah baris sebesar 10.
 /*+ Rows(a b *10) */ SELECT... ; # Meningkatkan jumlah baris sebesar 10 kali.
      • Hints for parallel execution

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

        Petunjuk untuk eksekusi paralel efektif pada tabel biasa, tabel warisan, tabel tanpa log, dan tabel sistem. Namun, petunjuk ini tidak berlaku untuk tabel eksternal, klausa dengan nilai konstan, ekspresi universal, tampilan, dan subquery. Anda dapat menentukan tabel internal dari tampilan menggunakan nama asli atau alias mereka.

        Contoh berikut menunjukkan cara pernyataan SQL dieksekusi secara berbeda pada setiap tabel:
        • Contoh 1: Atur derajat paralelisme (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: Atur 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)
      • Hints for GUC parameter setting

        Jenis petunjuk ini sementara mengubah nilai parameter GUC. Nilai parameter GUC hanya berlaku saat executor menghasilkan rencana eksekusi. Nilai-nilai ini membantu meningkatkan kinerja query tanpa memengaruhi seluruh sesi. Jika lebih dari satu petunjuk diatur untuk parameter GUC, petunjuk terbaru yang diterapkan.

        Contoh:
         /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
    • List of hint syntaxes
      JenisSintaksis petunjukDeskripsi
      Petunjuk untuk metode pemindaianSeqScan(table)Menentukan pemindaian urutan.
      TidScan(table)Menentukan pemindaian TID.
      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. Anda dapat menentukan indeks.
      NoSeqScan(table)Melarang pemindaian urutan.
      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 penggabunganNestLoop(table table[ table...])Menentukan penggabungan loop bersarang.
      HashJoin(table table[ table...])Menentukan penggabungan hash.
      MergeJoin(table table[ table...])Menentukan penggabungan merge.
      NoNestLoop(table table[ table...])Melarang penggabungan loop bersarang.
      NoHashJoin(table table[ table...])Melarang penggabungan hash.
      NoMergeJoin(table table[ table...])Melarang penggabungan merge.
      Petunjuk untuk urutan penggabunganLeading(table table[ table...])Menentukan urutan penggabungan.
      Leading(<join pair>)Menentukan urutan dan arah penggabungan.
      Petunjuk untuk koreksi jumlah barisRows(table table[ table...] correction)Mengoreksi jumlah baris hasil penggabungan yang diperoleh dari tabel yang ditentukan. Operator berikut didukung: #<n>, + <n>, -<n>, dan * <n>. Operator <n> didukung oleh fungsi strtod.
      Petunjuk untuk eksekusi paralelParallel(table <# of workers> [soft|hard])Menentukan atau melarang eksekusi paralel tabel yang ditentukan.
      Catatan
      • Parameter <worker#> menentukan jumlah program kerja yang diperlukan. Nilai 0 menentukan untuk melarang eksekusi paralel.
      • Jika parameter ketiga diatur ke soft, hanya nilai parameter max_parallel_workers_per_gather yang diubah dan parameter lainnya ditentukan oleh optimizer.
      • Jika parameter ketiga diatur ke hard, nilai semua parameter terkait diubah. Parameter ketiga diatur ke soft secara default.
      PX(<# of workers>)Menentukan eksekusi paralel lintas node.
      Catatan <# of workers> menentukan DOP.
      NoPX()Melarang eksekusi paralel lintas node.
      Petunjuk untuk pengaturan parameter GUCSet(GUC-param value)Menentukan nilai parameter GUC saat optimizer berjalan.
      Catatan pg_hint_plan juga dapat menentukan rencana eksekusi yang dihasilkan selama eksekusi paralel lintas node. Selama eksekusi paralel lintas node, petunjuk Rows(table table[ table...] correction) tidak didukung. Petunjuk untuk metode penggabungan hanya berlaku untuk menggabungkan dua tabel, sedangkan petunjuk untuk urutan penggabungan hanya dapat digunakan untuk semua tabel.