pg_hint_plan

Toàn tập về pg_hint_plan

1. Pg_hint_plan là gì?

Pg_hint_plan là một công cụ dùng để quản lý , điều khiển các kế hoạch thực thi câu lệnh SQL bằng cách sử dụng định dạng '/*+' và '*/' này đặt trước câu lệnh SQL. Nhờ đó mà ta có thể quyết định câu lệnh SQL được thực thi với kế hoạch tối ưu nhằm cải thiện tốc độ xử lý của SQL.

2. Qúa trình thực hiện một câu lệnh SQL:

3. Cài đặt pg_hint_plan:

Các version pg_hint_plan bạn có thể tải tại đây https://github.com/ossc-db/pg_hint_plan/releases

tar xfz pg_hint_plan-REL12_1_3_7.tar.gz
 cd pg_hint_plan-REL12_1_3_7
make
make install

 
Cấu hình pg_hint_plan vào file config postgresql
$ vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_hint_plan'
Ngoài ra bạn có thêm các thuộc tính sau vào file config postgresql:
pg_hint_plan.enable_hint = on : bật hay tắt pg_hint_plan
pg_hint_plan.enable_hint_table = on : bật hay tắt pg_hint_pl``an cho bảng
pg_hint_plan.debug_print = on : bật hay tắt chế độ debug
`pg_hint_plan.message_level = log` bật hay tắt chế độ lưu log
 
`pg_ctl restart -D $PGDATA`
`test2=# create extension pg_hint_plan;`
`CREATE EXTENSION`

4. Testing và sử dụng pg_hint_plan

create table t1 (c1 int, c2 int, c3 int, dummy char(100));
create index t1_idx1 on t1 (c1, c2, c3);
create index t1_idx2 on t1 (c2, c3);
create index t1_idx3 on t1 (c3);
create index t1_idx4 on t1 (c1);

create table t2 (c1 int, c2 int, c3 int, dummy char(100));
create index t2_idx1 on t2 (c1, c2, c3);
create index t2_idx2 on t2 (c2, c3);
create index t2_idx3 on t2 (c3);

create table t3 (c1 int, c2 int, c3 int, dummy char(100));
create index t3_idx1 on t3 (c1, c2, c3);
create index t3_idx2 on t3 (c2, c3);
create index t3_idx3 on t3 (c3);
 
insert into t1 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,100000) c1;
insert into t2 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,10000) c1;
insert into t3 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,100) c1;

/*+ IndexScan(t1 t1_idx1) */ explain analyze select * from t1 where c1=1 and c2=10 and c3=100

 
/*+ IndexScan(t1 t1_idx4) */ explain analyze select * from t1 where c1=1 and c2=10 and c3=100

 
/*+ Leading(c b a) NestLoop(c b) HashJoin(c b a) */ explain analyze select a.*, b.*, c.* from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c2=b.c2 and a.c3=b.c3 and b.c1=c.c1 and b.c2=c.c2 and b.c3=c.c3

 
/*+ Leading(a b c) HashJoin(a b) NestLoop(a b c) */ explain analyze select a.*, b.*, c.* from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c2=b.c2 and a.c3=b.c3 and b.c1=c.c1 and b.c2=c.c2 and b.c3=c.c3

 
INSERT INTO hint_plan.hints(norm_query_string, application_name, hints) VALUES ( 'explain analyze select * from t1 t where c1=?;', '', 'SeqScan(t)' );
select * from hint_plan.hints;
explain analyze select * from t1 t where c1=10;

Hoặc có thể đặt pg_hint_plan vào trong câu lệnh SQL

5. Tìm hiểu thêm về các phương pháp truy xuất dự liệu

Phương pháp scan Giải thích
SeqScan(table) Truy xuất theo thứ tự trên bảng
TidScan(table) Truy xuất TID(Tuple ID0 trên bảng
IndexScan(table[ index...]) Truy xuất dựa theo index
IndexOnlyScan(table[ index...]) Chỉ truy xuất dựa theo index mà không truy cập table
BitmapScan(table[ index...]) Chỉ truy xuất dựa theo Bitmap
IndexScanRegexp(table[ POSIX Regexp...]) Truy xuất chỉ mục dựa theo các mẫu Regrex
IndexOnlyScanRegexp(table[ POSIX Regexp...]) Truy xuất chỉ mục dựa theo các mẫu Regrex mà không truy cập bảng
BitmapScanRegexp(table[ POSIX Regexp...]) Truy xuất Bitmap dựa theo các mẫu Regrex chỉ định
NoSeqScan(table) Truy xuất không theo thứ tự
NoTidScan(table) Truy xuất không theo TID
NoIndexScan(table) Truy xuất không theo chỉ mục
NoIndexOnlyScan(table) Truy xuất không theo chỉ mục mà không cần truy cập
NoBitmapScan(table) Truy xuất không theo Bitmap

 

Phương pháp join Giải thích
NestLoop(table table[ table...]) Vòng lặp lồng nhau bắt buộc cho các liên kết bao gồm các bảng được chỉ định.
HashJoin(table table[ table...]) Tạo kết nối dựa theo bảng băm của các bảng được chỉ định.
MergeJoin(table table[ table...]) Hợp nhất các liên kết của các bảng chỉ định
NoNestLoop(table table[ table...]) Ngược lại với Nestloop
NoHashJoin(table table[ table...]) Ngược lại với Hashjoin
NoMergeJoin(table table[ table...]) Ngược lại với MergeJoin

 

Phương pháp khác Giải thích
Leading(table table[ table...]) Tạo liên kết theo tự các bảng chỉ định
Leading() Tạo liên kết theo thứ tự và hướng mặc định
Rows(table table[ table...] correction) Sửa số hàng của liên kết bao gồm các bảng đã xác định
Parallel(table [soft|hard]) Sử dụng hay ngăn chặn việc xử lý nhiều luồng song song
Set(GUC-param value) Thiết lập các giá trị GUC trong lúc thực hiện kế hoạch (planner)



Cảm ơn bạn đã đọc tôi!!!

Đăng kí nhận RSS - pg_hint_plan