Postgres10高性能开发（4）控制解释计划

柔光的暖阳◎ 2023-07-10 12:46 9阅读 0赞

# 控制解释计划 #

## 参数控制 ##

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>默认值</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>enable_bitmapscan</td> 
   <td>on</td> 
   <td>启用或禁用位图索引扫描</td> 
  </tr> 
  <tr> 
   <td>enable_gathermerge</td> 
   <td>on</td> 
   <td>启用或禁用收集合并，并发查询时会出现收集合并</td> 
  </tr> 
  <tr> 
   <td>enable_hashagg</td> 
   <td>on</td> 
   <td>启用或禁用HashAggregate</td> 
  </tr> 
  <tr> 
   <td>enable_hashjoin</td> 
   <td>on</td> 
   <td>启用或禁用Hash Join</td> 
  </tr> 
  <tr> 
   <td>enable_indexscan</td> 
   <td>on</td> 
   <td>启用或禁用索引扫描</td> 
  </tr> 
  <tr> 
   <td>enable_indexonlyscan</td> 
   <td>on</td> 
   <td>启用或禁用Index only scan</td> 
  </tr> 
  <tr> 
   <td>enable_material</td> 
   <td>on</td> 
   <td>启用或禁用物化中间结果，不建议关闭</td> 
  </tr> 
  <tr> 
   <td>enable_mergejoin</td> 
   <td>on</td> 
   <td>启用或禁用Merge Join</td> 
  </tr> 
  <tr> 
   <td>enable_nestloop</td> 
   <td>on</td> 
   <td>启用或禁用Nested Loop</td> 
  </tr> 
  <tr> 
   <td>enable_seqscan</td> 
   <td>on</td> 
   <td>启用或禁用全表扫描</td> 
  </tr> 
  <tr> 
   <td>enable_sort</td> 
   <td>on</td> 
   <td>启用或禁用显示排序</td> 
  </tr> 
  <tr> 
   <td>enable_tidscan</td> 
   <td>on</td> 
   <td>启用或禁用行ID扫描</td> 
  </tr> 
 </tbody> 
</table>

## 统计 ##

*  建立多列统计
 *  控制统计采样范围，默认为100,范围0到 10000。值越大，采样约准确，但ANALYZE耗时越长
    
     *  全局控制：default\_statistics\_target变量
     *  具体字段：ALTER TABLE inv\_t\_invoice ALTER cl\_id SET STATISTICS 200;
 *  默认ANALYZE会不定期自动执行。但在大量数据修改后，可能需要手动ANALYZE生成新的统计值

## 索引 ##

*  建立索引
 *  建立部分索引，除去索引效果不大的值

## Join ##

当查询只涉及到2-3张表联表时，规划器可以生成较优的表连接顺序。规划器分析时间随着表的增多指数级增加。如果表太多，规划器将使用遗传概率搜索，但不一定能找到最佳表连接顺序。

因此需要通过Join指定表连接顺序

只有FULL JOIN完全指定了连接顺序

另外外连接优先级低于内连接

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

上面的SQL指定先连接B和C，然后再和A连接

可通过调整Postgres运行时参数改变规划器行为

*  from\_collapse\_limit：from中的表个数不大于from\_collapse\_limit时，将使用from中定义的顺序。默认为8
 *  join\_collapse\_limit：join（不包含FULL JOIN）中的表个数不大于from\_collapse\_limit时，将使用from中定义的顺序。默认为等于from\_collapse\_limit

如果要强制指定规划器使用SQL的连接顺序，可以将from\_collapse\_limit，join\_collapse\_limit 设置为1，防止规划器重新排序

默认情况下

explain select * from inv_t_invoice i join inv_t_invoice_detail d on i.cl_id = d.cl_invoice_id
    join  inv_t_invoice_cash c on i.cl_id = c.cl_invoice_id;
    
    #Result
    Hash Join  (cost=531.63..8435.58 rows=15579 width=3086)
      Hash Cond: ((i.cl_id)::text = (d.cl_invoice_id)::text)
      ->  Nested Loop  (cost=0.29..7545.20 rows=14881 width=2860)
            ->  Seq Scan on inv_t_invoice i  (cost=0.00..2278.72 rows=8972 width=2385)
            ->  Index Scan using idx_invoice_cash_invoice_id on inv_t_invoice_cash c  (cost=0.29..0.57 rows=2 width=475)
                  Index Cond: ((cl_invoice_id)::text = (i.cl_id)::text)
      ->  Hash  (cost=413.93..413.93 rows=9393 width=226)
            ->  Seq Scan on inv_t_invoice_detail d  (cost=0.00..413.93 rows=9393 width=226)

注意：规划器修改了默认联表顺序，先连接inv\_t\_invoice和inv\_t\_invoice\_cash，然后才是inv\_t\_invoice\_detail

set join_collapse_limit=2;
    
    explain select * from inv_t_invoice i join inv_t_invoice_detail d on i.cl_id = d.cl_invoice_id
    join  inv_t_invoice_cash c on i.cl_id = c.cl_invoice_id;
    
    Nested Loop  (cost=531.63..8483.11 rows=15579 width=3086)
      Join Filter: ((i.cl_id)::text = (c.cl_invoice_id)::text)
      ->  Hash Join  (cost=531.34..3026.53 rows=9393 width=2611)
            Hash Cond: ((i.cl_id)::text = (d.cl_invoice_id)::text)
            ->  Seq Scan on inv_t_invoice i  (cost=0.00..2278.72 rows=8972 width=2385)
            ->  Hash  (cost=413.93..413.93 rows=9393 width=226)
                  ->  Seq Scan on inv_t_invoice_detail d  (cost=0.00..413.93 rows=9393 width=226)
      ->  Index Scan using idx_invoice_cash_invoice_id on inv_t_invoice_cash c  (cost=0.29..0.56 rows=2 width=475)
            Index Cond: ((cl_invoice_id)::text = (d.cl_invoice_id)::text)

通过设置join\_collapse\_limit小于3，成功让规划器按inv\_t\_invoice、inv\_t\_invoice\_detail、inv\_t\_invoice\_cash的默认顺序联表