Meningkatkan rencana kueri Aurora Postgre SQL

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 untuk mendapatkan pengoptimal untuk menghasilkan rencana yang diinginkan termasuk menggunakan pg_hint_plan ekstensi atau pengaturan variabel Grand Unified Configuration (GUC) di Postgre: SQL

• pg_hint_planekstensi — Tentukan “petunjuk” untuk memodifikasi cara kerja perencana dengan menggunakan ekstensi PostgreSQL. pg_hint_plan Untuk menginstal dan mempelajari selengkapnya tentang cara menggunakan ekstensi pg_hint_plan, lihat dokumentasi pg_hint_plan.

• GUCvariabel — Ganti satu atau lebih parameter model biaya atau parameter pengoptimal lainnya, seperti atau. from_collapse_limit 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 pg_hint_plan ekstensi untuk mengubah urutan gabungan, metode bergabung, atau jalur akses untuk SQL pernyataan. Anda menggunakan SQL komentar dengan pg_hint_plan sintaks khusus untuk memodifikasi cara pengoptimal membuat rencana. Misalnya, asumsikan SQL pernyataan masalah memiliki gabungan 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 SQL pernyataan tetapi tanpa menjalankan pernyataan. (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 SQL pernyataan minat.

   /*+ 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 SQL pernyataan asli berjalan, pengoptimal memilih salah satu Approved atau Preferred rencana. Jika rencana berbiaya minimum tidak berstatus Approved atau Preferred, pengoptimal akan memilih rencana Preferred.