关键时刻HINT出彩 - PG优化器的参数优化、执行计划固化CASE

背景

有过数据库使用经验的童鞋可曾遇到过SQL执行计划不准确，或者SQL执行计划抖动的问题。

PostgreSQL的执行计划与大多数的企业数据库是一样的，都是基于成本优化。

基于成本优化的优化器，在算法靠谱，统计信息准确的前提下，通常得到的执行计划是比较准确的。

那么什么时候执行计划可能不准确呢？

• 成本估算的算法不好

  这个需要内核的不断改进，完善。

  在没有合理的算法支撑的情况下，内核中往往会带有一些经验值，或者将这些经验值开放给用户设置。

• 统计信息不准确

  PG的统计信息收集调度是几个参数共同决定的，也可以设置为表级别的调度。

  如果发生了大批数据的变化，而且在调度来临之前没有及时的更新统计信息，就可能出现统计信息不准确的时间窗口。

  还好PG的统计信息收集是动态调度的，一般不会出问题。

  另外，如果SQL过于复杂，索引过多，可选用plan路径过多，在算法不好或者统计信息不准确的情况下，成本估算的误差成倍放大，可能造成选择的最优执行计划不准确。

如何让执行计划更准确

既然执行计划和算法、统计信息有关。要让执行计划准确，当然是有方法的。

PostgreSQL 影响执行计划的几种方法

• 因子

  在计算成本的公式中，会用到一些因子，这些因子会影响最终计算到的成本。

# - Planner Cost Constants -    
#seq_page_cost = 1.0                    # measured on an arbitrary scale    
#random_page_cost = 4.0                 # same scale as above    
#cpu_tuple_cost = 0.01                  # same scale as above    
#cpu_index_tuple_cost = 0.005           # same scale as above    
#cpu_operator_cost = 0.0025             # same scale as above    
#effective_cache_size = 4GB    

例如random_page_cost 表示使用索引扫描时，单个数据块的扫描成本。

seq_page_cost 表示全表扫描时，单个数据块的扫描成本。

如果你想让执行计划准确，就需要校准这些因子。

我之前写过成本因子校准的文章，大家可以阅读一下

http://blog.163.com/digoal@126/blog/static/163877040201310255717379

校准因子是非常通俗且恰当的方法。

• 开关

  PostgreSQL提供了一些开关，允许用户关闭或打开，从而建议优化器允许或不允许使用一些扫描方法。（并非强制，当没有其他可选方法时，则依然会使用）

  例如，关闭全表扫描 set enable_seqscan=off ， 则优化器在能走其他扫描方法的时候，就不会走全表扫描，否则还是会走全表扫描的。

# - Planner Method Configuration -    
#enable_bitmapscan = on    
#enable_hashagg = on    
#enable_hashjoin = on    
#enable_indexscan = on    
#enable_indexonlyscan = on    
#enable_material = on    
#enable_mergejoin = on    
#enable_nestloop = on    
#enable_seqscan = on    
#enable_sort = on    
#enable_tidscan = on    

• join与子查询固化或rewrite

  join或子查询的优化，属于优化器优化JOIN的范畴。

  当用户的QUERY涉及到多个JOIN对象，或者涉及到多个子查询时，优化器可以选择是否改变当前的SQL，产生更多的plan选择更优的执行计划。

#from_collapse_limit = 8    
当from列表的对象少于from_collapse_limit时，优化器可以将子查询提升到上层进行JOIN，从而可能选择到更优的执行计划。      
#join_collapse_limit = 8                # 1 disables collapsing of explicit    
                                        # JOIN clauses    
当使用显示的JOIN时（除了full join），例如a join b join c join d，优化器可以重排JOIN的顺序，以产生更多的PLAN选择更优的执行计划。      
如果join_collapse_limit=1，则不重排，使用SQL写法提供的顺序。      

如果用户要固化JOIN顺序，请使用显示的JOIN，同时将join_collapse_limit设置为1。

如果用户不打算提升子查询，同样的，将from_collapse_limit 设置为1即可。

• 约束排除

  通过设置以下参数，在SQL查询时，通过逻辑推理排除不需要查询的表。

  《PostgreSQL 优化器逻辑推理能力 源码解析》

  https://yq.aliyun.com/articles/6821

#constraint_exclusion = partition       # on, off, or partition    

• 游标建议

  通过设置以下参数，在使用游标时，告诉优化器是快速的返回部分行，还是快速返回所有行。

#cursor_tuple_fraction = 0.1            # range 0.0-1.0    

• 遗传算法

  PostgreSQL在遇到很多表的JOIN时，如果表的数量超过了参数geqo_threshold 设置的阈值，则会使用遗传算法，选择一个JOIN顺序。

  为什么要这样呢？

  因为默认情况下，PG是使用穷举法对所有的JOIN排列组合进行成本估算，如果表很多的话，可以想象一下，执行计划要估算很多个PLAN的成本，使得成本计算的开销非常庞大。

PostgreSQL 影响统计信息的几种方法

• 自动垃圾回收进程

  PostgreSQL的自动垃圾回收进程，除了回收垃圾，还负责收集统计信息。

  通过调整阈值，可以控制统计信息的收集调度。

单个垃圾回收进程可以使用的最大内存      
#autovacuum_work_mem      # min 1MB, or -1 to use maintenance_work_mem    
#     
#------------------------------------------------------------------------------    
# AUTOVACUUM PARAMETERS    
#------------------------------------------------------------------------------    
#    
是否打开自动垃圾回收    
#autovacuum = on                        # Enable autovacuum subprocess?  'on'    
最多允许多少个并行的垃圾回收进程    
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses    
                                        # (change requires restart)    
垃圾回收进程检查完所有数据库的周期，例如有4个数据库，周期为1分钟，则每个数据库每25秒被检查一次是否有需要被垃圾回收的对象。      
数据库不多，并且数据被频繁更新的话，建议把这个值调小到合理范围，例如2秒。      
确保快速的回收垃圾。      
#autovacuum_naptime = 1min              # time between autovacuum runs    
自动进行垃圾回收的最少影响行，例如表已经删除了50条记录，则可能触发垃圾回收，（同时还要满足另一个条件#autovacuum_vacuum_scale_factor）    
#autovacuum_vacuum_threshold = 50       # min number of row updates before    
                                        # vacuum    
自动收集统计信息的最少影响行    
#autovacuum_analyze_threshold = 50      # min number of row updates before    
                                        # analyze    
当超过0.2(即20%)的记录发生删除或更新时，同时满足超过#autovacuum_vacuum_threshold ，则触发自动垃圾回收    
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum    
同上，只是自动收集统计信息    
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze    
# 当自动垃圾回收的进程，在垃圾回收的过程中，所有的垃圾回收进程所产生的开销总和，超过#autovacuum_vacuum_cost_limit时，则所有的垃圾回收进程都会进入SLEEP状态    
# #autovacuum_vacuum_cost_delay表示sleep的时间    
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for    
                                        # autovacuum, in milliseconds;    
                                        # -1 means use vacuum_cost_delay    
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for    
                                        # autovacuum, -1 means use    
                                        # vacuum_cost_limit    
计算vacuum的开销，系数。    
垃圾页在shared buffer中命中    
#vacuum_cost_page_hit = 1               # 0-10000 credits    
垃圾页未在shared buffer中命中    
#vacuum_cost_page_miss = 10             # 0-10000 credits    
垃圾页是脏页, 需要先write到磁盘    
#vacuum_cost_page_dirty = 20            # 0-10000 credits    

很多值是可以表级别设置的。

• 统计信息收集的细粒度

  统计信息收集的粒度，表示一共产生多少个bucket，越大，越细，但是analyze需要扫描的块也越多。

  #default_statistics_target = 100        # range 1-10000    
  

  可以表级别设置

通过调整以上参数，可以使得统计信息够准确，成本计算的因子够准确，解决大多数的执行计划问题。

如果因为SQL过于复杂，已经出发了遗传算法，又或者某些确实是成本估算的算法不够好，导致的执行计划不准确，还有什么方法呢？

又或者你想节约数据库在执行计划优化阶段的CPU开销，有办法么？

如何绑定SQL执行计划

阿里云ApsaraDB for RDS PG，提供了一个插件叫pg_hint_plan，通过这个插件，可以设置SQL HINT，绑定执行计划。

用法可参考

http://pghintplan.osdn.jp/pg_hint_plan.html

http://pghintplan.osdn.jp/hint_list.html

https://yq.aliyun.com/articles/17212

特别注意，pg_hint_plan是用到了hook的，所以一定要调用so中的_PG_init(void)初始化一下hook，

见pg_hint_plan源码      
pg_hint_plan.c    
    
         /* Install hooks. */    
         prev_ProcessUtility = ProcessUtility_hook;    
         ProcessUtility_hook = pg_hint_plan_ProcessUtility;    
         prev_planner = planner_hook;    
         planner_hook = pg_hint_plan_planner;    
         prev_get_relation_info = get_relation_info_hook;    
         get_relation_info_hook = pg_hint_plan_get_relation_info;    
         prev_join_search = join_search_hook;    
         join_search_hook = pg_hint_plan_join_search;    
     
         /* setup PL/pgSQL plugin hook */    
         var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin");    
         *var_ptr = &plugin_funcs;    
     
         RegisterResourceReleaseCallback(plpgsql_query_erase_callback, NULL);    

因此用到了 alter role all set session_preload_libraries=’pg_hint_plan’;

或者 alter role 自己 set session_preload_libraries=’pg_hint_plan’;

并且会话需要重新连接，以加载hook。

例子

创建extension

=> create extension pg_hint_plan;    
CREATE EXTENSION    

以下QUERY为例

 Schema |      Name      | Type  | Owner  |  Size   | Description                        
--------+----------------+-------+--------+---------+---------    
 public | test01 | table | digoal | 7273 MB |     
 public | test02 | table | digoal | 560 MB  |     
 public | test03 | table | digoal | 368 MB  |     
    
SELECT xxxx    
    FROM    
  "test01" AS rd    
    INNER JOIN "test02" AS d ON (rd.test02_uuid = d.uuid)    
    INNER JOIN "test03" AS ir ON (d.test03_uuid = ir.uuid)    
    WHERE    
  d.status = 'normal'    
    AND ir.u_uuid = 'ttttttttt' and (d.test02_status in ('test02ed','checked'))     
      and d.is_sub = false and d.is_filter = false ORDER BY d.test02_time desc limit 10 offset 0    

需要固定为以下执行计划

 Limit  (cost=1204.30..1204.33 rows=10 width=276) (actual time=11.131..11.132 rows=10 loops=1)    
   ->  Sort  (cost=1204.30..1204.49 rows=75 width=276) (actual time=11.131..11.131 rows=10 loops=1)    
         Sort Key: d.test02_time    
         Sort Method: quicksort  Memory: 33kB    
         ->  Nested Loop  (cost=1.28..1202.68 rows=75 width=276) (actual time=0.085..11.095 rows=17 loops=1)    
               ->  Nested Loop  (cost=0.85..1117.07 rows=62 width=101) (actual time=0.066..10.879 rows=13 loops=1)    
                     ->  Index Scan using "abc" on test03 ir  (cost=0.42..14.89 rows=10 width=38) (actual time=0.026..0.166 rows=96 loops=1)    
                           Index Cond: ((u_uuid)::text = 'ttttttttt'::text)    
                     ->  Index Scan using "bcd" on test02 d  (cost=0.43..109.86 rows=36 width=80) (actual time=0.109..0.111 rows=0 loops=96)    
                           Index Cond: ((test03_uuid)::text = (ir.uuid)::text)    
                           Filter: ((NOT is_sub) AND (NOT is_filter) AND ((test02_status)::text = ANY ('{test02ed,checked}'::text[])) AND ((status)::text = 'normal'::text))    
                           Rows Removed by Filter: 67    
               ->  Index Scan using "def" on test01 rd  (cost=0.43..1.32 rows=6 width=192) (actual time=0.014..0.014 rows=1 loops=13)    
                     Index Cond: ((test02_uuid)::text = (d.uuid)::text)    

分析以上执行计划，需要固定的包括 索引，嵌套循环，以及JOIN的顺序，驱动顺序。

SQL语句改成

/*+     
  NestLoop(ir d)     
  NestLoop(ir d rd)     
  Leading(((ir d) rd))     
  IndexScan(rd "def")     
  IndexScan(d "bcd")     
  IndexScan(ir "abc")     
*/     
SELECT xxxx    
    FROM    
  "test01" AS rd    
    INNER JOIN "test02" AS d ON (rd.test02_uuid = d.uuid)    
    INNER JOIN "test03" AS ir ON (d.test03_uuid = ir.uuid)    
    WHERE    
  d.status = 'normal'    
    AND ir.u_uuid = 'ttttttttt' and (d.test02_status in ('test02ed','checked'))     
      and d.is_sub = false and d.is_filter = false ORDER BY d.test02_time desc limit 10 offset 0    

pg_hint_plan语法解释

整个query的第一个comment (/*+ */) 被pg_hint_plan用来固定执行计划。

如果有多个comment，也只认第一个，请务必注意。

/*+     
  NestLoop(ir d)  # 表示ir d两个表使用嵌套循环JOIN，如果QUERY用了别名，请使用别名。     
    
  NestLoop(ir d rd)  # 表示ir与d join完后再与rd JOIN。      
  所以如果要固定JOIN顺序，可以分多个JOIN hint来写，就像上面这样。      
    
  Leading(((ir d) rd))    # 表示JOIN顺序和驱动顺序，      
  每一对JOIN对象都需要用括号表示，      
  括号内左边的元素为outer、driver表，右边的元素为inner、driven表。      
  (hash 和 nestloop JOIN 请务必注意括号内的别名或表名顺序)。         
  如果QUERY用了别名，请使用别名。        
    
  IndexScan(rd "def")   #  指定索引，如果表名使用了别名，请使用别名。        
    
  IndexScan(d "bcd")     
    
  IndexScan(ir "abc")     
    
  务必注意，  如果对象名用了 小写和下划线 以外的字符。  必须使用""引用起来。      
*/     

pg_hint_plan插件支持的所有 hint

上面的例子使用了其中的一部分，其他的请用户自行阅读参考。

http://pghintplan.osdn.jp/pg_hint_plan.html

Format	Description
Scan method	-
SeqScan(table)	Forces sequential scan on the table
TidScan(table)	Forces TID scan on the table.
IndexScan(table[ index…])	Forces index scan on the table. Restricts to specified indexes if any.
IndexOnlyScan(table[ index…])	Forces index only scan on the table. Rstricts to specfied indexes if any. Index scan may be used if index only scan is not available. Available for PostgreSQL 9.2 and later.
BitmapScan(table[ index…])	Forces bitmap scan on the table. Restoricts to specfied indexes if any.
NoSeqScan(table)	Forces not to do sequential scan on the table.
NoTidScan(table)	Forces not to do TID scan on the table.
NoIndexScan(table)	Forces not to do index scan and index only scan (For PostgreSQL 9.2 and later) on the table.
NoIndexOnlyScan(table)	Forces not to do index only scan on the table. Available for PostgreSQL 9.2 and later.
NoBitmapScan(table)	Forces not to do bitmap scan on the table.
Join method	-
NestLoop(table table[ table…])	Forces nested loop for the joins consist of the specifiled tables.
HashJoin(table table[ table…])	Forces hash join for the joins consist of the specifiled tables.
MergeJoin(table table[ table…])	Forces merge join for the joins consist of the specifiled tables.
NoNestLoop(table table[ table…])	Forces not to do nested loop for the joins consist of the specifiled tables.
NoHashJoin(table table[ table…])	Forces not to do hash join for the joins consist of the specifiled tables.
NoMergeJoin(table table[ table…])	Forces not to do merge join for the joins consist of the specifiled tables.
Join order	-
Leading(table table[ table…])	Forces join order as specified.
Leading()	Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure.
Row number correction	-
Rows(table table[ table…] correction)	Corrects row number of a result of the joins consist of the specfied tables. The available correction methods are absolute (#), addition (+), subtract (-) and multiplication (*). should be a string that strtod() can read.
GUC	-
Set(GUC-param value)	Set the GUC parameter to the value while planner is running.

举例

postgres=# /*+    
postgres*#     Set(random_page_cost 2.0)    
postgres*#  */    
postgres-# SELECT * FROM table1 t1 WHERE key = 'value';    
    
postgres=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10    
postgres=# /*+ Rows(a b +10) */ SELECT... ; Increments row number by 10    
postgres=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number.    
postgres=# /*+ Rows(a b *10) */ SELECT... ; Makes the number 10 times larger.    

pg_hint_plan debug

当你不知道hint是否起到作用时，可以DEBUG一下。

postgres=# create extension pg_hint_plan ;  
CREATE EXTENSION  
set client_min_messages ='log';  
set pg_hint_plan.debug_print =on;  
set pg_hint_plan.enable_hint=on;  
set pg_hint_plan.message_level =log;  
set pg_hint_plan.parse_messages =log;  
set pg_hint_plan.enable_hint_table =on;  
  
postgres=# create table test(id int primary key, info text);  
CREATE TABLE  
postgres=# insert into test select generate_series(1,1000000), 'test';  
INSERT 0 1000000  
  
postgres=# explain /*+ seqscan(test) */ select count(*) from test;  
LOG:  pg_hint_plan:  
used hint:  
SeqScan(test)  
not used hint:  
duplication hint:  
error hint:  
  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)  
   ->  Seq Scan on test  (cost=0.00..15406.00 rows=1000000 width=0)  
(2 rows)  
  
postgres=# explain /*+ indexonlyscan(test) */ select count(*) from test;  
LOG:  pg_hint_plan:  
used hint:  
IndexOnlyScan(test)  
not used hint:  
duplication hint:  
error hint:  
  
                                        QUERY PLAN                                           
-------------------------------------------------------------------------------------------  
 Aggregate  (cost=33889.43..33889.44 rows=1 width=8)  
   ->  Index Only Scan using test_pkey on test  (cost=0.42..31389.42 rows=1000000 width=0)  
(2 rows)  
  
postgres=# explain /*+ indexscan(test) */ select count(*) from test;  
LOG:  pg_hint_plan:  
used hint:  
IndexScan(test)  
not used hint:  
duplication hint:  
error hint:  
  
                                     QUERY PLAN                                       
------------------------------------------------------------------------------------  
 Aggregate  (cost=10000017906.00..10000017906.01 rows=1 width=8)  
   ->  Seq Scan on test  (cost=10000000000.00..10000015406.00 rows=1000000 width=0)  
(2 rows)  
  
postgres=# explain /*+ bitmapscan(test) */ select count(*) from test;  
LOG:  pg_hint_plan:  
used hint:  
BitmapScan(test)  
not used hint:  
duplication hint:  
error hint:  
  
                                     QUERY PLAN                                       
------------------------------------------------------------------------------------  
 Aggregate  (cost=10000017906.00..10000017906.01 rows=1 width=8)  
   ->  Seq Scan on test  (cost=10000000000.00..10000015406.00 rows=1000000 width=0)  
(2 rows)  
  
postgres=# explain /*+ bitmap scan(test) */ select count(*) from test;  
LOG:  pg_hint_plan: hint syntax error at or near "bitmap scan(test) "  
DETAIL:  Unrecognized hint keyword "bitmap".  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)  
   ->  Seq Scan on test  (cost=0.00..15406.00 rows=1000000 width=0)  
(2 rows)  

绑定SQL执行计划的缺点

绑定SQL执行计划可以解决一些问题，但是也会引入一些问题。

SQL执行计划一旦绑定，则会严格按照绑定的执行计划执行。

例如使用了绑定变量的情况，如果SQL的条件发送变化，绑定的执行计划可能不适合变化后的条件。

小结

1. pg_hint_plan是一种辅助的手段，帮助用户解决因为优化器算法不好，或者统计信息不准确导致的执行计划不准确的问题。

2. 用户应该尽量的通过校准成本因子，设置好统计信息收集的参数等手段，使得优化器能够更加精准的评估成本，选择合理的执行计划。

3. pg_hint_plan需要用到PostgreSQL的hook，所以必须预加载的方式调用_PG_init(void)。

通过修改postgresql.conf的shared_preload_libraries预加载，或者设置会话级自动加载参数，都是可行的。

digoal’s 大量PostgreSQL文章入口