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

控制解释计划

参数控制

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

统计

• 建立多列统计
• 控制统计采样范围，默认为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的默认顺序联表