postgresql优化案例三:recheck cond

1.SQL语句

delete from sap_dispatchingd_hist a
       where exists (select 1 
                       from sap_dispatchingm_hist b 
                      where a.ffact_no=b.ffact_no
                        and a.fsfc_no=b.fsfc_no
                        and b.fsfc_date <to_char(current_date -365,'yyyymmdd') );

2.查看改善前执行计划:

在上图执行计划中看到虽然pg选择使用bitmap的方式,同时使用了recheck cond,我们来看看recheck cond的定义：
It is a potential re-check of the condition that is not always performed.
Only if the bitmap is lossy (which EXPLAIN (ANALYZE) will indicate) the recheck is performed.
A bitmap index scan becomes lossy if work_mem is not big enough to contain a bitmap that contains one bit per table row. It will then degrade to one bit per 8K page. Rows from such blocks will have to be rechecked.
简单来说就是work_mem不足以放下bitmap時，會變為lossy模式，即從原來的每一行建立bit位变為每一頁建立bit位，這里的頁指包含匹配行的页

3.解决方案

3.1增加work_mem的size

默认是4MB,可以使用如下语句设定到期望的值，这里是设到64M

SET work_mem = 65536;

3.2.创建合适的索引

增加work_mem的size能够帮助解决问题，但是还是建议不要轻易使用，而应该首先优化sql或者索引，由于执行计划中使用的索引是idx_sap_dispatchingm_hist_01,我们看看这个索引的结构

relnamespace |               relname                |   attname   | attnum
--------------+--------------------------------------+-------------+--------
 wmspci_app   | idx_sap_dispatchingm_hist_01         | ffact_no    |      1
 wmspci_app   | idx_sap_dispatchingm_hist_01         | fsfc_no     |      2
 wmspci_app   | idx_sap_dispatchingm_hist_01         | fsfc_date   |      9

可以看到idx_sap_dispatchingm_hist_01索引是一个组合索引，由三个子段组成（ffact_no,fsfc_no,fsfc_date),但是执行计划建立bitmap时实际只是用了fsfc_date,因此，我们将只在这个字段建立索引，这样可以减少bitmap的大小

create index idx_sap_dispatchingm_hist_02 on sap_dispatchingm_hist(fsfc_date)

再来看看改善后执行计划

4.改善后执行计划

从上图可以看出，创建合适的索引后，pg不再使用bitmap方式