Meningkatkan rencana kueri PostgreSQL Aurora - Amazon Aurora
Mengevaluasi performa rencanaMemperbaiki rencana menggunakan pg_hint_plan

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Meningkatkan rencana kueri PostgreSQL Aurora

Meningkatkan manajemen rencana kueri dengan mengevaluasi kinerja rencana dan memperbaiki rencana. Untuk informasi selengkapnya tentang meningkatkan rencana kueri Anda, lihat topik berikut.

Mengevaluasi performa rencana

Setelah pengoptimal mengambil rencana sebagai tidak disetujui, gunakan fungsi apg_plan_mgmt.evolve_plan_baselines untuk membandingkan rencana berdasarkan performanya yang sebenarnya. Bergantung pada hasil eksperimen performa Anda, Anda dapat mengubah status rencana dari tidak disetujui ke disetujui atau ditolak. Anda dapat memutuskan untuk menggunakan fungsi apg_plan_mgmt.evolve_plan_baselines untuk menonaktifkan rencana secara sementara jika tidak memenuhi persyaratan Anda.

Menyetujui rencana yang lebih baik

Contoh berikut menunjukkan cara mengubah status rencana terkelola menjadi disetujui menggunakan fungsi apg_plan_mgmt.evolve_plan_baselines. 

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';
NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)

Output menampilkan laporan performa untuk pernyataan rangequery dengan ikatan parameter 1 dan 10.000. Rencana baru yang belum disetujui (Baseline+1) lebih baik daripada rencana terbaik yang telah disetujui sebelumnya (Baseline). Untuk mengonfirmasi bahwa rencana yang baru sekarang berstatus Approved, periksa tampilan apg_plan_mgmt.dba_plans. 

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;
sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

Rencana terkelola kini mencakup dua rencana yang disetujui yang merupakan acuan dasar rencana pernyataan. Anda juga dapat memanggil fungsi apg_plan_mgmt.set_plan_status untuk mengatur langsung bidang status rencana ke 'Approved', 'Rejected', 'Unapproved', atau 'Preferred'.

Menolak atau menonaktifkan rencana yang lebih lambat

Untuk menolak atau menonaktifkan rencana, teruskan 'reject' atau 'disable' sebagai parameter tindakan ke fungsi apg_plan_mgmt.evolve_plan_baselines. Contoh ini menonaktifkan setiap rencana Unapproved yang diambil yang lebih lambat setidaknya 10 persen dari rencana Approved terbaik untuk pernyataan. 

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

Anda juga dapat secara langsung mengatur rencana ke ditolak atau dinonaktifkan. Untuk secara langsung mengatur bidang yang diaktifkan untuk rencana ke true atau false, panggil fungsi apg_plan_mgmt.set_plan_enabled. Untuk secara langsung mengatur bidang status rencana ke 'Approved', 'Rejected', 'Unapproved', atau 'Preferred', panggil fungsi apg_plan_mgmt.set_plan_status.

Untuk menghapus rencana yang tidak valid dan yang Anda perkirakan akan tetap tidak valid, gunakan fungsi apg_plan_mgmt.validate_plans. Fungsi ini memungkinkan Anda menghapus atau menonaktifkan rencana yang tidak valid. Untuk informasi selengkapnya, lihat Memvalidasi rencana.

Memperbaiki rencana menggunakan pg_hint_plan

Pengoptimal kueri dirancang dengan baik untuk menemukan rencana yang optimal untuk semua pernyataan, dan pada sebagian besar kasus, pengoptimal menemukan rencana yang baik. Namun, terkadang Anda mungkin tahu bahwa ada rencana yang jauh lebih baik dari yang dihasilkan oleh pengoptimal. Dua cara yang disarankan agar pengoptimal menghasilkan rencana yang diinginkan adalah menggunakan ekstensi pg_hint_plan atau mengatur variabel Grand Unified Configuration (GUC) di PostgreSQL:

  • Ekstensi pg_hint_plan – Menentukan "petunjuk" untuk memodifikasi cara perencana bekerja menggunakan ekstensi pg_hint_plan PostgreSQL. Untuk menginstal dan mempelajari selengkapnya tentang cara menggunakan ekstensi pg_hint_plan, lihat dokumentasi pg_hint_plan.

  • Variabel GUC – Menulis ulang satu atau beberapa parameter model biaya atau parameter pengoptimal lainnya, seperti from_collapse_limit atau GEQO_threshold.

Saat Anda menggunakan salah satu teknik ini untuk memaksa pengoptimal kueri agar menggunakan suatu rencana, Anda juga dapat menggunakan manajemen rencana kueri untuk mengambil dan memberlakukan penggunaan rencana baru.

Anda dapat menggunakan ekstensi pg_hint_plan untuk mengubah urutan join, metode join, atau jalur akses untuk pernyataan SQL. Anda menggunakan komentar SQL dengan sintaksis pg_hint_plan khusus untuk memodifikasi cara pengoptimal membuat rencana. Misalnya, anggaplah pernyataan SQL yang bermasalah memiliki join dua arah. 

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

Kemudian, anggaplah bahwa pengoptimal memilih urutan join (t1, t2), tetapi Anda tahu bahwa urutan join (t2, t1) lebih cepat. Petunjuk berikut memaksa pengoptimal untuk menggunakan urutan join yang lebih cepat, (t2, t1). Sertakan EXPLAIN sehingga pengoptimal menghasilkan rencana untuk pernyataan SQL, tetapi tanpa menjalankan pernyataan ini. (Output tidak ditampilkan.)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

Langkah-langkah berikut menunjukkan cara menggunakan pg_hint_plan.

Untuk mengubah rencana yang dibuat oleh pengoptimal dan mengambil rencana menggunakan pg_hint_plan

  1. Aktifkan mode pengambilan manual.

    SET apg_plan_mgmt.capture_plan_baselines = manual;

  2. Tentukan petunjuk untuk pernyataan SQL yang ingin diamati. 

    /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

    Setelah proses ini berjalan, pengoptimal mengambil rencana dalam tampilan apg_plan_mgmt.dba_plans. Rencana yang diambil tidak mencakup sintaksis komentar pg_hint_plan khusus karena manajemen rencana kueri menormalisasi pernyataan dengan menghapus komentar awal.

  3. Lihat rencana terkelola menggunakan tampilan apg_plan_mgmt.dba_plans.

    SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
FROM apg_plan_mgmt.dba_plans;

  4. Atur status rencana menjadi Preferred. Tindakan ini memastikan bahwa pengoptimal memilih untuk menjalankannya dan tidak memilih dari rencana yang disetujui, saat rencana berbiaya minimum belum berstatus Approved atau Preferred.

    SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );

  5. Nonaktifkan pengambilan rencana manual dan berlakukan penggunaan rencana terkelola.

    SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;

    Sekarang, ketika pernyataan SQL asli berjalan, pengoptimal memilih rencana Approved atau Preferred. Jika rencana berbiaya minimum tidak berstatus Approved atau Preferred, pengoptimal akan memilih rencana Preferred.