postgresql 标量子查询改写的各种姿势
同事提供一条SQL，原执行时间需要 3.6S ，反馈比较慢需要优化一下，废话不说贴SQL：
原SQL：
select ((select count(1) FROM AAAAAAAAA wf join BBBBBBBBBB procinst on procinst.id_ = wf.proc_inst_id join ccccccccc xpc on xpc.id = wf.master_entity_key join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('active') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_)) + (select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('active') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_))) as ZB, ((select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join ccccccccc xpc on xpc.id = wf.master_entity_key join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('ended') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_)) + (select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('ended') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_))) as BJ, ((select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join ccccccccc xpc on xpc.id = wf.master_entity_key join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('active') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_)) + (select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('active') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_))) as YQZB, ((select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join ccccccccc xpc on xpc.id = wf.master_entity_key join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('ended') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_)) + (select count(1) FROM AAAAAAAAA wf join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('ended') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_))) as YQBJ; ZB | BJ | YQZB | YQBJ ----+-----+------+------ 0 | 154 | 12 | 128 (1 row)
执行计划：
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=705941.01..705941.03 rows=1 width=32) (actual time=3607.065..3607.101 rows=1 loops=1) InitPlan 1 (returns $1) -> Aggregate (cost=89551.78..89551.79 rows=1 width=8) (actual time=459.571..459.578 rows=1 loops=1) -> Nested Loop (cost=87092.97..89551.77 rows=2 width=0) (actual time=459.568..459.573 rows=0 loops=1) Join Filter: (((wf.proc_inst_id)::text = (procinst.id_)::text) AND ((wf.bus_num1)::double precision <= date_part('day'::text, ((max(DDDDDDDDD.duedate)) - procinst.start_)))) Rows Removed by Join Filter: 7 -> Hash Join (cost=87092.69..89543.65 rows=18 width=65) (actual time=458.089..458.846 rows=99 loops=1) Hash Cond: ((wf.proc_inst_id)::text = (DDDDDDDDD.procinst_)::text) -> Hash Join (cost=1955.82..4406.74 rows=20 width=31) (actual time=33.853..34.568 rows=99 loops=1) Hash Cond: (wf.master_entity_key = xpc.id) -> Bitmap Heap Scan on AAAAAAAAA wf (cost=1898.14..4347.12 rows=740 width=36) (actual time=33.408..34.156 rows=740 loops=1) Recheck Cond: ((master_entity)::text = 'ccccccccc'::text) Heap Blocks: exact=116 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..1897.96 rows=740 width=0) (actual time=8.348..8.348 rows=103217 loops=1) Index Cond: ((master_entity)::text = 'ccccccccc'::text) -> Hash (cost=56.38..56.38 rows=104 width=5) (actual time=0.164..0.165 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Bitmap Heap Scan on ccccccccc xpc (cost=5.08..56.38 rows=104 width=5) (actual time=0.040..0.134 rows=100 loops=1) Recheck Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) Heap Blocks: exact=32 -> Bitmap Index Scan on ccccccccc_project_sort_idx (cost=0.00..5.06 rows=104 width=0) (actual time=0.025..0.025 rows=100 loops=1) Index Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) -> Hash (cost=85076.48..85076.48 rows=4831 width=34) (actual time=424.217..424.217 rows=5333 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 419kB -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=421.887..422.887 rows=5333 loops=1) Group Key: DDDDDDDDD.procinst_ -> Seq Scan on DDDDDDDDD (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.012..383.892 rows=52257 loops=1) -> Index Scan using idx_getsummy_p on EEEEEEEEEEE procinst (cost=0.28..0.43 rows=1 width=34) (actual time=0.007..0.007 rows=0 loops=99) Index Cond: (((id_)::text = (DDDDDDDDD.procinst_)::text) AND ((state_)::text = 'active'::text)) InitPlan 2 (returns $2) -> Aggregate (cost=86930.57..86930.58 rows=1 width=8) (actual time=442.198..442.202 rows=1 loops=1) -> Hash Join (cost=86814.21..86930.51 rows=26 width=0) (actual time=442.196..442.199 rows=0 loops=1) Hash Cond: ((DDDDDDDDD_1.procinst_)::text = (procinst_1.id_)::text) Join Filter: ((wf_1.bus_num1)::double precision <= date_part('day'::text, ((max(DDDDDDDDD_1.duedate)) - procinst_1.start_))) Rows Removed by Join Filter: 33 -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=423.106..424.110 rows=5333 loops=1) Group Key: DDDDDDDDD_1.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_1 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.012..384.952 rows=52257 loops=1) -> Hash (cost=1833.28..1833.28 rows=86 width=65) (actual time=17.369..17.371 rows=33 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Hash Join (cost=869.12..1833.28 rows=86 width=65) (actual time=17.066..17.357 rows=33 loops=1) Hash Cond: ((wf_1.proc_inst_id)::text = (procinst_1.id_)::text) -> Bitmap Heap Scan on AAAAAAAAA wf_1 (cost=686.48..1648.78 rows=267 width=31) (actual time=3.750..15.724 rows=267 loops=1) Recheck Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) Heap Blocks: exact=91 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..686.41 rows=267 width=0) (actual time=3.194..3.194 rows=36061 loops=1) Index Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) -> Hash (cost=161.13..161.13 rows=1721 width=34) (actual time=1.542..1.543 rows=1722 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 131kB -> Bitmap Heap Scan on EEEEEEEEEEE procinst_1 (cost=37.62..161.13 rows=1721 width=34) (actual time=0.172..1.108 rows=1722 loops=1) Recheck Cond: ((state_)::text = 'active'::text) Heap Blocks: exact=97 -> Bitmap Index Scan on EEEEEEEEEEE_state__idx (cost=0.00..37.19 rows=1721 width=0) (actual time=0.157..0.157 rows=1844 loops=1) Index Cond: ((state_)::text = 'active'::text) InitPlan 3 (returns $4) -> Aggregate (cost=89551.78..89551.79 rows=1 width=8) (actual time=460.928..460.932 rows=1 loops=1) -> Nested Loop (cost=87092.97..89551.77 rows=4 width=0) (actual time=459.326..460.925 rows=19 loops=1) Join Filter: (((wf_2.proc_inst_id)::text = (procinst_2.id_)::text) AND ((wf_2.bus_num1)::double precision <= date_part('day'::text, ((max(DDDDDDDDD_2.duedate)) - procinst_2.start_)))) Rows Removed by Join Filter: 73 -> Hash Join (cost=87092.69..89543.65 rows=18 width=65) (actual time=459.162..459.959 rows=99 loops=1) Hash Cond: ((wf_2.proc_inst_id)::text = (DDDDDDDDD_2.procinst_)::text) -> Hash Join (cost=1955.82..4406.74 rows=20 width=31) (actual time=33.042..33.798 rows=99 loops=1) Hash Cond: (wf_2.master_entity_key = xpc_1.id) -> Bitmap Heap Scan on AAAAAAAAA wf_2 (cost=1898.14..4347.12 rows=740 width=36) (actual time=32.584..33.382 rows=740 loops=1) Recheck Cond: ((master_entity)::text = 'ccccccccc'::text) Heap Blocks: exact=116 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..1897.96 rows=740 width=0) (actual time=8.245..8.245 rows=103217 loops=1) Index Cond: ((master_entity)::text = 'ccccccccc'::text) -> Hash (cost=56.38..56.38 rows=104 width=5) (actual time=0.167..0.168 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Bitmap Heap Scan on ccccccccc xpc_1 (cost=5.08..56.38 rows=104 width=5) (actual time=0.051..0.140 rows=100 loops=1) Recheck Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) Heap Blocks: exact=32 -> Bitmap Index Scan on ccccccccc_project_sort_idx (cost=0.00..5.06 rows=104 width=0) (actual time=0.036..0.036 rows=100 loops=1) Index Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) -> Hash (cost=85076.48..85076.48 rows=4831 width=34) (actual time=426.103..426.104 rows=5333 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 419kB -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=423.767..424.766 rows=5333 loops=1) Group Key: DDDDDDDDD_2.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_2 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.013..385.665 rows=52257 loops=1) -> Index Scan using idx_getsummy_p on EEEEEEEEEEE procinst_2 (cost=0.28..0.43 rows=1 width=34) (actual time=0.008..0.009 rows=1 loops=99) Index Cond: (((id_)::text = (DDDDDDDDD_2.procinst_)::text) AND ((state_)::text = 'ended'::text)) InitPlan 4 (returns $5) -> Aggregate (cost=86936.34..86936.35 rows=1 width=8) (actual time=440.862..440.865 rows=1 loops=1) -> Hash Join (cost=86818.27..86936.20 rows=53 width=0) (actual time=438.978..440.848 rows=135 loops=1) Hash Cond: ((DDDDDDDDD_3.procinst_)::text = (procinst_3.id_)::text) Join Filter: ((wf_3.bus_num1)::double precision <= date_part('day'::text, ((max(DDDDDDDDD_3.duedate)) - procinst_3.start_))) Rows Removed by Join Filter: 88 -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=420.198..421.209 rows=5333 loops=1) Group Key: DDDDDDDDD_3.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_3 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.009..382.419 rows=52257 loops=1) -> Hash (cost=1836.20..1836.20 rows=177 width=65) (actual time=18.737..18.739 rows=223 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 31kB -> Hash Join (cost=1652.11..1836.20 rows=177 width=65) (actual time=16.697..18.674 rows=223 loops=1) Hash Cond: ((procinst_3.id_)::text = (wf_3.proc_inst_id)::text) -> Seq Scan on EEEEEEEEEEE procinst_3 (cost=0.00..168.98 rows=3558 width=34) (actual time=0.015..1.949 rows=3545 loops=1) Filter: ((state_)::text = 'ended'::text) Rows Removed by Filter: 1816 -> Hash (cost=1648.78..1648.78 rows=267 width=31) (actual time=16.211..16.212 rows=267 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 25kB -> Bitmap Heap Scan on AAAAAAAAA wf_3 (cost=686.48..1648.78 rows=267 width=31) (actual time=3.778..16.142 rows=267 loops=1) Recheck Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) Heap Blocks: exact=91 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..686.41 rows=267 width=0) (actual time=3.214..3.214 rows=36061 loops=1) Index Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) InitPlan 5 (returns $7) -> Aggregate (cost=89551.78..89551.79 rows=1 width=8) (actual time=453.867..453.872 rows=1 loops=1) -> Nested Loop (cost=87092.97..89551.77 rows=2 width=0) (actual time=453.766..453.866 rows=1 loops=1) Join Filter: (((wf_4.proc_inst_id)::text = (procinst_4.id_)::text) AND ((wf_4.bus_num1)::double precision > date_part('day'::text, ((max(DDDDDDDDD_4.duedate)) - procinst_4.start_)))) Rows Removed by Join Filter: 6 -> Hash Join (cost=87092.69..89543.65 rows=18 width=65) (actual time=452.410..453.150 rows=99 loops=1) Hash Cond: ((wf_4.proc_inst_id)::text = (DDDDDDDDD_4.procinst_)::text) -> Hash Join (cost=1955.82..4406.74 rows=20 width=31) (actual time=32.643..33.343 rows=99 loops=1) Hash Cond: (wf_4.master_entity_key = xpc_2.id) -> Bitmap Heap Scan on AAAAAAAAA wf_4 (cost=1898.14..4347.12 rows=740 width=36) (actual time=32.192..32.932 rows=740 loops=1) Recheck Cond: ((master_entity)::text = 'ccccccccc'::text) Heap Blocks: exact=116 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..1897.96 rows=740 width=0) (actual time=8.285..8.285 rows=103217 loops=1) Index Cond: ((master_entity)::text = 'ccccccccc'::text) -> Hash (cost=56.38..56.38 rows=104 width=5) (actual time=0.165..0.166 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Bitmap Heap Scan on ccccccccc xpc_2 (cost=5.08..56.38 rows=104 width=5) (actual time=0.051..0.137 rows=100 loops=1) Recheck Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) Heap Blocks: exact=32 -> Bitmap Index Scan on ccccccccc_project_sort_idx (cost=0.00..5.06 rows=104 width=0) (actual time=0.035..0.036 rows=100 loops=1) Index Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) -> Hash (cost=85076.48..85076.48 rows=4831 width=34) (actual time=419.751..419.752 rows=5333 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 419kB -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=417.422..418.416 rows=5333 loops=1) Group Key: DDDDDDDDD_4.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_4 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.011..379.472 rows=52257 loops=1) -> Index Scan using idx_getsummy_p on EEEEEEEEEEE procinst_4 (cost=0.28..0.43 rows=1 width=34) (actual time=0.007..0.007 rows=0 loops=99) Index Cond: (((id_)::text = (DDDDDDDDD_4.procinst_)::text) AND ((state_)::text = 'active'::text)) InitPlan 6 (returns $8) -> Aggregate (cost=86930.57..86930.58 rows=1 width=8) (actual time=443.903..443.908 rows=1 loops=1) -> Hash Join (cost=86814.21..86930.51 rows=26 width=0) (actual time=442.221..443.900 rows=11 loops=1) Hash Cond: ((DDDDDDDDD_5.procinst_)::text = (procinst_5.id_)::text) Join Filter: ((wf_5.bus_num1)::double precision > date_part('day'::text, ((max(DDDDDDDDD_5.duedate)) - procinst_5.start_))) Rows Removed by Join Filter: 22 -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=424.690..425.691 rows=5333 loops=1) Group Key: DDDDDDDDD_5.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_5 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.011..386.627 rows=52257 loops=1) -> Hash (cost=1833.28..1833.28 rows=86 width=65) (actual time=17.483..17.485 rows=33 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Hash Join (cost=869.12..1833.28 rows=86 width=65) (actual time=17.183..17.472 rows=33 loops=1) Hash Cond: ((wf_5.proc_inst_id)::text = (procinst_5.id_)::text) -> Bitmap Heap Scan on AAAAAAAAA wf_5 (cost=686.48..1648.78 rows=267 width=31) (actual time=3.671..15.869 rows=267 loops=1) Recheck Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) Heap Blocks: exact=91 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..686.41 rows=267 width=0) (actual time=3.107..3.107 rows=36061 loops=1) Index Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) -> Hash (cost=161.13..161.13 rows=1721 width=34) (actual time=1.513..1.514 rows=1722 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 131kB -> Bitmap Heap Scan on EEEEEEEEEEE procinst_5 (cost=37.62..161.13 rows=1721 width=34) (actual time=0.150..1.090 rows=1722 loops=1) Recheck Cond: ((state_)::text = 'active'::text) Heap Blocks: exact=97 -> Bitmap Index Scan on EEEEEEEEEEE_state__idx (cost=0.00..37.19 rows=1721 width=0) (actual time=0.136..0.136 rows=1844 loops=1) Index Cond: ((state_)::text = 'active'::text) InitPlan 7 (returns $10) -> Aggregate (cost=89551.78..89551.79 rows=1 width=8) (actual time=462.657..462.661 rows=1 loops=1) -> Nested Loop (cost=87092.97..89551.77 rows=4 width=0) (actual time=461.038..462.652 rows=40 loops=1) Join Filter: (((wf_6.proc_inst_id)::text = (procinst_6.id_)::text) AND ((wf_6.bus_num1)::double precision > date_part('day'::text, ((max(DDDDDDDDD_6.duedate)) - procinst_6.start_)))) Rows Removed by Join Filter: 52 -> Hash Join (cost=87092.69..89543.65 rows=18 width=65) (actual time=460.961..461.725 rows=99 loops=1) Hash Cond: ((wf_6.proc_inst_id)::text = (DDDDDDDDD_6.procinst_)::text) -> Hash Join (cost=1955.82..4406.74 rows=20 width=31) (actual time=33.547..34.270 rows=99 loops=1) Hash Cond: (wf_6.master_entity_key = xpc_3.id) -> Bitmap Heap Scan on AAAAAAAAA wf_6 (cost=1898.14..4347.12 rows=740 width=36) (actual time=33.096..33.861 rows=740 loops=1) Recheck Cond: ((master_entity)::text = 'ccccccccc'::text) Heap Blocks: exact=116 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..1897.96 rows=740 width=0) (actual time=8.462..8.462 rows=103217 loops=1) Index Cond: ((master_entity)::text = 'ccccccccc'::text) -> Hash (cost=56.38..56.38 rows=104 width=5) (actual time=0.162..0.163 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Bitmap Heap Scan on ccccccccc xpc_3 (cost=5.08..56.38 rows=104 width=5) (actual time=0.048..0.134 rows=100 loops=1) Recheck Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) Heap Blocks: exact=32 -> Bitmap Index Scan on ccccccccc_project_sort_idx (cost=0.00..5.06 rows=104 width=0) (actual time=0.034..0.035 rows=100 loops=1) Index Cond: ((project_sort)::text = 'XM_PROJECT_SPECIAL'::text) -> Hash (cost=85076.48..85076.48 rows=4831 width=34) (actual time=427.397..427.398 rows=5333 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 419kB -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=425.069..426.072 rows=5333 loops=1) Group Key: DDDDDDDDD_6.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_6 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.012..387.173 rows=52257 loops=1) -> Index Scan using idx_getsummy_p on EEEEEEEEEEE procinst_6 (cost=0.28..0.43 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=99) Index Cond: (((id_)::text = (DDDDDDDDD_6.procinst_)::text) AND ((state_)::text = 'ended'::text)) InitPlan 8 (returns $11) -> Aggregate (cost=86936.34..86936.35 rows=1 width=8) (actual time=443.046..443.048 rows=1 loops=1) -> Hash Join (cost=86818.27..86936.20 rows=53 width=0) (actual time=441.214..443.036 rows=88 loops=1) Hash Cond: ((DDDDDDDDD_7.procinst_)::text = (procinst_7.id_)::text) Join Filter: ((wf_7.bus_num1)::double precision > date_part('day'::text, ((max(DDDDDDDDD_7.duedate)) - procinst_7.start_))) Rows Removed by Join Filter: 135 -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=422.761..423.762 rows=5333 loops=1) Group Key: DDDDDDDDD_7.procinst_ -> Seq Scan on DDDDDDDDD DDDDDDDDD_7 (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.015..384.640 rows=52257 loops=1) -> Hash (cost=1836.20..1836.20 rows=177 width=65) (actual time=18.421..18.423 rows=223 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 31kB -> Hash Join (cost=1652.11..1836.20 rows=177 width=65) (actual time=16.255..18.355 rows=223 loops=1) Hash Cond: ((procinst_7.id_)::text = (wf_7.proc_inst_id)::text) -> Seq Scan on EEEEEEEEEEE procinst_7 (cost=0.00..168.98 rows=3558 width=34) (actual time=0.012..2.104 rows=3545 loops=1) Filter: ((state_)::text = 'ended'::text) Rows Removed by Filter: 1816 -> Hash (cost=1648.78..1648.78 rows=267 width=31) (actual time=15.740..15.741 rows=267 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 25kB -> Bitmap Heap Scan on AAAAAAAAA wf_7 (cost=686.48..1648.78 rows=267 width=31) (actual time=3.740..15.669 rows=267 loops=1) Recheck Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) Heap Blocks: exact=91 -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..686.41 rows=267 width=0) (actual time=3.180..3.180 rows=36061 loops=1) Index Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) Planning Time: 5.092 ms Execution Time: 3607.579 ms (211 rows)
这条 SQL 属于标量子查询，最终结果返回一行，输出结果 4个字段，
每个字段包含两个表达式聚合结果相加，过滤条件略有不同，共8个SQL表达式，
从执行计划可以看到 InitPlan 1-8。
等价改写方式1：
select ( count((case when procinst.state_ in ('active') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) + count((case when procinst.state_ in ('active') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) ) as ZB, ( count((case when procinst.state_ in ('ended') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) + count((case when procinst.state_ in ('ended') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 <= date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) ) as BJ, ( count((case when procinst.state_ in ('active') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) + count((case when procinst.state_ in ('active') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) ) as YQBJ, ( count((case when procinst.state_ in ('ended') and wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) + count((case when procinst.state_ in ('ended') and wf.master_entity = 'REGULATORY_PLAN_STAGE' and wf.bus_num1 > date_part('day', t_expire.expire_date - procinst.start_) then '1' else null end)) ) as YQZB FROM AAAAAAAAA wf left join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id left join ccccccccc xpc on xpc.id = wf.master_entity_key left join (select procinst_, max(duedate) as expire_date from DDDDDDDDD group by procinst_) t_expire on t_expire.procinst_ = wf.proc_inst_id where procinst.state_ in ('active', 'ended') and (wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL') or wf.master_entity = 'REGULATORY_PLAN_STAGE'; ZB | BJ | YQZB | YQBJ ----+-----+------+------ 0 | 154 | 12 | 128
执行计划1：
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=91247.69..91247.71 rows=1 width=32) (actual time=461.756..461.763 rows=1 loops=1) -> Hash Left Join (cost=88010.99..91237.79 rows=66 width=63) (actual time=430.033..461.264 rows=366 loops=1) Hash Cond: ((wf.proc_inst_id)::text = (t_expire.procinst_)::text) -> Hash Left Join (cost=2874.12..6100.76 rows=66 width=81) (actual time=16.241..47.265 rows=366 loops=1) Hash Cond: (wf.master_entity_key = xpc.id) Filter: ((((procinst.state_)::text = ANY ('{active,ended}'::text[])) AND ((wf.master_entity)::text = 'ccccccccc'::text) AND ((xpc.project_sort)::text = 'XM_PROJECT_SPECIAL'::text)) OR ((wf.master_entity)::text = 'REGULATORY_PLAN_STAGE'::text)) Rows Removed by Filter: 641 -> Hash Left Join (cost=2807.41..6031.50 rows=970 width=68) (actual time=15.403..45.825 rows=1007 loops=1) Hash Cond: ((wf.proc_inst_id)::text = (procinst.id_)::text) -> Bitmap Heap Scan on AAAAAAAAA wf (cost=2584.85..5795.60 rows=970 width=54) (actual time=12.446..42.095 rows=1007 loops=1) Recheck Cond: (((master_entity)::text = 'ccccccccc'::text) OR ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text)) Heap Blocks: exact=118 -> BitmapOr (cost=2584.85..2584.85 rows=1007 width=0) (actual time=11.330..11.331 rows=0 loops=1) -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..1897.96 rows=740 width=0) (actual time=8.208..8.208 rows=103217 loops=1) Index Cond: ((master_entity)::text = 'ccccccccc'::text) -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..686.41 rows=267 width=0) (actual time=3.121..3.122 rows=36061 loops=1) Index Cond: ((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) -> Hash (cost=155.58..155.58 rows=5358 width=40) (actual time=2.944..2.945 rows=5361 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 456kB -> Seq Scan on EEEEEEEEEEE procinst (cost=0.00..155.58 rows=5358 width=40) (actual time=0.017..1.452 rows=5361 loops=1) -> Hash (cost=57.43..57.43 rows=743 width=23) (actual time=0.829..0.829 rows=743 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 49kB -> Seq Scan on ccccccccc xpc (cost=0.00..57.43 rows=743 width=23) (actual time=0.006..0.620 rows=743 loops=1) -> Hash (cost=85076.48..85076.48 rows=4831 width=34) (actual time=413.781..413.782 rows=5333 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 403kB -> Subquery Scan on t_expire (cost=84979.86..85076.48 rows=4831 width=34) (actual time=410.613..412.370 rows=5333 loops=1) -> HashAggregate (cost=84979.86..85028.17 rows=4831 width=34) (actual time=410.611..411.793 rows=5333 loops=1) Group Key: DDDDDDDDD.procinst_ -> Seq Scan on DDDDDDDDD (cost=0.00..84718.57 rows=52257 width=34) (actual time=0.008..371.865 rows=52257 loops=1) Planning Time: 0.805 ms Execution Time: 461.907 ms (31 rows)
改写后可以看到只需要 461ms 就能把结果跑出来。
在 PostgreSQL9.4以后，聚合函数新增 FILTER 属性，我们可以使用这种新特性来代替 case when 写法，使得SQL语句看起来更加简洁、优雅。
等价改写方式2：
select count(*) filter ( where procinst.state_ in ('active') and wf.bus_num1 <= days ) ZB, count(*) filter ( where procinst.state_ in ('ended') and wf.bus_num1 <= days ) BJ, count(*) filter ( where procinst.state_ in ('active') and wf.bus_num1 > days ) YQZB, count(*) filter ( where procinst.state_ in ('ended') and wf.bus_num1 > days) YQBJ from (select) v join AAAAAAAAA wf on wf.master_entity in ('ccccccccc', 'REGULATORY_PLAN_STAGE') left join ccccccccc xpc on xpc.id = wf.master_entity_key join EEEEEEEEEEE procinst on procinst.id_ = wf.proc_inst_id and procinst.state_ in ('active', 'ended') join lateral (select max(duedate) as expire_date from DDDDDDDDD where procinst_ = procinst.id_ group by procinst_) t_expire on true join LATERAL (select date_part('day', t_expire.expire_date - procinst.start_) days ) on true where ((wf.master_entity = 'ccccccccc' and xpc.project_sort = 'XM_PROJECT_SPECIAL' ) or (wf.master_entity = 'REGULATORY_PLAN_STAGE')); ZB | BJ | YQBJ | YQZB ----+-----+------+------ 0 | 154 | 12 | 128
执行计划2：
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2048.60..2048.61 rows=1 width=32) (actual time=195.004..195.008 rows=1 loops=1) -> Nested Loop (cost=555.04..2040.02 rows=143 width=27) (actual time=43.858..194.401 rows=355 loops=1) -> Nested Loop (cost=554.62..1299.12 rows=13 width=45) (actual time=43.144..48.111 rows=355 loops=1) -> Hash Left Join (cost=554.34..1253.29 rows=13 width=31) (actual time=13.312..44.417 rows=366 loops=1) Hash Cond: (wf.master_entity_key = xpc.id) Filter: ((((wf.master_entity)::text = 'ccccccccc'::text) AND ((xpc.project_sort)::text = 'XM_PROJECT_SPECIAL'::text)) OR ((wf.master_entity)::text = 'REGULATORY_PLAN_STAGE'::text)) Rows Removed by Filter: 641 -> Bitmap Heap Scan on AAAAAAAAA wf (cost=487.62..1186.09 rows=183 width=54) (actual time=12.510..42.785 rows=1007 loops=1) Recheck Cond: ((((master_entity)::text = 'ccccccccc'::text) AND ((master_entity)::text = ANY ('{ccccccccc,REGULATORY_PLAN_STAGE}'::text[]))) OR (((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) AND ((master_entity)::text = ANY ('{ccccccccc,REGULATORY_PLAN_STAGE}'::text[])))) Heap Blocks: exact=118 -> BitmapOr (cost=487.62..487.62 rows=190 width=0) (actual time=11.410..11.412 rows=0 loops=1) -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..358.21 rows=139 width=0) (actual time=8.181..8.181 rows=103217 loops=1) Index Cond: (((master_entity)::text = 'ccccccccc'::text) AND ((master_entity)::text = ANY ('{ccccccccc,REGULATORY_PLAN_STAGE}'::text[]))) -> Bitmap Index Scan on AAAAAAAAA_master_entity_idx (cost=0.00..129.31 rows=50 width=0) (actual time=3.228..3.228 rows=36061 loops=1) Index Cond: (((master_entity)::text = 'REGULATORY_PLAN_STAGE'::text) AND ((master_entity)::text = ANY ('{ccccccccc,REGULATORY_PLAN_STAGE}'::text[]))) -> Hash (cost=57.43..57.43 rows=743 width=23) (actual time=0.792..0.792 rows=743 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 49kB -> Seq Scan on ccccccccc xpc (cost=0.00..57.43 rows=743 width=23) (actual time=0.010..0.578 rows=743 loops=1) -> Index Scan using idx_getsummy_p on EEEEEEEEEEE procinst (cost=0.28..3.52 rows=1 width=40) (actual time=0.009..0.009 rows=1 loops=366) Index Cond: ((id_)::text = (wf.proc_inst_id)::text) Filter: ((state_)::text = ANY ('{active,ended}'::text[])) Rows Removed by Filter: 0 -> GroupAggregate (cost=0.41..56.77 rows=11 width=34) (actual time=0.411..0.411 rows=1 loops=355) Group Key: DDDDDDDDD.procinst_ -> Index Scan using DDDDDDDDD_procinst__idx on DDDDDDDDD (cost=0.41..56.61 rows=11 width=34) (actual time=0.392..0.405 rows=9 loops=355) Index Cond: ((procinst_)::text = (procinst.id_)::text) Planning Time: 0.927 ms Execution Time: 195.131 ms (28 rows)
使用聚合 filter 改写后可以看到只需要 195ms 就能把结果跑出来，比 case when 写法效率提高 50% ，SQL更加美观，真正实现了代码越少，效率越高的性能。
总结：写SQL尽量少用标量子查询。
