PolarDB：Menyesuaikan rencana eksekusi

Ikhtisar

Manajemen Rencana SQL (SPM) adalah mekanisme pencegahan. SPM memungkinkan pengoptimal secara otomatis mengelola rencana eksekusi guna memastikan bahwa database Anda hanya menggunakan rencana eksekusi yang diketahui atau diverifikasi.

SPM menyediakan fitur Outline. Fitur Outline hanya mendukung rencana eksekusi tetap dan tidak mendukung rencana eksekusi yang berkembang. Fitur Outline dari PolarDB dapat digunakan untuk menandai rencana eksekusi dari pernyataan PREPARE saja sebagai rencana eksekusi tetap.

Fitur Outline di PolarDB diimplementasikan menggunakan ekstensi polar_outline. Untuk menggunakan fitur ini, buka Pusat Kuota. Klik Apply di kolom Actions yang sesuai dengan polardb_pg_polar_outline.

Membuat ekstensi polar_outline

Untuk membuat ekstensi polar_outline, jalankan pernyataan berikut:

CREATE EXTENSION polar_outline;

Konfigurasikan parameter

Untuk menggunakan fitur Outline dalam menandai rencana eksekusi sebagai rencana eksekusi tetap, jalankan pernyataan berikut:

SET polar_outline.use_stored_plan TO ON;

Fungsi dan relasi yang didefinisikan oleh fitur Outline disimpan dalam skema polar_outline. Anda dapat menjalankan pernyataan berikut untuk menambahkan skema polar_outline ke jalur pencarian:

SET search_path To "$user",public,polar_outline;

Siapkan data uji

Jalankan pernyataan berikut untuk membuat tabel bernama t dan menyisipkan data ke dalam tabel:

CREATE TABLE t(a INT,b INT);
INSERT INTO t SELECT i, i FROM generate_series(1, 1000)i;

Perbarui data dalam tabel untuk memastikan bahwa pengoptimal menggunakan statistik yang akurat.

ANALYZE t;

Jalankan pernyataan PREPARE. Berikut adalah contoh kode:

PREPARE test AS SELECT * FROM t WHERE a=$1;

Tangkap rencana eksekusi dan tandai mereka sebagai rencana tetap

PolarDB for Oracle menyediakan metode berikut untuk menangkap rencana eksekusi dan menandai rencana eksekusi sebagai rencana eksekusi tetap:

• Panggil fungsi polar_outline_create untuk menangkap rencana eksekusi. Kami merekomendasikan Anda menggunakan metode ini. Sebagai contoh, jalankan pernyataan berikut:

  SELECT polar_outline_create('EXECUTE test(100)');

  Informasi berikut akan dikembalikan:

   polar_outline_create 
  ----------------------
   t
  (1 row)

• Gunakan fitur Outline untuk menangkap rencana eksekusi. Metode ini dapat digunakan untuk menangkap beberapa rencana eksekusi sekaligus. Untuk menggunakan fitur Outline dalam menangkap rencana eksekusi, lakukan langkah-langkah berikut:
  Penting Jika fitur Outline digunakan untuk menangkap rencana eksekusi, cache rencana akan dibuat.
  1. Jalankan pernyataan berikut untuk mengizinkan fitur Outline menangkap rencana eksekusi:

     SET polar_outline.capture_plan TO ON;

  2. Jalankan pernyataan berikut untuk menangkap rencana eksekusi:

     EXECUTE test(100);

     Informasi berikut akan dikembalikan:

       a  |  b
     -----+-----
      100 | 100
     (1 row)

  3. Jika Anda tidak ingin menggunakan fitur Outline untuk menangkap rencana eksekusi, jalankan pernyataan berikut:

     SET polar_outline.capture_plan TO OFF;

Parameter

set polar_outline.log_usage To none;
set polar_outline.log_usage To debug;
set polar_outline.log_usage To debug1;
set polar_outline.log_usage To debug2;
set polar_outline.log_usage To debug3;
set polar_outline.log_usage To debug4;
set polar_outline.log_usage To debug5;
set polar_outline.log_usage To log;
set polar_outline.log_usage To info;
set polar_outline.log_usage To notice;
set polar_outline.log_usage To warning;

Menggunakan fitur Outline untuk menulis ulang kueri

SQL1: 
select t.a, t2.avg_b
from t join (select avg(b) as avg_b, a 
             from t2
             group by a) t2
on t2.a = t.a and t.c < $1 
order by t.a;

SQL2:
select t.a, t2.avg_b
from t join lateral (select avg(b) as avg_b
                     from t2
                     where t2.a = t.a) as t2
on t.c < $1
order by t.a;

Anda dapat melakukan langkah-langkah berikut untuk menggunakan fitur Outline mengganti rencana eksekusi SQL1 dengan rencana eksekusi SQL2:

1. Jalankan pernyataan berikut untuk menyiapkan data:

   CREATE TABLE t(a int, b int, c int);
   insert into t select i % 100000, i, i from generate_series(1, 1000000) i;
   create table t2 as select * from t;
   create index on t(c);
   create index on t2(a);
   ANALYZE t,t2;

2. Jalankan pernyataan berikut untuk menangkap rencana eksekusi dari kueri sumber SQL1 dan kueri yang ditulis ulang SQL2:

   prepare s1 as select t.a, t2.avg_b from t join (select avg(b) as avg_b, a from t2 group by a) t2
   on t2.a = t.a and t.c < $1 order by t.a;
   prepare s2 as select t.a, t2.avg_b from t join lateral (select avg(b) as avg_b from t2 where t2.a = t.a) as t2
   on t.c < $1 order by t.a;
   SELECT polar_outline.polar_outline_create('EXECUTE s1(5)');
    polar_outline_create 
   ----------------------
    t
   (1 row)
   
   SELECT polar_outline.polar_outline_create('EXECUTE s2(5)');
    polar_outline_create 
   ----------------------
    t
   (1 row)

3. Jalankan pernyataan berikut untuk menggunakan polar_outline_switch menukar rencana eksekusi dari dua kueri:
   Catatan Parameter input adalah ID dari dua rencana eksekusi dalam outline. Rencana eksekusi SQL1 diganti dengan rencana eksekusi SQL2.

   SELECT polar_outline.polar_outline_switch(1,2);
    polar_outline_switch 
   ----------------------
    t
   (1 row)

4. Jalankan pernyataan berikut untuk menghapus rencana eksekusi SQL2 dari outline:
   Catatan Setelah SQL1 ditulis ulang, rencana eksekusi SQL2 tidak diperlukan dan harus dibersihkan.

   SELECT polar_outline.polar_outline_delete(2);

5. Kode berikut menunjukkan hasil eksekusi SQL1 sebelum SQL1 ditulis ulang dan hasil eksekusi SQL1 setelah SQL1 ditulis ulang:

   Sebelum SQL1 ditulis ulang, s1 menunjukkan rencana eksekusi asli dari SQL1.
   EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                   QUERY PLAN                 
   -------------------------------------------
    Sort
      Sort Key: t.a
      ->  Hash Join
            Hash Cond: (t.a = t2.a)
            ->  Index Scan using t_c_idx on t
                  Index Cond: (c < $1)
            ->  Hash
                  ->  HashAggregate
                        Group Key: t2.a
                        ->  Seq Scan on t2
   (10 rows)
   
   SELECT polar_outline_switch(1,2);
    polar_outline_switch 
   ----------------------
    t
   (1 row)
   
   Setelah SQL1 ditulis ulang, s1 menunjukkan rencana eksekusi saat ini dari SQL1. Rencana eksekusi saat ini bersumber dari SQL2.
   EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                         QUERY PLAN                       
   -------------------------------------------------------
    Sort
      Sort Key: t.a
      ->  Nested Loop
            ->  Index Scan using t_c_idx on t
                  Index Cond: (c < $1)
            ->  Aggregate
                  ->  Bitmap Heap Scan on t2
                        Recheck Cond: (a = t.a)
                        ->  Bitmap Index Scan on t2_a_idx
                              Index Cond: (a = t.a)
   (10 rows)