阿里云 PostgreSQL pg_hint_plan插件的用法

背景

阿里云RDS 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)  

参考

《PostgreSQL SQL HINT的使用(pg_hint_plan)》

digoal’s 大量PostgreSQL文章入口