AliCloudDB for PostgreSQL pg_hint_plan插件的用法

使用rds_superuser登陆需要安装pg_hint_plan插件的数据库。

postgres=> \du+
                                       List of roles
  Role name   |                   Attributes                   | Member of |  Description  
--------------+------------------------------------------------+-----------+---------------
 digoal       |                                                | {}        | rds_superuser
postgres=> \c test

执行以下SQL：

test=> create extension pg_hint_plan;
CREATE EXTENSION

修改所有用户的session_preload_libraries

test=> alter role all set session_preload_libraries='pg_hint_plan';  
ALTER ROLE

试用pg_hint_plan

test=> create table test(id int primary key, info text);
CREATE TABLE
test=> insert into test select generate_series(1,100000);
INSERT 0 100000
test=> explain select * from test where id=1;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.29..8.31 rows=1 width=36)
   Index Cond: (id = 1)
(2 rows)

test=> /*+ seqscan(test) */ explain select * from test where id=1;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test  (cost=0.00..1124.11 rows=272 width=36)
   Filter: (id = 1)
(2 rows)

test=> /*+ bitmapscan(test) */ explain select * from test where id=1;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=4.30..8.31 rows=1 width=36)
   Recheck Cond: (id = 1)
   ->  Bitmap Index Scan on test_pkey  (cost=0.00..4.30 rows=1 width=0)
         Index Cond: (id = 1)
(4 rows)