Gunakan petunjuk di AnalyticDB for PostgreSQL untuk memodifikasi rencana eksekusi - AnalyticDB

AnalyticDB for PostgreSQL memungkinkan penggunaan petunjuk untuk memodifikasi rencana eksekusi. Contohnya, Anda dapat mengaktifkan atau menonaktifkan pengoptimal ORCA untuk pernyataan kueri atau mengubah urutan dan jenis JOIN.

Prasyarat

Instance AnalyticDB for PostgreSQL versi V6.3.7.0 atau lebih baru telah dibuat. Untuk informasi lebih lanjut tentang cara melihat dan memperbarui versi mesin minor dari sebuah instance, lihat Perbarui versi mesin minor.
Ekstensi pg_hint_plan telah diinstal. Untuk informasi lebih lanjut, lihat Gunakan fitur petunjuk.

Aktifkan atau nonaktifkan pengoptimal ORCA untuk pernyataan kueri

Aktifkan petunjuk untuk memodifikasi rencana eksekusi dan aktifkan fitur pendaftaran petunjuk.
```
SET pg_hint_plan.enable_hint to on;
SET pg_hint_plan.enable_hint_table to on;
```

Buat tabel t1 dan t2.

CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE t2 (id int PRIMARY KEY, val int);

Masukkan data ke tabel t1 dan t2.

INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;

Buat indeks untuk tabel t1 dan t2.

CREATE INDEX t1_val ON t1 (val);
CREATE INDEX t2_val ON t2 (val);

Nonaktifkan pengoptimal ORCA.

SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 1;$$);

Informasi berikut dikembalikan:

-[ RECORD 1 ]---------+-----------------------------------------------------
id                    | 1
norm_query_string     | explain select * from t1 join t2 on t1.val = t2.val;
application_name      |
hints                 | set(optimizer off)
query_hash            | -2169095602568752481
enable                | t
prepare_param_strings | {}

Tampilkan rencana eksekusi.

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 3;

Pengoptimal kueri Postgres digunakan dalam rencana eksekusi. Informasi berikut dikembalikan:

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=8.25..27.61 rows=11 width=16)
   ->  Hash Join  (cost=8.25..27.61 rows=4 width=16)
         Hash Cond: (t2.val = t1.val)
         ->  Seq Scan on t2  (cost=0.00..13.00 rows=334 width=8)
         ->  Hash  (cost=8.22..8.22 rows=1 width=8)
               ->  Broadcast Motion 1:3  (slice1; segments: 1)  (cost=0.16..8.22 rows=3 width=8)
                     ->  Index Scan using t1_pkey on t1  (cost=0.16..8.18 rows=1 width=8)
                           Index Cond: (id = 3)
 Optimizer: Postgres query optimizer
(9 rows)

Aktifkan pengoptimal ORCA.

SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer on) SET(rds_optimizer_options 0) */SELECT * FROM t1 WHERE t1.id = 1;$$);

Informasi berikut dikembalikan:

-[ RECORD 1 ]---------+------------------------------------------------
id                    | 2
norm_query_string     | select * from t1 where t1.id = $1;
application_name      |
hints                 | set(optimizer on) set(rds_optimizer_options 0)
query_hash            | -8281826471521807124
enable                | t
prepare_param_strings | {}

Tampilkan rencana eksekusi.

EXPLAIN SELECT * FROM t1 WHERE t1.id = 2;

Pengoptimal ORCA digunakan dalam rencana eksekusi. Informasi berikut dikembalikan:

                                 QUERY PLAN
----------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.00 rows=1 width=8)
   ->  Index Scan using t1_pkey on t1  (cost=0.00..6.00 rows=1 width=8)
         Index Cond: (id = 1)
 Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(4 rows)

Modifikasi jenis JOIN dan urutan JOIN

Aktifkan petunjuk untuk memodifikasi rencana eksekusi dan aktifkan fitur pendaftaran petunjuk.

SET optimizer to off;
SET pg_hint_plan.enable_hint to on;
SET pg_hint_plan.enable_hint_table to on;

Buat tabel t1 dan t2.

CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE t2 (id int PRIMARY KEY, val int);

Masukkan data ke tabel t1 dan t2.

INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;

Buat indeks untuk tabel t1 dan t2.

CREATE INDEX t1_val ON t1 (val);
CREATE INDEX t2_val ON t2 (val);

Tampilkan rencana eksekusi.

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;

Rencana eksekusi default ditampilkan, dengan jenis JOIN menjadi hash join dan urutan JOIN menjadi (t1 t2). Informasi berikut dikembalikan:

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=25.50..208.00 rows=800 width=16)
   ->  Hash Join  (cost=25.50..208.00 rows=267 width=16)
         Hash Cond: (t1.id = t2.id)
         ->  Seq Scan on t1  (cost=0.00..137.00 rows=3334 width=8)
               Filter: (val < 100)
         ->  Hash  (cost=15.50..15.50 rows=267 width=8)
               ->  Seq Scan on t2  (cost=0.00..15.50 rows=267 width=8)
                     Filter: (val > 1)
 Optimizer: Postgres query optimizer

Gunakan salah satu metode berikut untuk memodifikasi jenis JOIN dan urutan JOIN:

Tambahkan petunjuk di awal pernyataan rencana eksekusi.

/*+ MergeJoin(t1 t2) Leading((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;

Jenis JOIN berubah dari hash join menjadi merge join dan urutan JOIN berubah dari (t1 t2) menjadi (t2 t1). Informasi berikut dikembalikan:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.31..93.54 rows=800 width=16)
   ->  Merge Join  (cost=0.31..93.54 rows=267 width=16)
         Merge Cond: (t2.id = t1.id)
         ->  Index Scan using t2_pkey on t2  (cost=0.15..52.90 rows=267 width=8)
               Filter: (val > 1)
         ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=3334 width=8)
               Filter: (val < 100)
 Optimizer: Postgres query optimizer
(8 rows)

Daftarkan petunjuk dan tampilkan rencana eksekusi.

Daftarkan petunjuk dengan pola SQL.

SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);

Informasi berikut dikembalikan:

-[ RECORD 1 ]---------+--------------------------------------------------------------------------
id                    | 1
norm_query_string     | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;
application_name      |
hints                 | MergeJoin(t1 t2) Leading((t2 t1))
query_hash            | -4733464863014584191
enable                | t
prepare_param_strings | {}

Tampilkan rencana eksekusi.

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;

Petunjuk tersebut berlaku. Jenis JOIN berubah dari hash join menjadi merge join dan urutan JOIN berubah dari (t1 t2) menjadi (t2 t1). Informasi berikut dikembalikan:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.31..93.54 rows=800 width=16)
   ->  Merge Join  (cost=0.31..93.54 rows=267 width=16)
         Merge Cond: (t2.id = t1.id)
         ->  Index Scan using t2_pkey on t2  (cost=0.15..52.90 rows=267 width=8)
               Filter: (val > 1)
         ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=3334 width=8)
               Filter: (val < 100)
 Optimizer: Postgres query optimizer
(8 rows)

Tampilkan rencana eksekusi dari pernyataan kueri yang menggunakan pola SQL yang sama.

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;

Informasi berikut dikembalikan. Petunjuk tersebut berlaku dan memodifikasi rencana eksekusi dari pernyataan kueri yang menggunakan pola SQL yang sama.

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=8.25..38.24 rows=13 width=16)
   ->  Merge Join  (cost=8.25..38.24 rows=5 width=16)
         Merge Cond: (t1.id = t2.id)
         ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=2667 width=8)
               Filter: (val < 80)
         ->  Sort  (cost=8.09..8.09 rows=1 width=8)
               Sort Key: t2.id
               ->  Index Scan using t2_val on t2  (cost=0.15..8.08 rows=1 width=8)
                     Index Cond: (val > 20)
 Optimizer: Postgres query optimizer
(10 rows)

(Opsional) Hapus petunjuk dari pola SQL.

SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);

Daftarkan petunjuk dan tampilkan rencana eksekusi ketika konstanta tetap ada dalam pernyataan kueri.

Gunakan pernyataan PREPARE untuk mendaftarkan petunjuk dengan pola SQL.

SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > $1;$$);

Informasi berikut dikembalikan:

-[ RECORD 1 ]---------+--------------------------------------------------------------------------
id                    | 1
norm_query_string     | PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2;
application_name      |
hints                 | MergeJoin(t1 t2) Leading((t2 t1))
query_hash            | -4733464863014584191
enable                | t
prepare_param_strings | {100,$1}

Tampilkan rencana eksekusi dari pernyataan kueri di mana konstanta parameter berubah.

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 2;

Setelah konstanta parameter berubah, petunjuk masih berlaku. Jenis JOIN berubah dari hash join menjadi merge join dan urutan JOIN berubah dari (t1 t2) menjadi (t2 t1). Informasi berikut dikembalikan:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.31..93.54 rows=800 width=16)
   ->  Merge Join  (cost=0.31..93.54 rows=267 width=16)
         Merge Cond: (t1.id = t2.id)
         ->  Index Scan using t1_pkey on t1  (cost=0.16..279.91 rows=3334 width=8)
               Filter: (val < 100)
         ->  Index Scan using t2_pkey on t2  (cost=0.15..52.90 rows=267 width=8)
               Filter: (val > 2)
 Optimizer: Postgres query optimizer
(8 rows)

Tampilkan rencana eksekusi dari pernyataan kueri di mana konstanta tetap berubah.

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;

Setelah konstanta tetap berubah, petunjuk tidak berlaku. Informasi berikut dikembalikan:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=8.09..175.22 rows=13 width=16)
   ->  Hash Join  (cost=8.09..175.22 rows=5 width=16)
         Hash Cond: (t1.id = t2.id)
         ->  Seq Scan on t1  (cost=0.00..137.00 rows=2667 width=8)
               Filter: (val < 80)
         ->  Hash  (cost=8.08..8.08 rows=1 width=8)
               ->  Index Scan using t2_val on t2  (cost=0.15..8.08 rows=1 width=8)
                     Index Cond: (val > 20)
 Optimizer: Postgres query optimizer
(9 rows)

(Opsional) Hapus petunjuk dari pola SQL.

SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);