记录一下上海某个内网报表系统的项目的一个案例，里面的逻辑比较复杂，很多视图套视图的语句。
最多的一个视图除了它本身以外，一层层嵌套了7个视图在里面，贼恶心。
而最难受的是内网项目无法远程，sql 、执行计划、表结构、视图结构等相关信息只能让现场同事配合，刻录数据从内网到外网转出来发给我，我改写好的语句给到现场同事她又要刻录光盘上传到内网服务器来验证，这种方式非常不方便，往往验证一个优化思路需要半天时间，工作效率很慢。
废话不多说，先放SQL和执行计划：
with t1 as (select fybm,
count(distinct
case when sarq between date '2022-01-01' and date '2022-12-31' then a.ajid end) msslasqs,
count(distinct case
when larq between date '2022-01-01' and date '2022-12-31'
then a.ajid end) msslas,
count(distinct case
when trunc(k.ktjsrq) between date '2022-01-01' and date '2022-12-31'
then a.ajid end) ktslajs,
count(distinct case
when trunc(k.ktjsrq) between date '2022-01-01' and date '2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1')
then a.ajid end) zxtsajs,
sum(case
when trunc(k.ktjsrq) between date '2022-01-01' and date '2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1') then 1
else 0 end) zxtscs,
sum(case
when trunc(k.ktjsrq) between date '2022-01-01' and date '2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1')
then (cast(k.ktjsrq as date) - cast(k.ktksrq as date)) end) *
24 zxktsc,
count(distinct case
when larq between date '2022-01-01' and date '2022-12-31' and exists(select 1
from spcx.tab_satj s
where s.ajid = a.ajid
and s.satj in
('xsla',
'ysla',
'zzla',
'zzp',
'xsgs',
'jxjs',
'xfla',
'qtwsla',
'dyjf'))
then a.ajid end) zxlas
from spcx.v_tycx_ajjbxx a
left join sp_ktxx k on a.ajid = k.ajid and k.sfts = '1' and k.ktjg not in (4, 6)
where (ajxz_tj = 2
or ajlbbh in (601, 2140)
and exists(select 1 from sp_ajxx_zx z where a.ajid = z.ajid and z.zxajxz in (4, 5, 6)))
and (larq between date '2022-01-01' and date '2022-12-31'
or sarq between date '2022-01-01' and date '2022-12-31'
or trunc(k.ktjsrq) between date '2022-01-01' and date '2022-12-31')
group by fybm),
t2 as (select sqfy fybm,
sum(case when trunc(sqsj) between date '2022-01-01' and date '2022-12-31' then 1 else 0 end) zxsqlas
from spcx.fy_wsla_sqjbxx s
join spcx.fy_wsla_zt_nw z on s.id = z.sqbh
left join spcx.fy_wsla_spzxjg j on s.id = j.sqbh
where (lsid is not null
or userid is not null)
and ajlb in (2, 4, 5)
and trunc(sqsj) between date '2022-01-01' and date '2022-12-31'
and z.zt != 'SQ000'
group by sqfy),
t3 as (select fybm, count(1) sdcs
from spcx.v_tycx_ajjbxx a
join V_MV_EMS_TXM t
on a.ah = t.ah
where (ajxz_tj = 2
or ajlbbh in (601, 2140)
and exists(select 1
from sp_ajxx_zx z
where a.ajid = z.ajid
and z.zxajxz in (4, 5, 6)))
and trunc(cjsj) between date '2022-01-01'
and date '2022-12-31'
group by fybm),
t4 as (select fydm fybm,
count(distinct ah) dzsdajs,
count(1) dzsds,
sum(case when sdzt = 1 then 1 else 0 end) yxdzsds
from MV_SDXXFS_TYSDPT t
where exists(select 1
from spcx.v_tycx_ajjbxx a
where t.ah = a.ah
and (a.ajxz_tj = 2
or ajlbbh in (601, 2140)
and exists(select 1
from sp_ajxx_zx z
where a.ajid = z.ajid
and z.zxajxz in (4, 5, 6))))
and trunc(fssdsj) between date '2022-01-01'
and date '2022-12-31'
group by fydm),
t5 as (select fydm fybm, count(distinct a.ah) dzsdwss
from spcx.v_tycx_ajjbxx a
join MV_SDXXWS_TYSDPT t
on a.ah = t.ah
where exists(select 1
from spcx.v_tycx_ajjbxx a
where t.ah = a.ah
and (a.ajxz_tj = 2
or ajlbbh in (601, 2140)
and exists(select 1
from sp_ajxx_zx z
where a.ajid = z.ajid
and z.zxajxz in (4, 5, 6))))
and exists(select 1
from MV_SDXXFS_TYSDPT t1
where t.ah = t1.ah
and t.fsxxid = t1.fsxxid)
and (sshdtz like '%判决书%'
or sshdtz like '%裁定书%'
or sshdtz like '%调解书%')
and trunc(jarq) between date '2022-01-01'
and date '2022-12-31'
group by fydm)
select '全市' fymc,
sum(nvl(t1.msslasqs, 0)) msslasqs,
sum(nvl(t1.msslas, 0)) msslas,
sum(nvl(t1.ktslajs, 0)) ktslajs,
round(sum(nvl(t1.zxktsc, 0)), 2) zxktsc,
sum(nvl(t3.sdcs, 0)) sdcs,
sum(nvl(t2.zxsqlas, 0)) zxsqlas,
case
when sum(nvl(t1.msslasqs, 0)) = 0 then 0
else round(sum(nvl(t2.zxsqlas, 0)) / sum(nvl(t1.msslasqs, 0)) ::float * 100, 2) end zxsqlal,
sum(nvl(t1.zxlas, 0)) zxlas,
case
when sum(nvl(t1.msslas, 0)) = 0 then 0
else round(sum(nvl(t1.zxlas, 0)) / sum(nvl(t1.msslas, 0)) ::float * 100, 2) end zxlal,
sum(nvl(t1.zxtsajs, 0)) zxtsajs,
case
when sum(nvl(t1.ktslajs, 0)) = 0 then 0
else round(sum(nvl(t1.zxtsajs, 0)) / sum(nvl(t1.ktslajs, 0)) ::float * 100, 2) end zxtssyl,
sum(nvl(t1.zxtscs, 0)) zxtscs,
case
when sum(nvl(t1.zxtscs, 0)) = 0 then 0
else round(sum(nvl(t1.zxktsc, 0)) / sum(nvl(t1.zxtscs, 0)), 2) end zxtspjsc,
sum(nvl(t4.dzsdajs, 0)) dzsdajs,
sum(nvl(t4.dzsds, 0)) dzsds,
case
when sum(nvl(t3.sdcs, 0)) = 0 then 0
else round(sum(nvl(t4.dzsds, 0)) / sum(nvl(t3.sdcs, 0)) ::float * 100, 2) end dzsdsyl,
sum(nvl(t4.yxdzsds, 0)) yxdzsds,
case
when sum(nvl(t4.dzsds, 0)) = 0 then 0
else round(sum(nvl(t4.yxdzsds, 0)) / sum(nvl(t4.dzsds, 0)) ::float * 100, 2) end dzsdcgl,
sum(nvl(t5.dzsdwss, 0)) dzsdwss,
'000000' fydm
from rs.rs_dm_fy f
left join t1 on f.fydm = t1.fybm
left join t2 on f.fydm = t2.fybm
left join t3 on f.fydm = t3.fybm
left join t4 on f.fydm = t4.fybm
left join t5 on f.fydm = t5.fybm
where f.sfjy = 0
and f.fydm not in ('242000', '243000', '244000', '247000', '200000', '230000')
# t1 执行计划
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=79984219.81..89986231.80 rows=29 width=63) (actual time=43809.820..54246.806 rows=28 loops=1)
Group Key: v_tycx_ajjbxx.fybm
-> Sort (cost=79984219.81..79987131.18 rows=1164548 width=56) (actual time=43726.222..43978.471 rows=784588 loops=1)
Sort Key: v_tycx_ajjbxx.fybm
Sort Method: external merge Disk: 45768kB
-> Hash Left Join (cost=275398.62..79827078.30 rows=1164548 width=56) (actual time=6019.203..43086.439 rows=784588 loops=1)
Hash Cond: (v_tycx_ajjbxx.ajid = k.ajid)
Filter: (((v_tycx_ajjbxx.larq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.larq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR ((v_tycx_ajjbxx.sarq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.sarq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR (((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone)))
Rows Removed by Filter: 6517320
-> Seq Scan on v_tycx_ajjbxx (cost=0.00..79307395.42 rows=5951478 width=36) (actual time=0.022..28739.488 rows=6961866 loops=1)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 2)))
Rows Removed by Filter: 1615040
SubPlan 2
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z (cost=0.43..8.45 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=2370279)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 0
-> Hash (cost=205019.65..205019.65 rows=3639758 width=32) (actual time=6006.262..6006.262 rows=3726381 loops=1)
Buckets: 1048576 Batches: 8 Memory Usage: 36213kB
-> Seq Scan on sp_ktxx k (cost=0.00..205019.65 rows=3639758 width=32) (actual time=0.052..4746.285 rows=3726381 loops=1)
Filter: ((ktjg <> ALL ('{4,6}'::numeric[])) AND ((sfts)::text = '1'::text))
Rows Removed by Filter: 2303845
SubPlan 1
-> Index Scan using tab_satj_ix_tab_satj_ajid on tab_satj s (cost=0.43..8.46 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=746223)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: ((satj)::text = ANY ('{xsla,ysla,zzla,zzp,xsgs,jxjs,xfla,qtwsla,dyjf}'::text[]))
Rows Removed by Filter: 0
Planning Time: 1.840 ms
Execution Time: 54250.812 ms
(29 行记录)
# t2 执行计划
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=167402046.31..167402089.93 rows=24 width=15) (actual time=363974.672..364962.373 rows=24 loops=1)
Group Key: s.sqfy
-> Sort (cost=167402046.31..167402051.13 rows=1928 width=15) (actual time=363974.467..364138.202 rows=859664 loops=1)
Sort Key: s.sqfy
Sort Method: external merge Disk: 21880kB
-> Nested Loop (cost=0.43..167401941.11 rows=1928 width=15) (actual time=13.255..363209.943 rows=859664 loops=1)
-> Seq Scan on fy_wsla_wwzt a (cost=0.00..167052106.34 rows=503737 width=98) (actual time=3.434..351022.646 rows=1333077 loops=1)
Filter: ((czsj > '2018-01-01 00:00:00'::timestamp without time zone) AND (czsj >= '2018-01-01 00:00:00'::timestamp without time zone) AND ((SubPlan 1) >= '2018-01-01 00:00:00'::timestamp without time zone) AND ((CASE WHEN ((SubPlan 2) IS NOT NULL) THEN (SubPlan 3) ELSE zt END)::text <> 'SQ000'::text))
Rows Removed by Filter: 239028
SubPlan 1
-> Nested Loop (cost=0.85..35.55 rows=1 width=8) (actual time=0.014..0.029 rows=1 loops=1537495)
Join Filter: (fy_wsla_nwzt.czsj > b.czsj)
Rows Removed by Join Filter: 0
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt b (cost=0.43..8.45 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=1537495)
Index Cond: (sqbh = a.sqbh)
-> Append (cost=0.43..27.08 rows=2 width=14) (actual time=0.009..0.023 rows=1 loops=1537495)
-> Index Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt (cost=0.43..8.45 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=1537495)
Index Cond: (sqbh = a.sqbh)
Filter: (czsj > '2018-01-01 00:00:00'::timestamp without time zone)
-> Subquery Scan on "*SELECT* 2" (cost=0.70..18.62 rows=1 width=14) (actual time=0.016..0.016 rows=0 loops=1537495)
-> Nested Loop Anti Join (cost=0.70..18.61 rows=1 width=52) (actual time=0.016..0.016 rows=0 loops=1537495)
Join Filter: (fy_wsla_nwzt_1.sqbh = fy_wsla_dfbnwzt.sqbh)
-> Index Only Scan using fy_wsla_dfbnwzt_constraint_1 on fy_wsla_dfbnwzt (cost=0.28..10.15 rows=1 width=14) (actual time=0.015..0.015 rows=0 loops=1537495)
Index Cond: ((czsj > '2018-01-01 00:00:00'::timestamp without time zone) AND (sqbh = a.sqbh))
Heap Fetches: 0
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_1 (cost=0.43..8.45 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=155)
Index Cond: (sqbh = a.sqbh)
Heap Fetches: 1
SubPlan 2
-> Nested Loop (cost=0.85..35.82 rows=1 width=6) (actual time=0.008..0.113 rows=1 loops=1333078)
Join Filter: (fy_wsla_nwzt_2.czsj > b_1.czsj)
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt b_1 (cost=0.43..8.45 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=1333078)
Index Cond: (sqbh = a.sqbh)
-> Append (cost=0.43..27.35 rows=2 width=20) (actual time=0.004..0.109 rows=1 loops=1333078)
-> Index Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_2 (cost=0.43..8.45 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1333078)
Index Cond: (sqbh = a.sqbh)
-> Subquery Scan on "*SELECT* 2_1" (cost=0.43..18.89 rows=1 width=20) (actual time=0.104..0.104 rows=0 loops=1333078)
-> Nested Loop Anti Join (cost=0.43..18.88 rows=1 width=20) (actual time=0.104..0.104 rows=0 loops=1333078)
Join Filter: (fy_wsla_nwzt_3.sqbh = fy_wsla_dfbnwzt_1.sqbh)
-> Seq Scan on fy_wsla_dfbnwzt fy_wsla_dfbnwzt_1 (cost=0.00..10.43 rows=1 width=20) (actual time=0.103..0.103 rows=0 loops=1333078)
Filter: (sqbh = a.sqbh)
Rows Removed by Filter: 514
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_3 (cost=0.43..8.45 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=154)
Index Cond: (sqbh = a.sqbh)
Heap Fetches: 0
SubPlan 3
-> Nested Loop (cost=0.85..35.82 rows=1 width=6) (actual time=0.009..0.114 rows=1 loops=1333078)
Join Filter: (fy_wsla_nwzt_4.czsj > b_2.czsj)
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt b_2 (cost=0.43..8.45 rows=1 width=14) (actual time=0.004..0.005 rows=1 loops=1333078)
Index Cond: (sqbh = a.sqbh)
-> Append (cost=0.43..27.35 rows=2 width=20) (actual time=0.004..0.109 rows=1 loops=1333078)
-> Index Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_4 (cost=0.43..8.45 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1333078)
Index Cond: (sqbh = a.sqbh)
-> Subquery Scan on "*SELECT* 2_2" (cost=0.43..18.89 rows=1 width=20) (actual time=0.104..0.104 rows=0 loops=1333078)
-> Nested Loop Anti Join (cost=0.43..18.88 rows=1 width=20) (actual time=0.104..0.104 rows=0 loops=1333078)
Join Filter: (fy_wsla_nwzt_5.sqbh = fy_wsla_dfbnwzt_2.sqbh)
-> Seq Scan on fy_wsla_dfbnwzt fy_wsla_dfbnwzt_2 (cost=0.00..10.43 rows=1 width=20) (actual time=0.103..0.103 rows=0 loops=1333078)
Filter: (sqbh = a.sqbh)
Rows Removed by Filter: 514
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_5 (cost=0.43..8.45 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=154)
Index Cond: (sqbh = a.sqbh)
Heap Fetches: 0
-> Index Scan using ind_spcx_fy_wsla_sqjbxx_id on fy_wsla_sqjbxx s (cost=0.43..0.67 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=1333077)
Index Cond: (id = a.sqbh)
Filter: (((lsid IS NOT NULL) OR (userid IS NOT NULL)) AND (ajlb = ANY ('{2,4,5}'::numeric[])) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 0
Planning Time: 1.524 ms
Execution Time: 364963.074 ms
(68 行记录)
# t3 执行计划
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1105404.05..1105571.38 rows=29 width=15) (actual time=100523.963..101336.827 rows=24 loops=1)
Group Key: v_tycx_ajjbxx.fybm
-> Sort (cost=1105404.05..1105459.73 rows=22272 width=7) (actual time=100518.776..100996.887 rows=1888823 loops=1)
Sort Key: v_tycx_ajjbxx.fybm
Sort Method: external merge Disk: 31432kB
-> Nested Loop (cost=1000.56..1103795.68 rows=22272 width=7) (actual time=0.538..99736.246 rows=1888823 loops=1)
-> Gather (cost=1000.00..541736.69 rows=34697 width=31) (actual time=0.458..490.690 rows=2074515 loops=1)
Workers Planned: 6
Workers Launched: 6
-> Parallel Append (cost=0.00..537266.99 rows=5792 width=31) (actual time=0.160..1533.485 rows=296359 loops=7)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..370183.32 rows=11 width=31) (actual time=0.095..1600.095 rows=63540 loops=3)
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a (cost=0.00..370183.21 rows=2 width=97) (actual time=0.094..1592.106 rows=63540 loops=3)
Filter: (((fydm)::text = '210000'::text) AND ((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 5184535
-> Parallel Seq Scan on t_spgl_ems_txm t (cost=0.00..167054.71 rows=6937 width=31) (actual time=3.055..823.547 rows=269128 loops=7)
Filter: (((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 722404
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx (cost=0.56..16.19 rows=1 width=38) (actual time=0.047..0.047 rows=1 loops=2074515)
Index Cond: ((ah)::text = ("*SELECT* 2".ah)::text)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 1)))
Rows Removed by Filter: 0
SubPlan 1
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z (cost=0.43..8.45 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=228717)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 0
Planning Time: 0.789 ms
Execution Time: 101337.590 ms
(28 行记录)
# t4 执行计划
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=371382.38..371387.04 rows=25 width=31) (actual time=370026.926..570229.887 rows=24 loops=1)
Group Key: a.fydm
-> Sort (cost=371382.38..371383.01 rows=252 width=40) (actual time=368947.200..370421.516 rows=6767591 loops=1)
Sort Key: a.fydm
Sort Method: external merge Disk: 330944kB
-> Nested Loop Semi Join (cost=1000.56..371372.33 rows=252 width=40) (actual time=0.592..363935.473 rows=6767591 loops=1)
-> Gather (cost=1000.00..364662.52 rows=394 width=40) (actual time=0.501..2307.977 rows=7267012 loops=1)
Workers Planned: 6
Workers Launched: 2
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a (cost=0.00..363623.11 rows=66 width=40) (actual time=0.028..5224.235 rows=2422337 loops=3)
Filter: (((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 2825737
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx (cost=0.56..17.02 rows=1 width=31) (actual time=0.049..0.049 rows=1 loops=7267012)
Index Cond: ((ah)::text = (a.ah)::text)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 1)))
Rows Removed by Filter: 0
SubPlan 1
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z (cost=0.43..8.45 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=821982)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 0
Planning Time: 1.033 ms
Execution Time: 570233.406 ms
(23 行记录)
# t5 执行计划
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=653698.49..653698.58 rows=5 width=15) (actual time=131569.512..153922.072 rows=24 loops=1)
Group Key: t.fydm
-> Sort (cost=653698.49..653698.50 rows=5 width=38) (actual time=131394.205..131564.697 rows=775664 loops=1)
Sort Key: t.fydm
Sort Method: external merge Disk: 36312kB
-> Nested Loop Semi Join (cost=325460.48..653698.43 rows=5 width=38) (actual time=24400.818..130777.018 rows=775664 loops=1)
Join Filter: ((v_tycx_ajjbxx.ah)::text = (v_tycx_ajjbxx_1.ah)::text)
-> Gather (cost=325459.92..653617.27 rows=7 width=100) (actual time=24400.779..91105.634 rows=823030 loops=1)
Workers Planned: 6
Workers Launched: 0
-> Nested Loop (cost=324459.92..652616.57 rows=1 width=100) (actual time=24400.268..90990.938 rows=823030 loops=1)
-> Parallel Hash Semi Join (cost=324459.36..643091.57 rows=3023 width=69) (actual time=24400.058..28430.144 rows=1193286 loops=1)
Hash Cond: (((t.ah)::text = (a.ah)::text) AND (t.fsxxid = a.fsxxid))
-> Parallel Seq Scan on mv_sdxxws_tysdpt t (cost=0.00..317531.06 rows=199811 width=44) (actual time=4.518..8893.172 rows=1300955 loops=1)
Filter: (((sshdtz)::text ~~ '%判决书%'::text) OR ((sshdtz)::text ~~ '%裁定书%'::text) OR ((sshdtz)::text ~~ '%调解书%'::text))
Rows Removed by Filter: 9098265
-> Parallel Hash (cost=324262.56..324262.56 rows=13120 width=37) (actual time=14848.182..14848.183 rows=11651659 loops=1)
Buckets: 524288 (originally 131072) Batches: 32 (originally 1) Memory Usage: 29984kB
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a (cost=0.00..324262.56 rows=13120 width=37) (actual time=0.019..8557.858 rows=11651659 loops=1)
Filter: ((sdfs)::integer = 2)
Rows Removed by Filter: 4092564
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx (cost=0.56..3.14 rows=1 width=31) (actual time=0.052..0.052 rows=1 loops=1193286)
Index Cond: ((ah)::text = (t.ah)::text)
Filter: (((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 0
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx v_tycx_ajjbxx_1 (cost=0.56..11.58 rows=1 width=31) (actual time=0.048..0.048 rows=1 loops=823030)
Index Cond: ((ah)::text = (t.ah)::text)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 1)))
Rows Removed by Filter: 0
SubPlan 1
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z (cost=0.43..8.45 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=113631)
Index Cond: (ajid = v_tycx_ajjbxx_1.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 0
Planning Time: 5.693 ms
Execution Time: 153924.009 ms
(36 行记录)
# rs.rs_dm_fy 表 关联 t1 - t5 执行计划 （explain 的计划，根本跑不出真实的计划）
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=259518985.45..259518985.57 rows=1 width=672)
-> Merge Left Join (cost=249516753.33..259518983.52 rows=24 width=104)
Merge Cond: ((f.fydm)::text = (t_1.fydm)::text)
-> Merge Left Join (cost=248863054.84..258865284.79 rows=24 width=103)
Merge Cond: ((f.fydm)::text = (a_2.fydm)::text)
-> Merge Left Join (cost=248491672.46..258493897.21 rows=24 width=79)
Merge Cond: ((f.fydm)::text = (v_tycx_ajjbxx_1.fybm)::text)
-> Merge Left Join (cost=247386268.41..257388325.22 rows=24 width=71)
Merge Cond: ((f.fydm)::text = (s.sqfy)::text)
-> Merge Left Join (cost=79984222.10..89986234.77 rows=24 width=63)
Merge Cond: ((f.fydm)::text = (v_tycx_ajjbxx.fybm)::text)
-> Sort (cost=2.29..2.35 rows=24 width=7)
Sort Key: f.fydm
-> Seq Scan on rs_dm_fy f (cost=0.00..1.74 rows=24 width=7)
Filter: ((sfjy = '0'::numeric) AND ((fydm)::text <> ALL ('{242000,243000,244000,247000,200000,230000}'::text[])))
-> GroupAggregate (cost=79984219.81..89986231.80 rows=29 width=63)
Group Key: v_tycx_ajjbxx.fybm
-> Sort (cost=79984219.81..79987131.18 rows=1164548 width=56)
Sort Key: v_tycx_ajjbxx.fybm
-> Hash Left Join (cost=275398.62..79827078.30 rows=1164548 width=56)
Hash Cond: (v_tycx_ajjbxx.ajid = k.ajid)
Filter: (((v_tycx_ajjbxx.larq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.larq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR ((v_tycx_ajjbxx.sarq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.sarq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR (((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone)))
-> Seq Scan on v_tycx_ajjbxx (cost=0.00..79307395.42 rows=5951478 width=36)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 2)))
SubPlan 2
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z (cost=0.43..8.45 rows=1 width=0)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
-> Hash (cost=205019.65..205019.65 rows=3639758 width=32)
-> Seq Scan on sp_ktxx k (cost=0.00..205019.65 rows=3639758 width=32)
Filter: ((ktjg <> ALL ('{4,6}'::numeric[])) AND ((sfts)::text = '1'::text))
SubPlan 1
-> Index Scan using tab_satj_ix_tab_satj_ajid on tab_satj s_1 (cost=0.43..8.46 rows=1 width=0)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: ((satj)::text = ANY ('{xsla,ysla,zzla,zzp,xsgs,jxjs,xfla,qtwsla,dyjf}'::text[]))
-> GroupAggregate (cost=167402046.31..167402089.93 rows=24 width=15)
Group Key: s.sqfy
-> Sort (cost=167402046.31..167402051.13 rows=1928 width=15)
Sort Key: s.sqfy
-> Nested Loop (cost=0.43..167401941.11 rows=1928 width=15)
-> Seq Scan on fy_wsla_wwzt a (cost=0.00..167052106.34 rows=503737 width=98)
Filter: ((czsj > '2018-01-01 00:00:00'::timestamp without time zone) AND (czsj >= '2018-01-01 00:00:00'::timestamp without time zone) AND ((SubPlan 3) >= '2018-01-01 00:00:00'::timestamp without time zone) AND ((CASE WHEN ((SubPlan 4) IS NOT NULL) THEN (SubPlan 5) ELSE zt END)::text <> 'SQ000'::text))
SubPlan 3
-> Nested Loop (cost=0.85..35.55 rows=1 width=8)
Join Filter: (fy_wsla_nwzt.czsj > b.czsj)
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt b (cost=0.43..8.45 rows=1 width=14)
Index Cond: (sqbh = a.sqbh)
-> Append (cost=0.43..27.08 rows=2 width=14)
-> Index Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt (cost=0.43..8.45 rows=1 width=14)
Index Cond: (sqbh = a.sqbh)
Filter: (czsj > '2018-01-01 00:00:00'::timestamp without time zone)
-> Subquery Scan on "*SELECT* 2_1" (cost=0.70..18.62 rows=1 width=14)
-> Nested Loop Anti Join (cost=0.70..18.61 rows=1 width=52)
Join Filter: (fy_wsla_nwzt_1.sqbh = fy_wsla_dfbnwzt.sqbh)
-> Index Only Scan using fy_wsla_dfbnwzt_constraint_1 on fy_wsla_dfbnwzt (cost=0.28..10.15 rows=1 width=14)
Index Cond: ((czsj > '2018-01-01 00:00:00'::timestamp without time zone) AND (sqbh = a.sqbh))
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_1 (cost=0.43..8.45 rows=1 width=6)
Index Cond: (sqbh = a.sqbh)
SubPlan 4
-> Nested Loop (cost=0.85..35.82 rows=1 width=6)
Join Filter: (fy_wsla_nwzt_2.czsj > b_1.czsj)
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt b_1 (cost=0.43..8.45 rows=1 width=14)
Index Cond: (sqbh = a.sqbh)
-> Append (cost=0.43..27.35 rows=2 width=20)
-> Index Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_2 (cost=0.43..8.45 rows=1 width=20)
Index Cond: (sqbh = a.sqbh)
-> Subquery Scan on "*SELECT* 2_2" (cost=0.43..18.89 rows=1 width=20)
-> Nested Loop Anti Join (cost=0.43..18.88 rows=1 width=20)
Join Filter: (fy_wsla_nwzt_3.sqbh = fy_wsla_dfbnwzt_1.sqbh)
-> Seq Scan on fy_wsla_dfbnwzt fy_wsla_dfbnwzt_1 (cost=0.00..10.43 rows=1 width=20)
Filter: (sqbh = a.sqbh)
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_3 (cost=0.43..8.45 rows=1 width=6)
Index Cond: (sqbh = a.sqbh)
SubPlan 5
-> Nested Loop (cost=0.85..35.82 rows=1 width=6)
Join Filter: (fy_wsla_nwzt_4.czsj > b_2.czsj)
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt b_2 (cost=0.43..8.45 rows=1 width=14)
Index Cond: (sqbh = a.sqbh)
-> Append (cost=0.43..27.35 rows=2 width=20)
-> Index Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_4 (cost=0.43..8.45 rows=1 width=20)
Index Cond: (sqbh = a.sqbh)
-> Subquery Scan on "*SELECT* 2_3" (cost=0.43..18.89 rows=1 width=20)
-> Nested Loop Anti Join (cost=0.43..18.88 rows=1 width=20)
Join Filter: (fy_wsla_nwzt_5.sqbh = fy_wsla_dfbnwzt_2.sqbh)
-> Seq Scan on fy_wsla_dfbnwzt fy_wsla_dfbnwzt_2 (cost=0.00..10.43 rows=1 width=20)
Filter: (sqbh = a.sqbh)
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_5 (cost=0.43..8.45 rows=1 width=6)
Index Cond: (sqbh = a.sqbh)
-> Index Scan using ind_spcx_fy_wsla_sqjbxx_id on fy_wsla_sqjbxx s (cost=0.43..0.67 rows=1 width=22)
Index Cond: (id = a.sqbh)
Filter: (((lsid IS NOT NULL) OR (userid IS NOT NULL)) AND (ajlb = ANY ('{2,4,5}'::numeric[])) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
-> GroupAggregate (cost=1105404.05..1105571.38 rows=29 width=15)
Group Key: v_tycx_ajjbxx_1.fybm
-> Sort (cost=1105404.05..1105459.73 rows=22272 width=7)
Sort Key: v_tycx_ajjbxx_1.fybm
-> Nested Loop (cost=1000.56..1103795.68 rows=22272 width=7)
-> Gather (cost=1000.00..541736.69 rows=34697 width=31)
Workers Planned: 6
-> Parallel Append (cost=0.00..537266.99 rows=5792 width=31)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..370183.32 rows=11 width=31)
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a_1 (cost=0.00..370183.21 rows=2 width=97)
Filter: (((fydm)::text = '210000'::text) AND ((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
-> Parallel Seq Scan on t_spgl_ems_txm t (cost=0.00..167054.71 rows=6937 width=31)
Filter: (((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx v_tycx_ajjbxx_1 (cost=0.56..16.19 rows=1 width=38)
Index Cond: ((ah)::text = ("*SELECT* 2".ah)::text)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 6)))
SubPlan 6
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z_1 (cost=0.43..8.45 rows=1 width=0)
Index Cond: (ajid = v_tycx_ajjbxx_1.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
-> GroupAggregate (cost=371382.38..371387.04 rows=25 width=31)
Group Key: a_2.fydm
-> Sort (cost=371382.38..371383.01 rows=252 width=40)
Sort Key: a_2.fydm
-> Nested Loop Semi Join (cost=1000.56..371372.33 rows=252 width=40)
-> Gather (cost=1000.00..364662.52 rows=394 width=40)
Workers Planned: 6
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a_2 (cost=0.00..363623.11 rows=66 width=40)
Filter: (((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx v_tycx_ajjbxx_2 (cost=0.56..17.02 rows=1 width=31)
Index Cond: ((ah)::text = (a_2.ah)::text)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 7)))
SubPlan 7
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z_2 (cost=0.43..8.45 rows=1 width=0)
Index Cond: (ajid = v_tycx_ajjbxx_2.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
-> GroupAggregate (cost=653698.49..653698.58 rows=5 width=15)
Group Key: t_1.fydm
-> Sort (cost=653698.49..653698.50 rows=5 width=38)
Sort Key: t_1.fydm
-> Nested Loop Semi Join (cost=325460.48..653698.43 rows=5 width=38)
Join Filter: ((v_tycx_ajjbxx_3.ah)::text = (v_tycx_ajjbxx_4.ah)::text)
-> Gather (cost=325459.92..653617.27 rows=7 width=100)
Workers Planned: 6
-> Nested Loop (cost=324459.92..652616.57 rows=1 width=100)
-> Parallel Hash Semi Join (cost=324459.36..643091.57 rows=3023 width=69)
Hash Cond: (((t_1.ah)::text = (a_3.ah)::text) AND (t_1.fsxxid = a_3.fsxxid))
-> Parallel Seq Scan on mv_sdxxws_tysdpt t_1 (cost=0.00..317531.06 rows=199811 width=44)
Filter: (((sshdtz)::text ~~ '%判决书%'::text) OR ((sshdtz)::text ~~ '%裁定书%'::text) OR ((sshdtz)::text ~~ '%调解书%'::text))
-> Parallel Hash (cost=324262.56..324262.56 rows=13120 width=37)
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a_3 (cost=0.00..324262.56 rows=13120 width=37)
Filter: ((sdfs)::integer = 2)
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx v_tycx_ajjbxx_3 (cost=0.56..3.14 rows=1 width=31)
Index Cond: ((ah)::text = (t_1.ah)::text)
Filter: (((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using idx_v_tycx_ajjbxx_ah on v_tycx_ajjbxx v_tycx_ajjbxx_4 (cost=0.56..11.58 rows=1 width=31)
Index Cond: ((ah)::text = (t_1.ah)::text)
Filter: ((ajxz_tj = 2) OR ((ajlbbh = ANY ('{601,2140}'::integer[])) AND (SubPlan 8)))
SubPlan 8
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx z_3 (cost=0.43..8.45 rows=1 width=0)
Index Cond: (ajid = v_tycx_ajjbxx_4.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
(153 行记录)
这条 报表SQL 比较复杂，是通过 rs_dm_fy 表关联 5 张CTE表达式的表（t1 - t5）组成。
t1 - t5 执行计划被 cte 查询固定住，在生产环境跑2个小时跑不出结果。
而且 t1 - t5 表 里面的SQL语句执行起来都很慢，t1表 54秒 、t2 表 6分钟 、t3 表101秒、t4 表 9.5分钟 、t5 表 2.5分钟。
要优化这条SQL语句相当于要把前面 5 条 t1 - t5 表的慢SQL 都优化了，而且 t1 - t5 这几张表里面关联的表又全部是视图，所以优化起来很麻烦，慢慢抽丝剥茧来找到慢的节点进行优化。
直接放优化后的SQL加执行计划了，大家慢慢看，这里面优化哪些内容我也忘了 ：
# t1 表SQL 加执行计划：
select fybm,
count(distinct
case when sarq between date'2022-01-01' and date'2022-12-31' then a.ajid end) msslasqs,
count(distinct
case when larq between date'2022-01-01' and date'2022-12-31' then a.ajid end) msslas,
count(distinct case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31'
then a.ajid end) ktslajs,
count(distinct case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1') then a.ajid end) zxtsajs,
sum(case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1')
then 1
else 0 end) zxtscs,
sum(case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1')
then (cast(k.ktjsrq as date) - cast(k.ktksrq as date)) end) * 24 zxktsc,
count(distinct case
when larq between date'2022-01-01' and date'2022-12-31' and s.ajid is not null
then a.ajid end) zxlas
from spcx.v_tycx_ajjbxx a
left join (select ajid from sp_ajxx_zx where zxajxz in (4, 5, 6)) z on a.ajid = z.ajid
left join sp_ktxx k on a.ajid = k.ajid and k.sfts = '1' and k.ktjg not in (4, 6)
left join (select ajid
from spcx.tab_satj s1
where s1.satj in
('xsla', 'ysla', 'zzla', 'zzp', 'xsgs', 'jxjs', 'xfla', 'qtwsla',
'dyjf')) s
on s.ajid = a.ajid
where (ajxz_tj = 2 or ajlbbh in (601, 2140) and z.ajid is not null)
and (larq between date'2022-01-01' and date'2022-12-31'
or sarq between date'2022-01-01' and date'2022-12-31'
or trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31')
group by fybm;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1284115.15..1387629.58 rows=29 width=63) (actual time=5240.026..10652.240 rows=28 loops=1)
Group Key: v_tycx_ajjbxx.fybm
-> Sort (cost=1284115.15..1286144.84 rows=811875 width=67) (actual time=5217.784..5401.150 rows=785714 loops=1)
Sort Key: v_tycx_ajjbxx.fybm
Sort Method: external merge Disk: 60840kB
-> Gather (cost=137346.28..1171123.48 rows=811875 width=67) (actual time=762.493..4678.207 rows=785714 loops=1)
Workers Planned: 7
Workers Launched: 7
-> Nested Loop Left Join (cost=136346.28..1088935.98 rows=115982 width=67) (actual time=756.234..4690.542 rows=98214 loops=8)
-> Nested Loop Left Join (cost=136345.84..1032131.15 rows=115982 width=55) (actual time=756.161..3870.860 rows=98214 loops=8)
Filter: ((v_tycx_ajjbxx.ajxz_tj = 2) OR ((v_tycx_ajjbxx.ajlbbh = ANY ('{601,2140}'::integer[])) AND (sp_ajxx_zx.ajid IS NOT NULL)))
Rows Removed by Filter: 2353
-> Parallel Hash Left Join (cost=136345.41..942350.26 rows=166957 width=59) (actual time=756.123..3210.751 rows=100568 loops=8)
Hash Cond: (v_tycx_ajjbxx.ajid = k.ajid)
Filter: (((v_tycx_ajjbxx.larq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.larq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR ((v_tycx_ajjbxx.sarq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.sarq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR (((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone)))
Rows Removed by Filter: 868288
-> Parallel Seq Scan on v_tycx_ajjbxx (cost=0.00..800060.10 rows=884565 width=39) (actual time=0.030..1478.289 rows=926186 loops=8)
Filter: ((ajxz_tj = 2) OR (ajlbbh = ANY ('{601,2140}'::integer[])))
Rows Removed by Filter: 154192
-> Parallel Hash (cost=126889.21..126889.21 rows=756496 width=32) (actual time=750.899..750.908 rows=468075 loops=8)
Buckets: 4194304 Batches: 1 Memory Usage: 269760kB
-> Parallel Seq Scan on sp_ktxx k (cost=0.00..126889.21 rows=756496 width=32) (actual time=0.026..519.663 rows=468075 loops=8)
Filter: ((ktjg <> ALL ('{4,6}'::numeric[])) AND ((sfts)::text = '1'::text))
Rows Removed by Filter: 295743
-> Index Scan using idx_sp_ajxx_zx_ajid on sp_ajxx_zx (cost=0.43..0.52 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=804541)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 0
-> Index Only Scan using ind_spcx_tab_satj_ajid_satj on tab_satj s1 (cost=0.43..0.49 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=785714)
Index Cond: (ajid = v_tycx_ajjbxx.ajid)
Filter: ((satj)::text = ANY ('{xsla,ysla,zzla,zzp,xsgs,jxjs,xfla,qtwsla,dyjf}'::text[]))
Rows Removed by Filter: 0
Heap Fetches: 500670
Planning Time: 2.167 ms
Execution Time: 10653.452 ms
(35 rows)
# t2 表SQL 加执行计划：
select sqfy fybm,
sum(case
when trunc(sqsj) between date'2022-01-01' and date'2022-12-31' then 1
else 0 end) zxsqlas
from spcx.fy_wsla_sqjbxx s
join spcx.fy_wsla_zt_nw z on s.id = z.sqbh
left join spcx.fy_wsla_spzxjg j on s.id = j.sqbh
where (lsid is not null
or userid is not null)
and ajlb in (2, 4, 5)
and trunc(sqsj) between date'2022-01-01' and date'2022-12-31'
and z.zt != 'SQ000'
group by sqfy;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=188981.85..189004.97 rows=24 width=15) (actual time=4568.801..4906.953 rows=24 loops=1)
Group Key: s.sqfy
-> Gather Merge (cost=188981.85..189004.37 rows=72 width=15) (actual time=4567.189..4906.902 rows=96 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Partial GroupAggregate (cost=187981.81..187995.87 rows=24 width=15) (actual time=4558.555..4838.875 rows=24 loops=4)
Group Key: s.sqfy
-> Sort (cost=187981.81..187983.35 rows=614 width=15) (actual time=4558.510..4579.712 rows=215125 loops=4)
Sort Key: s.sqfy
Sort Method: quicksort Memory: 16401kB
Worker 0: Sort Method: quicksort Memory: 16147kB
Worker 1: Sort Method: quicksort Memory: 15964kB
Worker 2: Sort Method: quicksort Memory: 16402kB
-> Parallel Hash Left Join (cost=92287.99..187953.38 rows=614 width=15) (actual time=1820.006..4444.892 rows=215125 loops=4)
Hash Cond: (a.sqbh = "*SELECT* 2".sqbh)
Filter: ((CASE WHEN ((CASE WHEN ("*SELECT* 2".sqbh IS NOT NULL) THEN "*SELECT* 2".zt ELSE NULL::character varying END)::character varying(10 char) IS NOT NULL) THEN (CASE WHEN ("*SELECT* 2".sqbh IS NOT NULL) THEN "*SELECT* 2".zt ELSE NULL::character varying END)::character varying(10 char) ELSE a.zt END)::text <> 'SQ000'::text)
-> Parallel Hash Left Join (cost=45922.96..129828.10 rows=617 width=27) (actual time=904.641..3307.961 rows=215125 loops=4)
Hash Cond: (a.sqbh = "*SELECT* 2_1".sqbh)
Filter: (CASE WHEN ("*SELECT* 2_1".sqbh IS NOT NULL) THEN "*SELECT* 2_1".czsj ELSE NULL::timestamp without time zone END >= '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 7018
-> Nested Loop (cost=0.43..49470.36 rows=1850 width=27) (actual time=0.130..2194.549 rows=222143 loops=4)
-> Parallel Seq Scan on fy_wsla_sqjbxx s (cost=0.00..37260.51 rows=1892 width=22) (actual time=0.073..511.364 rows=222144 loops=4)
Filter: (((lsid IS NOT NULL) OR (userid IS NOT NULL)) AND (ajlb = ANY ('{2,4,5}'::numeric[])) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 159736
-> Index Scan using fy_wsla_wwzt_constraint_1 on fy_wsla_wwzt a (cost=0.43..6.45 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=888575)
Index Cond: (sqbh = s.id)
Filter: ((czsj > '2018-01-01 00:00:00'::timestamp without time zone) AND (czsj >= '2018-01-01 00:00:00'::timestamp without time zone))
-> Parallel Hash (cost=44061.59..44061.59 rows=148875 width=14) (actual time=903.360..903.469 rows=345556 loops=4)
Buckets: 2097152 (originally 524288) Batches: 1 (originally 1) Memory Usage: 93632kB
-> Parallel Hash Join (cost=23349.79..44061.59 rows=148875 width=14) (actual time=260.845..626.852 rows=345556 loops=4)
Hash Cond: ("*SELECT* 2_1".sqbh = b_1.sqbh)
Join Filter: ("*SELECT* 2_1".czsj > b_1.czsj)
Rows Removed by Join Filter: 1630
-> Parallel Append (cost=0.00..19539.40 rows=446627 width=14) (actual time=0.037..115.190 rows=347366 loops=4)
-> Subquery Scan on "*SELECT* 2_1" (cost=0.43..883.52 rows=1 width=14) (actual time=0.110..0.896 rows=187 loops=1)
-> Nested Loop Anti Join (cost=0.43..883.51 rows=1 width=52) (actual time=0.108..0.873 rows=187 loops=1)
-> Seq Scan on fy_wsla_dfbnwzt (cost=0.00..10.43 rows=187 width=14) (actual time=0.071..0.144 rows=188 loops=1)
Filter: (czsj > '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 326
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_2 (cost=0.43..4.66 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=188)
Index Cond: (sqbh = fy_wsla_dfbnwzt.sqbh)
Heap Fetches: 0
-> Parallel Seq Scan on fy_wsla_nwzt fy_wsla_nwzt_1 (cost=0.00..16422.75 rows=446626 width=14) (actual time=0.009..90.514 rows=347319 loops=4)
Filter: (czsj > '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 7613
-> Parallel Hash (cost=16704.91..16704.91 rows=531591 width=14) (actual time=256.397..256.398 rows=403681 loops=4)
Buckets: 2097152 Batches: 1 Memory Usage: 92256kB
-> Parallel Seq Scan on fy_wsla_wwzt b_1 (cost=0.00..16704.91 rows=531591 width=14) (actual time=0.008..85.241 rows=403681 loops=4)
-> Parallel Hash (cost=44459.44..44459.44 rows=152447 width=12) (actual time=914.183..914.187 rows=353218 loops=4)
Buckets: 2097152 (originally 524288) Batches: 1 (originally 1) Memory Usage: 95040kB
-> Parallel Hash Join (cost=23349.79..44459.44 rows=152447 width=12) (actual time=292.313..681.012 rows=353218 loops=4)
Hash Cond: ("*SELECT* 2".sqbh = b.sqbh)
Join Filter: ("*SELECT* 2".czsj > b.czsj)
Rows Removed by Join Filter: 1636
-> Parallel Append (cost=0.00..19909.13 rows=457341 width=20) (actual time=0.032..108.101 rows=355058 loops=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.43..2343.02 rows=1 width=20) (actual time=0.091..2.177 rows=504 loops=1)
-> Nested Loop Anti Join (cost=0.43..2343.01 rows=1 width=20) (actual time=0.090..2.119 rows=504 loops=1)
-> Seq Scan on fy_wsla_dfbnwzt fy_wsla_dfbnwzt_1 (cost=0.00..9.14 rows=514 width=20) (actual time=0.039..0.160 rows=514 loops=1)
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_3 (cost=0.43..4.53 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=514)
Index Cond: (sqbh = fy_wsla_dfbnwzt_1.sqbh)
Heap Fetches: 0
-> Parallel Seq Scan on fy_wsla_nwzt (cost=0.00..15279.40 rows=457340 width=20) (actual time=0.010..82.603 rows=354932 loops=4)
-> Parallel Hash (cost=16704.91..16704.91 rows=531591 width=14) (actual time=287.919..287.920 rows=403681 loops=4)
Buckets: 2097152 Batches: 1 Memory Usage: 92256kB
-> Parallel Seq Scan on fy_wsla_wwzt b (cost=0.00..16704.91 rows=531591 width=14) (actual time=0.017..90.439 rows=403681 loops=4)
Planning Time: 2.070 ms
Execution Time: 4907.228 ms
(67 rows)
# t3 表SQL 加执行计划：
select
fybm,
count(1) sdcs
from spcx.v_tycx_ajjbxx a
inner join V_MV_EMS_TXM t on a.ah = t.ah
left join (select ajid from sp_ajxx_zx where zxajxz in (4, 5, 6)) z on a.ajid = z.ajid
where (ajxz_tj = 2 or ajlbbh in (601, 2140) and z.ajid is not null)
and trunc(cjsj) between date '2022-01-01' and date '2022-12-31'
group by fybm;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=1433095.21..1433139.10 rows=29 width=15) (actual time=5220.154..5378.018 rows=24 loops=1)
Group Key: v_tycx_ajjbxx.fybm
-> Gather Merge (cost=1433095.21..1433138.08 rows=145 width=15) (actual time=5205.965..5377.948 rows=144 loops=1)
Workers Planned: 5
Workers Launched: 5
-> Partial GroupAggregate (cost=1432095.14..1432120.54 rows=29 width=15) (actual time=5182.444..5266.338 rows=24 loops=6)
Group Key: v_tycx_ajjbxx.fybm
-> Sort (cost=1432095.14..1432103.51 rows=3349 width=7) (actual time=5181.868..5210.072 rows=314805 loops=6)
Sort Key: v_tycx_ajjbxx.fybm
Sort Method: quicksort Memory: 26934kB
Worker 0: Sort Method: quicksort Memory: 26681kB
Worker 1: Sort Method: quicksort Memory: 18190kB
Worker 2: Sort Method: quicksort Memory: 31048kB
Worker 3: Sort Method: quicksort Memory: 27735kB
Worker 4: Sort Method: quicksort Memory: 25538kB
-> Parallel Hash Join (cost=924722.71..1431899.06 rows=3349 width=7) (actual time=4679.049..5068.448 rows=314805 loops=6)
Hash Cond: (("*SELECT* 2".ah)::text = (v_tycx_ajjbxx.ah)::text)
-> Parallel Append (cost=0.00..502269.64 rows=5792 width=31) (actual time=35.177..1251.332 rows=345752 loops=6)
-> Subquery Scan on "*SELECT* 2" (cost=16282.89..335185.97 rows=11 width=31) (actual time=70.236..402.888 rows=63540 loops=3)
-> Parallel Bitmap Heap Scan on mv_sdxxfs_tysdpt a (cost=16282.89..335185.86 rows=2 width=97) (actual time=70.234..395.923 rows=63540 loops=3)
Recheck Cond: ((fydm)::text = '210000'::text)
Filter: (((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 84781
-> Bitmap Index Scan on ind_xdata_mv_sdxxfs_tysdpt_fydm_ah (cost=0.00..16282.89 rows=445110 width=0) (actual time=63.011..63.012 rows=444961 loops=1)
Index Cond: ((fydm)::text = '210000'::text)
-> Parallel Seq Scan on t_spgl_ems_txm t (cost=0.00..167054.71 rows=6937 width=31) (actual time=0.544..1022.733 rows=313983 loops=6)
Filter: (((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 842804
-> Parallel Hash (cost=912240.57..912240.57 rows=614491 width=38) (actual time=3248.277..3248.308 rows=1170302 loops=6)
Buckets: 524288 Batches: 16 Memory Usage: 35680kB
-> Parallel Hash Left Join (cost=109858.48..912240.57 rows=614491 width=38) (actual time=465.853..2900.193 rows=1170302 loops=6)
Hash Cond: (v_tycx_ajjbxx.ajid = sp_ajxx_zx.ajid)
Filter: ((v_tycx_ajjbxx.ajxz_tj = 2) OR ((v_tycx_ajjbxx.ajlbbh = ANY ('{601,2140}'::integer[])) AND (sp_ajxx_zx.ajid IS NOT NULL)))
Rows Removed by Filter: 64612
-> Parallel Seq Scan on v_tycx_ajjbxx (cost=0.00..800060.10 rows=884565 width=54) (actual time=0.019..1665.925 rows=1234914 loops=6)
Filter: ((ajxz_tj = 2) OR (ajlbbh = ANY ('{601,2140}'::integer[])))
Rows Removed by Filter: 205589
-> Parallel Hash (cost=104210.63..104210.63 rows=451828 width=12) (actual time=459.967..459.995 rows=370004 loops=6)
Buckets: 4194304 Batches: 1 Memory Usage: 137024kB
-> Parallel Seq Scan on sp_ajxx_zx (cost=0.00..104210.63 rows=451828 width=12) (actual time=0.050..291.962 rows=370004 loops=6)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 89596
Planning Time: 1.188 ms
Execution Time: 5378.178 ms
(44 rows)
# t4 表SQL 加执行计划：
select
t.fydm fybm,
count(distinct t.ah) dzsdajs,
count(1) dzsds,
sum(case when sdzt = 1 then 1 else 0 end) yxdzsds
from MV_SDXXFS_TYSDPT t
left join spcx.v_tycx_ajjbxx a on t.ah = a.ah
left join sp_ajxx_zx z on a.ajid = z.ajid
where (a.ajxz_tj = 2 or ajlbbh in (601, 2140) and z.zxajxz in (4, 5, 6))
and trunc(fssdsj) between date'2022-01-02' and date'2022-12-31'
group by t.fydm;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1301622.23..1301625.87 rows=24 width=31) (actual time=10262.123..139914.026 rows=24 loops=1)
Group Key: a.fydm
-> Sort (cost=1301622.23..1301622.72 rows=194 width=40) (actual time=9476.617..10970.756 rows=6767633 loops=1)
Sort Key: a.fydm
Sort Method: external merge Disk: 330936kB
-> Gather (cost=494900.94..1301614.86 rows=194 width=40) (actual time=5255.109..6490.648 rows=6767633 loops=1)
Workers Planned: 7
Workers Launched: 7
-> Parallel Hash Left Join (cost=493900.94..1300595.46 rows=28 width=40) (actual time=5252.826..6392.983 rows=845954 loops=8)
Hash Cond: (v_tycx_ajjbxx.ajid = z.ajid)
Filter: ((v_tycx_ajjbxx.ajxz_tj = 2) OR ((v_tycx_ajjbxx.ajlbbh = ANY ('{601,2140}'::integer[])) AND (z.zxajxz = ANY ('{4,5,6}'::integer[]))))
Rows Removed by Filter: 9452
-> Parallel Hash Join (cost=384812.19..1191506.60 rows=41 width=56) (actual time=4875.599..5714.763 rows=855407 loops=8)
Hash Cond: ((v_tycx_ajjbxx.ah)::text = (a.ah)::text)
-> Parallel Seq Scan on v_tycx_ajjbxx (cost=0.00..800060.10 rows=884565 width=47) (actual time=0.107..1262.620 rows=926186 loops=8)
Filter: ((ajxz_tj = 2) OR (ajlbbh = ANY ('{601,2140}'::integer[])))
Rows Removed by Filter: 154192
-> Parallel Hash (cost=384811.33..384811.33 rows=69 width=40) (actual time=3216.805..3216.806 rows=908376 loops=8)
Buckets: 524288 (originally 1024) Batches: 16 (originally 1) Memory Usage: 38528kB
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a (cost=0.00..384811.33 rows=69 width=40) (actual time=0.060..2547.594 rows=908376 loops=8)
Filter: (((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-02 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 1170282
-> Parallel Hash (cost=102120.00..102120.00 rows=557500 width=14) (actual time=372.256..372.273 rows=344700 loops=8)
Buckets: 4194304 Batches: 1 Memory Usage: 162336kB
-> Parallel Seq Scan on sp_ajxx_zx z (cost=0.00..102120.00 rows=557500 width=14) (actual time=0.021..205.679 rows=344700 loops=8)
Planning Time: 1.448 ms
Execution Time: 139917.446 ms
(27 rows)
# t5 表SQL 加执行计划：
select
t.fydm fydm,
count(distinct a.ah) dzsdwss
from spcx.v_tycx_ajjbxx a
join MV_SDXXWS_TYSDPT t on a.ah = t.ah
left join spcx.v_tycx_ajjbxx x on t.ah = x.ah
left join sp_ajxx_zx z on x.ajid = z.ajid
left join MV_SDXXFS_TYSDPT t1 on (t.ah = t1.ah and t.fsxxid = t1.fsxxid)
where (
x.ajxz_tj = 2 or a.ajlbbh in (601, 2140) and z.zxajxz in (4, 5, 6)
)
and t1.ah is not null
and (sshdtz like '%判决书%' or sshdtz like '%裁定书%' or sshdtz like '%调解书%')
and trunc(a.jarq) between date'2022-01-01' and date'2022-12-31'
group by t.fydm;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=2446311.15..2446311.17 rows=1 width=15) (actual time=31099.780..78891.280 rows=24 loops=1)
Group Key: t.fydm
-> Sort (cost=2446311.15..2446311.16 rows=1 width=38) (actual time=30728.822..31317.552 rows=2481088 loops=1)
Sort Key: t.fydm
Sort Method: external merge Disk: 116184kB
-> Hash Join (cost=1628893.79..2446311.14 rows=1 width=38) (actual time=26583.707..29566.423 rows=2481088 loops=1)
Hash Cond: ((v_tycx_ajjbxx.ah)::text = (t.ah)::text)
Join Filter: ((v_tycx_ajjbxx_1.ajxz_tj = 2) OR ((v_tycx_ajjbxx.ajlbbh = ANY ('{601,2140}'::integer[])) AND (z.zxajxz = ANY ('{4,5,6}'::integer[]))))
Rows Removed by Join Filter: 145278
-> Gather (cost=1000.00..818250.21 rows=44567 width=33) (actual time=0.468..961.568 rows=847169 loops=1)
Workers Planned: 7
Workers Launched: 7
-> Parallel Seq Scan on v_tycx_ajjbxx (cost=0.00..812793.51 rows=6367 width=33) (actual time=0.167..1190.351 rows=105896 loops=8)
Filter: (((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 974481
-> Hash (cost=1627893.78..1627893.78 rows=1 width=73) (actual time=26582.882..26641.028 rows=4018979 loops=1)
Buckets: 524288 (originally 1024) Batches: 16 (originally 1) Memory Usage: 36865kB
-> Gather (cost=1274336.32..1627893.78 rows=1 width=73) (actual time=21130.062..25451.581 rows=4018979 loops=1)
Workers Planned: 6
Workers Launched: 1
-> Parallel Hash Left Join (cost=1273336.32..1626893.68 rows=1 width=73) (actual time=21087.430..23796.922 rows=2009490 loops=2)
Hash Cond: (v_tycx_ajjbxx_1.ajid = z.ajid)
-> Parallel Hash Left Join (cost=1164247.57..1517804.92 rows=1 width=83) (actual time=19762.408..21731.225 rows=2009490 loops=2)
Hash Cond: ((t.ah)::text = (v_tycx_ajjbxx_1.ah)::text)
-> Parallel Hash Join (cost=343445.42..685808.75 rows=1 width=69) (actual time=11204.647..13535.597 rows=2009490 loops=2)
Hash Cond: (((t.ah)::text = (a.ah)::text) AND (t.fsxxid = a.fsxxid))
-> Parallel Seq Scan on mv_sdxxws_tysdpt t (cost=0.00..339116.37 rows=216464 width=44) (actual time=0.189..3101.952 rows=683044 loops=2)
Filter: (((sshdtz)::text ~~ '%判决书%'::text) OR ((sshdtz)::text ~~ '%裁定书%'::text) OR ((sshdtz)::text ~~ '%调解书%'::text))
Rows Removed by Filter: 4871648
-> Parallel Hash (cost=343238.16..343238.16 rows=13817 width=37) (actual time=7759.524..7759.524 rows=6236342 loops=2)
Buckets: 524288 (originally 131072) Batches: 32 (originally 1) Memory Usage: 31776kB
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a (cost=0.00..343238.16 rows=13817 width=37) (actual time=0.074..4462.048 rows=6236342 loops=2)
Filter: ((ah IS NOT NULL) AND ((sdfs)::integer = 2))
Rows Removed by Filter: 2078291
-> Parallel Hash (cost=793693.40..793693.40 rows=1273340 width=45) (actual time=5591.546..5591.547 rows=4321510 loops=2)
Buckets: 524288 Batches: 32 Memory Usage: 25632kB
-> Parallel Seq Scan on v_tycx_ajjbxx v_tycx_ajjbxx_1 (cost=0.00..793693.40 rows=1273340 width=45) (actual time=0.016..4287.377 rows=4321510 loops=2)
-> Parallel Hash (cost=102120.00..102120.00 rows=557500 width=14) (actual time=1306.198..1306.198 rows=1378798 loops=2)
Buckets: 4194304 Batches: 1 Memory Usage: 162208kB
-> Parallel Seq Scan on sp_ajxx_zx z (cost=0.00..102120.00 rows=557500 width=14) (actual time=0.019..689.995 rows=1378798 loops=2)
Planning Time: 2.716 ms
Execution Time: 78892.986 ms
(42 rows)
rs.rs_dm_fy 表 关联 t1 - t5 执行计划 （优化后真实的计划）
select
'全市' fymc,
sum(nvl(t1.msslasqs, 0)) msslasqs,
sum(nvl(t1.msslas, 0)) msslas,
sum(nvl(t1.ktslajs, 0)) ktslajs,
round(sum(nvl(t1.zxktsc, 0)), 2) zxktsc,
sum(nvl(t3.sdcs, 0)) sdcs,
sum(nvl(t2.zxsqlas, 0)) zxsqlas,
case
when sum(nvl(t1.msslasqs, 0)) = 0 then 0
else round(sum(nvl(t2.zxsqlas, 0)) / sum(nvl(t1.msslasqs, 0))::float * 100, 2) end zxsqlal,
sum(nvl(t1.zxlas, 0)) zxlas,
case
when sum(nvl(t1.msslas, 0)) = 0 then 0
else round(sum(nvl(t1.zxlas, 0)) / sum(nvl(t1.msslas, 0))::float * 100, 2) end zxlal,
sum(nvl(t1.zxtsajs, 0)) zxtsajs,
case
when sum(nvl(t1.ktslajs, 0)) = 0 then 0
else round(sum(nvl(t1.zxtsajs, 0)) / sum(nvl(t1.ktslajs, 0))::float * 100, 2) end zxtssyl,
sum(nvl(t1.zxtscs, 0)) zxtscs,
case
when sum(nvl(t1.zxtscs, 0)) = 0 then 0
else round(sum(nvl(t1.zxktsc, 0)) / sum(nvl(t1.zxtscs, 0)), 2) end zxtspjsc,
sum(nvl(t4.dzsdajs, 0)) dzsdajs,
sum(nvl(t4.dzsds, 0)) dzsds,
case
when sum(nvl(t3.sdcs, 0)) = 0 then 0
else round(sum(nvl(t4.dzsds, 0)) / sum(nvl(t3.sdcs, 0))::float * 100, 2) end dzsdsyl,
sum(nvl(t4.yxdzsds, 0)) yxdzsds,
case
when sum(nvl(t4.dzsds, 0)) = 0 then 0
else round(sum(nvl(t4.yxdzsds, 0)) / sum(nvl(t4.dzsds, 0))::float * 100, 2) end dzsdcgl,
sum(nvl(t5.dzsdwss, 0)) dzsdwss,
'000000' fydm
from rs.rs_dm_fy f
left join (select fybm,
count(distinct
case when sarq between date'2022-01-01' and date'2022-12-31' then a.ajid end) msslasqs,
count(distinct
case when larq between date'2022-01-01' and date'2022-12-31' then a.ajid end) msslas,
count(distinct case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31'
then a.ajid end) ktslajs,
count(distinct case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1') then a.ajid end) zxtsajs,
sum(case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1')
then 1
else 0 end) zxtscs,
sum(case
when trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31' and
(k.wstp = '1' or k.zxzqrhy = '1')
then (cast(k.ktjsrq as date) - cast(k.ktksrq as date)) end) * 24 zxktsc,
count(distinct case
when larq between date'2022-01-01' and date'2022-12-31' and s.ajid is not null
then a.ajid end) zxlas
from spcx.v_tycx_ajjbxx a
left join (select ajid from sp_ajxx_zx where zxajxz in (4, 5, 6)) z on a.ajid = z.ajid
left join sp_ktxx k on a.ajid = k.ajid and k.sfts = '1' and k.ktjg not in (4, 6)
left join (select ajid
from spcx.tab_satj s1
where s1.satj in
('xsla', 'ysla', 'zzla', 'zzp', 'xsgs', 'jxjs', 'xfla', 'qtwsla',
'dyjf')) s
on s.ajid = a.ajid
where (ajxz_tj = 2 or ajlbbh in (601, 2140) and z.ajid is not null)
and (larq between date'2022-01-01' and date'2022-12-31'
or sarq between date'2022-01-01' and date'2022-12-31'
or trunc(k.ktjsrq) between date'2022-01-01' and date'2022-12-31')
group by fybm) t1 on f.fydm = t1.fybm
left join (select sqfy fybm,
sum(case
when trunc(sqsj) between date'2022-01-01' and date'2022-12-31' then 1
else 0 end) zxsqlas
from spcx.fy_wsla_sqjbxx s
join spcx.fy_wsla_zt_nw z on s.id = z.sqbh
left join spcx.fy_wsla_spzxjg j on s.id = j.sqbh
where (lsid is not null
or userid is not null)
and ajlb in (2, 4, 5)
and trunc(sqsj) between date'2022-01-01' and date'2022-12-31'
and z.zt != 'SQ000'
group by sqfy) t2 on f.fydm = t2.fybm
left join (select
fybm,
count(1) sdcs
from spcx.v_tycx_ajjbxx a
inner join V_MV_EMS_TXM t on a.ah = t.ah
left join (select ajid from sp_ajxx_zx where zxajxz in (4, 5, 6)) z on a.ajid = z.ajid
where (ajxz_tj = 2 or ajlbbh in (601, 2140) and z.ajid is not null)
and trunc(cjsj) between date '2022-01-01' and date '2022-12-31'
group by fybm) t3 on f.fydm = t3.fybm
left join (select
t.fydm fybm,
count(distinct t.ah) dzsdajs,
count(1) dzsds,
sum(case when sdzt = 1 then 1 else 0 end) yxdzsds
from MV_SDXXFS_TYSDPT t
left join spcx.v_tycx_ajjbxx a on t.ah = a.ah
left join sp_ajxx_zx z on a.ajid = z.ajid
where (a.ajxz_tj = 2 or ajlbbh in (601, 2140) and z.zxajxz in (4, 5, 6))
and trunc(fssdsj) between date'2022-01-02' and date'2022-12-31'
group by t.fydm) t4 on f.fydm = t4.fybm
left join (select t.fydm fydm,
count(distinct a.ah) dzsdwss
from spcx.v_tycx_ajjbxx a
join MV_SDXXWS_TYSDPT t on a.ah = t.ah
left join spcx.v_tycx_ajjbxx x on t.ah = x.ah
left join sp_ajxx_zx z on x.ajid = z.ajid
left join MV_SDXXFS_TYSDPT t1 on (t.ah = t1.ah and t.fsxxid = t1.fsxxid)
where (
x.ajxz_tj = 2 or a.ajlbbh in (601, 2140) and z.zxajxz in (4, 5, 6)
)
and t1.ah is not null
and (sshdtz like '%判决书%' or sshdtz like '%裁定书%' or sshdtz like '%调解书%')
and trunc(a.jarq) between date'2022-01-01' and date'2022-12-31'
group by t.fydm) t5 on f.fydm = t5.fydm
where f.sfjy = 0
and f.fydm not in ('242000', '243000', '244000', '247000', '200000', '230000');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6941139.54..6941139.66 rows=1 width=672) (actual time=266092.951..266217.538 rows=1 loops=1)
-> Merge Left Join (cost=6837550.12..6941137.61 rows=24 width=104) (actual time=67817.666..266217.282 rows=23 loops=1)
Merge Cond: ((f.fydm)::text = (t_1.fydm)::text)
-> Merge Left Join (cost=4391238.97..4494826.36 rows=24 width=103) (actual time=34976.837..186504.676 rows=23 loops=1)
Merge Cond: ((f.fydm)::text = (a_2.fydm)::text)
-> Merge Left Join (cost=3089616.74..3193199.97 rows=24 width=79) (actual time=15086.694..20757.951 rows=23 loops=1)
Merge Cond: ((f.fydm)::text = (v_tycx_ajjbxx_1.fybm)::text)
-> Merge Left Join (cost=1656521.53..1760060.26 rows=24 width=71) (actual time=9749.538..15329.433 rows=23 loops=1)
Merge Cond: ((f.fydm)::text = (s.sqfy)::text)
-> Merge Left Join (cost=1364871.34..1468386.44 rows=24 width=63) (actual time=5004.476..10331.236 rows=23 loops=1)
Merge Cond: ((f.fydm)::text = (v_tycx_ajjbxx.fybm)::text)
-> Sort (cost=2.29..2.35 rows=24 width=7) (actual time=0.064..0.083 rows=23 loops=1)
Sort Key: f.fydm
Sort Method: quicksort Memory: 26kB
-> Seq Scan on rs_dm_fy f (cost=0.00..1.74 rows=24 width=7) (actual time=0.018..0.038 rows=23 loops=1)
Filter: ((sfjy = '0'::numeric) AND ((fydm)::text <> ALL ('{242000,243000,244000,247000,200000,230000}'::text[])))
Rows Removed by Filter: 14
-> GroupAggregate (cost=1364869.05..1468383.47 rows=29 width=63) (actual time=4908.114..10330.910 rows=28 loops=1)
Group Key: v_tycx_ajjbxx.fybm
-> Sort (cost=1364869.05..1366898.73 rows=811875 width=67) (actual time=4885.531..5084.006 rows=785714 loops=1)
Sort Key: v_tycx_ajjbxx.fybm
Sort Method: external merge Disk: 60840kB
-> Gather (cost=363942.31..1251877.37 rows=811875 width=67) (actual time=1690.899..4305.802 rows=785714 loops=1)
Workers Planned: 7
Workers Launched: 7
-> Parallel Hash Left Join (cost=362942.31..1169689.87 rows=115982 width=67) (actual time=1682.630..4249.550 rows=98214 loops=8)
Hash Cond: (v_tycx_ajjbxx.ajid = s1.ajid)
-> Parallel Hash Left Join (cost=246203.89..1052647.00 rows=115982 width=55) (actual time=1159.023..3639.497 rows=98214 loops=8)
Hash Cond: (v_tycx_ajjbxx.ajid = sp_ajxx_zx.ajid)
Filter: ((v_tycx_ajjbxx.ajxz_tj = 2) OR ((v_tycx_ajjbxx.ajlbbh = ANY ('{601,2140}'::integer[])) AND (sp_ajxx_zx.ajid IS NOT NULL)))
Rows Removed by Filter: 2353
-> Parallel Hash Left Join (cost=136345.41..942350.26 rows=166957 width=59) (actual time=786.570..3197.875 rows=100568 loops=8)
Hash Cond: (v_tycx_ajjbxx.ajid = k.ajid)
Filter: (((v_tycx_ajjbxx.larq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.larq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR ((v_tycx_ajjbxx.sarq >= '2022-01-01 00:00:00'::timestamp without time zone) AND (v_tycx_ajjbxx.sarq <= '2022-12-31 00:00:00'::timestamp without time zone)) OR (((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((k.ktjsrq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone)))
Rows Removed by Filter: 868288
-> Parallel Seq Scan on v_tycx_ajjbxx (cost=0.00..800060.10 rows=884565 width=39) (actual time=0.136..1449.936 rows=926186 loops=8)
Filter: ((ajxz_tj = 2) OR (ajlbbh = ANY ('{601,2140}'::integer[])))
Rows Removed by Filter: 154192
-> Parallel Hash (cost=126889.21..126889.21 rows=756496 width=32) (actual time=781.109..781.110 rows=468075 loops=8)
Buckets: 4194304 Batches: 1 Memory Usage: 269792kB
-> Parallel Seq Scan on sp_ktxx k (cost=0.00..126889.21 rows=756496 width=32) (actual time=0.032..486.548 rows=468075 loops=8)
Filter: ((ktjg <> ALL ('{4,6}'::numeric[])) AND ((sfts)::text = '1'::text))
Rows Removed by Filter: 295743
-> Parallel Hash (cost=104210.63..104210.63 rows=451828 width=12) (actual time=368.056..368.083 rows=277503 loops=8)
Buckets: 4194304 Batches: 1 Memory Usage: 137056kB
-> Parallel Seq Scan on sp_ajxx_zx (cost=0.00..104210.63 rows=451828 width=12) (actual time=0.065..214.362 rows=277503 loops=8)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 67197
-> Parallel Hash (cost=105959.04..105959.04 rows=862350 width=12) (actual time=518.743..518.743 rows=433631 loops=8)
Buckets: 4194304 Batches: 1 Memory Usage: 195648kB
-> Parallel Seq Scan on tab_satj s1 (cost=0.00..105959.04 rows=862350 width=12) (actual time=0.033..283.397 rows=433631 loops=8)
Filter: ((satj)::text = ANY ('{xsla,ysla,zzla,zzp,xsgs,jxjs,xfla,qtwsla,dyjf}'::text[]))
Rows Removed by Filter: 143599
-> Finalize GroupAggregate (cost=291650.19..291673.30 rows=24 width=15) (actual time=4643.577..4998.035 rows=24 loops=1)
Group Key: s.sqfy
-> Gather Merge (cost=291650.19..291672.70 rows=72 width=15) (actual time=4641.771..4997.763 rows=96 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Partial GroupAggregate (cost=290650.15..290664.20 rows=24 width=15) (actual time=4621.637..4909.049 rows=24 loops=4)
Group Key: s.sqfy
-> Sort (cost=290650.15..290651.68 rows=614 width=15) (actual time=4621.508..4648.267 rows=215125 loops=4)
Sort Key: s.sqfy
Sort Method: quicksort Memory: 16628kB
Worker 0: Sort Method: quicksort Memory: 16672kB
Worker 1: Sort Method: quicksort Memory: 16648kB
Worker 2: Sort Method: quicksort Memory: 14966kB
-> Parallel Hash Left Join (cost=223116.63..290621.71 rows=614 width=15) (actual time=3767.216..4516.120 rows=215125 loops=4)
Hash Cond: (a.sqbh = "*SELECT* 2".sqbh)
Filter: ((CASE WHEN ((CASE WHEN ("*SELECT* 2".sqbh IS NOT NULL) THEN "*SELECT* 2".zt ELSE NULL::character varying END)::character varying(10 char) IS NOT NULL) THEN (CASE WHEN ("*SELECT* 2".sqbh IS NOT NULL) THEN "*SELECT* 2".zt ELSE NULL::character varying END)::character varying(10 char) ELSE a.zt END)::text <> 'SQ000'::text)
-> Parallel Hash Left Join (cost=130707.41..186454.56 rows=617 width=27) (actual time=2229.923..2779.827 rows=215125 loops=4)
Hash Cond: (a.sqbh = "*SELECT* 2_1".sqbh)
Filter: (CASE WHEN ("*SELECT* 2_1".sqbh IS NOT NULL) THEN "*SELECT* 2_1".czsj ELSE NULL::timestamp without time zone END >= '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 7018
-> Parallel Hash Join (cost=37284.16..58603.03 rows=1850 width=27) (actual time=664.280..1021.562 rows=222143 loops=4)
Hash Cond: (a.sqbh = s.id)
-> Parallel Seq Scan on fy_wsla_wwzt a (cost=0.00..19362.86 rows=520012 width=12) (actual time=0.036..105.807 rows=395004 loops=4)
Filter: ((czsj > '2018-01-01 00:00:00'::timestamp without time zone) AND (czsj >= '2018-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 8652
-> Parallel Hash (cost=37260.51..37260.51 rows=1892 width=22) (actual time=664.091..664.092 rows=222144 loops=4)
Buckets: 1048576 (originally 8192) Batches: 1 (originally 1) Memory Usage: 65056kB
-> Parallel Seq Scan on fy_wsla_sqjbxx s (cost=0.00..37260.51 rows=1892 width=22) (actual time=0.088..486.961 rows=222144 loops=4)
Filter: (((lsid IS NOT NULL) OR (userid IS NOT NULL)) AND (ajlb = ANY ('{2,4,5}'::numeric[])) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((sqsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 159736
-> Parallel Hash (cost=91562.65..91562.65 rows=148848 width=14) (actual time=1564.435..1564.454 rows=345508 loops=4)
Buckets: 2097152 (originally 524288) Batches: 1 (originally 1) Memory Usage: 93664kB
-> Parallel Hash Join (cost=23349.79..91562.65 rows=148848 width=14) (actual time=295.795..882.610 rows=345508 loops=4)
Hash Cond: ("*SELECT* 2_1".sqbh = b_1.sqbh)
Join Filter: ("*SELECT* 2_1".czsj > b_1.czsj)
Rows Removed by Join Filter: 1628
-> Parallel Append (cost=0.00..67040.68 rows=446544 width=14) (actual time=1.085..320.473 rows=347317 loops=4)
-> Subquery Scan on "*SELECT* 2_1" (cost=4.22..48388.28 rows=1 width=14) (actual time=4.308..798.636 rows=187 loops=1)
-> Merge Anti Join (cost=4.22..48388.27 rows=1 width=52) (actual time=4.307..798.608 rows=187 loops=1)
Merge Cond: (fy_wsla_dfbnwzt.sqbh = fy_wsla_nwzt_2.sqbh)
-> Index Scan using ind_xdata_fy_wsla_dfbnwzt_sqbh on fy_wsla_dfbnwzt (cost=0.28..32.27 rows=187 width=14) (actual time=0.089..0.212 rows=188 loops=1)
Filter: (czsj > '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 326
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_2 (cost=0.43..45297.84 rows=1417489 width=6) (actual time=0.065..535.717 rows=1419464 loops=1)
Heap Fetches: 536856
-> Parallel Seq Scan on fy_wsla_nwzt fy_wsla_nwzt_1 (cost=0.00..16419.68 rows=446543 width=14) (actual time=0.008..96.186 rows=347270 loops=4)
Filter: (czsj > '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 7613
-> Parallel Hash (cost=16704.91..16704.91 rows=531591 width=14) (actual time=290.337..290.338 rows=403657 loops=4)
Buckets: 2097152 Batches: 1 Memory Usage: 92224kB
-> Parallel Seq Scan on fy_wsla_wwzt b_1 (cost=0.00..16704.91 rows=531591 width=14) (actual time=0.009..89.514 rows=403657 loops=4)
-> Parallel Hash (cost=90504.00..90504.00 rows=152418 width=12) (actual time=1536.076..1536.079 rows=353171 loops=4)
Buckets: 2097152 (originally 524288) Batches: 1 (originally 1) Memory Usage: 95008kB
-> Parallel Hash Join (cost=23349.79..90504.00 rows=152418 width=12) (actual time=297.852..873.426 rows=353171 loops=4)
Hash Cond: ("*SELECT* 2".sqbh = b.sqbh)
Join Filter: ("*SELECT* 2".czsj > b.czsj)
Rows Removed by Join Filter: 1635
-> Parallel Append (cost=0.00..65953.91 rows=457256 width=20) (actual time=0.066..305.195 rows=355009 loops=4)
-> Subquery Scan on "*SELECT* 2" (cost=4.22..48391.08 rows=1 width=20) (actual time=0.231..772.400 rows=504 loops=1)
-> Merge Anti Join (cost=4.22..48391.07 rows=1 width=20) (actual time=0.230..772.336 rows=504 loops=1)
Merge Cond: (fy_wsla_dfbnwzt_1.sqbh = fy_wsla_nwzt_3.sqbh)
-> Index Scan using ind_xdata_fy_wsla_dfbnwzt_sqbh on fy_wsla_dfbnwzt fy_wsla_dfbnwzt_1 (cost=0.28..30.98 rows=514 width=20) (actual time=0.036..0.227 rows=514 loops=1)
-> Index Only Scan using fy_wsla_nwzt_constraint_1 on fy_wsla_nwzt fy_wsla_nwzt_3 (cost=0.43..45297.84 rows=1417489 width=6) (actual time=0.060..507.841 rows=1419464 loops=1)
Heap Fetches: 536994
-> Parallel Seq Scan on fy_wsla_nwzt (cost=0.00..15276.55 rows=457255 width=20) (actual time=0.010..87.239 rows=354883 loops=4)
-> Parallel Hash (cost=16704.91..16704.91 rows=531591 width=14) (actual time=293.412..293.412 rows=403657 loops=4)
Buckets: 2097152 Batches: 1 Memory Usage: 92192kB
-> Parallel Seq Scan on fy_wsla_wwzt b (cost=0.00..16704.91 rows=531591 width=14) (actual time=0.035..95.080 rows=403657 loops=4)
-> Finalize GroupAggregate (cost=1433095.21..1433139.10 rows=29 width=15) (actual time=5329.452..5428.307 rows=24 loops=1)
Group Key: v_tycx_ajjbxx_1.fybm
-> Gather Merge (cost=1433095.21..1433138.08 rows=145 width=15) (actual time=5316.009..5428.138 rows=144 loops=1)
Workers Planned: 5
Workers Launched: 5
-> Partial GroupAggregate (cost=1432095.14..1432120.54 rows=29 width=15) (actual time=5292.532..5377.336 rows=24 loops=6)
Group Key: v_tycx_ajjbxx_1.fybm
-> Sort (cost=1432095.14..1432103.51 rows=3349 width=7) (actual time=5291.952..5320.656 rows=314805 loops=6)
Sort Key: v_tycx_ajjbxx_1.fybm
Sort Method: quicksort Memory: 26509kB
Worker 0: Sort Method: quicksort Memory: 25366kB
Worker 1: Sort Method: quicksort Memory: 24620kB
Worker 2: Sort Method: quicksort Memory: 27703kB
Worker 3: Sort Method: quicksort Memory: 30337kB
Worker 4: Sort Method: quicksort Memory: 27734kB
-> Parallel Hash Join (cost=924722.71..1431899.06 rows=3349 width=7) (actual time=4802.771..5179.049 rows=314805 loops=6)
Hash Cond: (("*SELECT* 2_2".ah)::text = (v_tycx_ajjbxx_1.ah)::text)
-> Parallel Append (cost=0.00..502269.64 rows=5792 width=31) (actual time=40.589..1273.508 rows=345752 loops=6)
-> Subquery Scan on "*SELECT* 2_2" (cost=16282.89..335185.97 rows=11 width=31) (actual time=81.038..436.255 rows=63540 loops=3)
-> Parallel Bitmap Heap Scan on mv_sdxxfs_tysdpt a_1 (cost=16282.89..335185.86 rows=2 width=97) (actual time=81.036..429.156 rows=63540 loops=3)
Recheck Cond: ((fydm)::text = '210000'::text)
Filter: (((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 84781
-> Bitmap Index Scan on ind_xdata_mv_sdxxfs_tysdpt_fydm_ah (cost=0.00..16282.89 rows=445110 width=0) (actual time=73.913..73.914 rows=444961 loops=1)
Index Cond: ((fydm)::text = '210000'::text)
-> Parallel Seq Scan on t_spgl_ems_txm t (cost=0.00..167054.71 rows=6937 width=31) (actual time=0.200..1028.182 rows=313983 loops=6)
Filter: (((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((cjsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 842804
-> Parallel Hash (cost=912240.57..912240.57 rows=614491 width=38) (actual time=3348.110..3348.261 rows=1170302 loops=6)
Buckets: 524288 Batches: 16 Memory Usage: 35648kB
-> Parallel Hash Left Join (cost=109858.48..912240.57 rows=614491 width=38) (actual time=468.864..3003.056 rows=1170302 loops=6)
Hash Cond: (v_tycx_ajjbxx_1.ajid = sp_ajxx_zx_1.ajid)
Filter: ((v_tycx_ajjbxx_1.ajxz_tj = 2) OR ((v_tycx_ajjbxx_1.ajlbbh = ANY ('{601,2140}'::integer[])) AND (sp_ajxx_zx_1.ajid IS NOT NULL)))
Rows Removed by Filter: 64612
-> Parallel Seq Scan on v_tycx_ajjbxx v_tycx_ajjbxx_1 (cost=0.00..800060.10 rows=884565 width=54) (actual time=0.028..1712.030 rows=1234914 loops=6)
Filter: ((ajxz_tj = 2) OR (ajlbbh = ANY ('{601,2140}'::integer[])))
Rows Removed by Filter: 205589
-> Parallel Hash (cost=104210.63..104210.63 rows=451828 width=12) (actual time=462.886..462.900 rows=370004 loops=6)
Buckets: 4194304 Batches: 1 Memory Usage: 137056kB
-> Parallel Seq Scan on sp_ajxx_zx sp_ajxx_zx_1 (cost=0.00..104210.63 rows=451828 width=12) (actual time=0.032..262.182 rows=370004 loops=6)
Filter: (zxajxz = ANY ('{4,5,6}'::integer[]))
Rows Removed by Filter: 89596
-> GroupAggregate (cost=1301622.23..1301625.87 rows=24 width=31) (actual time=17656.617..165746.497 rows=24 loops=1)
Group Key: a_2.fydm
-> Sort (cost=1301622.23..1301622.72 rows=194 width=40) (actual time=16728.572..18285.295 rows=6767633 loops=1)
Sort Key: a_2.fydm
Sort Method: external merge Disk: 330936kB
-> Gather (cost=494900.94..1301614.86 rows=194 width=40) (actual time=10058.705..13124.395 rows=6767633 loops=1)
Workers Planned: 7
Workers Launched: 3
-> Parallel Hash Left Join (cost=493900.94..1300595.46 rows=28 width=40) (actual time=10028.906..12223.267 rows=1691908 loops=4)
Hash Cond: (v_tycx_ajjbxx_2.ajid = z.ajid)
Filter: ((v_tycx_ajjbxx_2.ajxz_tj = 2) OR ((v_tycx_ajjbxx_2.ajlbbh = ANY ('{601,2140}'::integer[])) AND (z.zxajxz = ANY ('{4,5,6}'::integer[]))))
Rows Removed by Filter: 18905
-> Parallel Hash Join (cost=384812.19..1191506.60 rows=41 width=56) (actual time=9268.458..10869.614 rows=1710813 loops=4)
Hash Cond: ((v_tycx_ajjbxx_2.ah)::text = (a_2.ah)::text)
-> Parallel Seq Scan on v_tycx_ajjbxx v_tycx_ajjbxx_2 (cost=0.00..800060.10 rows=884565 width=47) (actual time=0.021..2400.001 rows=1852372 loops=4)
Filter: ((ajxz_tj = 2) OR (ajlbbh = ANY ('{601,2140}'::integer[])))
Rows Removed by Filter: 308383
-> Parallel Hash (cost=384811.33..384811.33 rows=69 width=40) (actual time=6257.396..6257.397 rows=1816753 loops=4)
Buckets: 524288 (originally 1024) Batches: 16 (originally 1) Memory Usage: 38528kB
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a_2 (cost=0.00..384811.33 rows=69 width=40) (actual time=0.130..5054.854 rows=1816753 loops=4)
Filter: (((sdfs)::integer = 2) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-02 00:00:00'::timestamp without time zone) AND ((trunc((fssdsj)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 2340563
-> Parallel Hash (cost=102120.00..102120.00 rows=557500 width=14) (actual time=751.445..751.447 rows=689399 loops=4)
Buckets: 4194304 Batches: 1 Memory Usage: 162176kB
-> Parallel Seq Scan on sp_ajxx_zx z (cost=0.00..102120.00 rows=557500 width=14) (actual time=0.040..366.164 rows=689399 loops=4)
-> GroupAggregate (cost=2446311.15..2446311.17 rows=1 width=15) (actual time=31923.453..79712.271 rows=24 loops=1)
Group Key: t_1.fydm
-> Sort (cost=2446311.15..2446311.16 rows=1 width=38) (actual time=31539.179..32123.775 rows=2481088 loops=1)
Sort Key: t_1.fydm
Sort Method: external merge Disk: 116184kB
-> Hash Join (cost=1628893.79..2446311.14 rows=1 width=38) (actual time=27345.700..30290.382 rows=2481088 loops=1)
Hash Cond: ((v_tycx_ajjbxx_3.ah)::text = (t_1.ah)::text)
Join Filter: ((v_tycx_ajjbxx_4.ajxz_tj = 2) OR ((v_tycx_ajjbxx_3.ajlbbh = ANY ('{601,2140}'::integer[])) AND (z_1.zxajxz = ANY ('{4,5,6}'::integer[]))))
Rows Removed by Join Filter: 145278
-> Gather (cost=1000.00..818250.21 rows=44567 width=33) (actual time=1.076..975.941 rows=847169 loops=1)
Workers Planned: 7
Workers Launched: 7
-> Parallel Seq Scan on v_tycx_ajjbxx v_tycx_ajjbxx_3 (cost=0.00..812793.51 rows=6367 width=33) (actual time=0.125..1181.178 rows=105896 loops=8)
Filter: (((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone >= '2022-01-01 00:00:00'::timestamp without time zone) AND ((trunc((jarq)::timestamp with time zone, 'DDD'::text))::timestamp without time zone <= '2022-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 974481
-> Hash (cost=1627893.78..1627893.78 rows=1 width=73) (actual time=27344.212..27398.208 rows=4018979 loops=1)
Buckets: 524288 (originally 1024) Batches: 16 (originally 1) Memory Usage: 36865kB
-> Gather (cost=1274336.32..1627893.78 rows=1 width=73) (actual time=21914.592..26218.699 rows=4018979 loops=1)
Workers Planned: 6
Workers Launched: 1
-> Parallel Hash Left Join (cost=1273336.32..1626893.68 rows=1 width=73) (actual time=21951.552..24638.323 rows=2009490 loops=2)
Hash Cond: (v_tycx_ajjbxx_4.ajid = z_1.ajid)
-> Parallel Hash Left Join (cost=1164247.57..1517804.92 rows=1 width=83) (actual time=20528.313..22481.794 rows=2009490 loops=2)
Hash Cond: ((t_1.ah)::text = (v_tycx_ajjbxx_4.ah)::text)
-> Parallel Hash Join (cost=343445.42..685808.75 rows=1 width=69) (actual time=11750.627..14269.762 rows=2009490 loops=2)
Hash Cond: (((t_1.ah)::text = (a_3.ah)::text) AND (t_1.fsxxid = a_3.fsxxid))
-> Parallel Seq Scan on mv_sdxxws_tysdpt t_1 (cost=0.00..339116.37 rows=216464 width=44) (actual time=0.205..3178.108 rows=683044 loops=2)
Filter: (((sshdtz)::text ~~ '%判决书%'::text) OR ((sshdtz)::text ~~ '%裁定书%'::text) OR ((sshdtz)::text ~~ '%调解书%'::text))
Rows Removed by Filter: 4871648
-> Parallel Hash (cost=343238.16..343238.16 rows=13817 width=37) (actual time=8228.930..8228.931 rows=6236342 loops=2)
Buckets: 524288 (originally 131072) Batches: 32 (originally 1) Memory Usage: 31776kB
-> Parallel Seq Scan on mv_sdxxfs_tysdpt a_3 (cost=0.00..343238.16 rows=13817 width=37) (actual time=0.106..4533.260 rows=6236342 loops=2)
Filter: ((ah IS NOT NULL) AND ((sdfs)::integer = 2))
Rows Removed by Filter: 2078291
-> Parallel Hash (cost=793693.40..793693.40 rows=1273340 width=45) (actual time=5652.219..5652.220 rows=4321510 loops=2)
Buckets: 524288 Batches: 32 Memory Usage: 25632kB
-> Parallel Seq Scan on v_tycx_ajjbxx v_tycx_ajjbxx_4 (cost=0.00..793693.40 rows=1273340 width=45) (actual time=0.017..4367.066 rows=4321510 loops=2)
-> Parallel Hash (cost=102120.00..102120.00 rows=557500 width=14) (actual time=1404.374..1404.375 rows=1378798 loops=2)
Buckets: 4194304 Batches: 1 Memory Usage: 162176kB
-> Parallel Seq Scan on sp_ajxx_zx z_1 (cost=0.00..102120.00 rows=557500 width=14) (actual time=0.023..718.539 rows=1378798 loops=2)
Planning Time: 13.961 ms
Execution Time: 266227.073 ms
(230 rows)
可以看到经过哥的不懈努力以后，
t1表从 原来 54 s 优化至 10s
t2表从 原来 6分钟 优化至 4s
t3表从 原来 101s 优化至 4s
t4表从 原来 9.5分钟 优化至 2.33分钟
t5表从 原来 2.5 分钟 优化至 1.3分钟
整体SQL 原来2小时跑不出结果现在 4.4 分钟能跑出结果。
就是优化的过程太缓慢，效率太差了，各种刻录数据，谁叫这个是内网项目呢。
如果我在项目现场，这条SQL应该还能继续优化到2分钟左右，毕竟还有大招还没放，嘿嘿。
标签：11,rows,..,SQL,改写,width,cost,time,loops From： https://www.cnblogs.com/yuzhijian/p/17223780.html