PostgreSQL禁用全表扫描

PostgreSQL禁用全表扫描"/>

PostgreSQL禁用全表扫描

作者：瀚高PG实验室 （Highgo PG Lab）- 徐云鹤

PostgreSQL可以通过一些设置来禁用全表扫描（FULL SCAN/Seq Scan）

注意：

设置此功能后不是完全避免全表扫描，而是只要有不通过全表扫描能得出结果的就不走全表扫描。如果什么路都不通，那肯定得全表扫描，不然怎么获取数据。

而且并不是不走全表扫描性能就一定好。

下面展示下这个功能：

查询表结构：

highgo=# \d testTable testColumn    |              Type              | Modifiers 
-------------+--------------------------------+-----------G           | character varying(50)          | A           | character varying(12)          | M           | timestamp(0) without time zone | W           | character varying(5)           | 
Indexes:"s__x0" btree ("G", "A", "M", "W")

先检查视图：

highgo=# select * from pg_db_role_setting ;setdatabase | setrole | setconfig 
-------------+---------+-----------
(0 rows)

查询执行计划：

highgo=# explain select "G","Z" from test  where "G"='PG';QUERY PLAN                                  
------------------------------------------------------------------------------Seq Scan on test  (cost=0.00..3.11 rows=1 width=72)Filter: (("G")::text = '7e'::text)
(2 rows)Seq Scan on test  (cost=0.00..3.11 rows=1 width=72)Filter: (("G")::text = '7e'::text)
(2 rows)

对用户进行限制：

highgo=# alter role xyh set enable_seqscan =off;
ALTER ROLEhighgo=# select * from pg_db_role_setting ;setdatabase | setrole |      setconfig       
-------------+---------+----------------------0 |   26171 | {enable_seqscan=off}enable_seqscan =off;
ALTER ROLEhighgo=# select * from pg_db_role_setting ;setdatabase | setrole |      setconfig       
-------------+---------+----------------------0 |   26171 | {enable_seqscan=off}

再次查询执行计划：

highgo=# explain select "G","Z" from test  where "G"='7e';QUERY PLAN                                  
------------------------------------------------------------------------------Index Scan using "s__x0" on test  (cost=0.14..8.15 rows=1 width=72)Index Cond: (("G")::text = '7e'::text)
(2 rows)Index Scan using "s__x0" on test  (cost=0.14..8.15 rows=1 width=72)Index Cond: (("G")::text = '7e'::text)
(2 rows)