ap_poc_db=# show work_mem; work_mem ---------- 64MB (1 row) Time: 0.285 ms ap_poc_db=# explain (analyze,buffers) ap_poc_db-# SELECT SUM ((((se_cov1val + se_cov2val) + se_cov3val) + se_cov4val)) ap_poc_db-# AS "10", ap_poc_db-# SUM (se_site_limit) ap_poc_db-# AS "11", ap_poc_db-# SUM (se_cov1val) ap_poc_db-# AS "12", ap_poc_db-# SUM (se_cov2val) ap_poc_db-# AS "13", ap_poc_db-# SUM (se_cov3val) ap_poc_db-# AS "14", ap_poc_db-# SUM (se_cov4val) ap_poc_db-# AS "15", ap_poc_db-# SUM (se_site_deduct) ap_poc_db-# AS "17", ap_poc_db-# SUM (se_risk_count) ap_poc_db-# AS "21", ap_poc_db-# SUM (se_cov1limit) ap_poc_db-# AS "143", ap_poc_db-# SUM (se_cov1deduct) ap_poc_db-# AS "144", ap_poc_db-# SUM (se_cov2limit) ap_poc_db-# AS "145", ap_poc_db-# SUM (se_cov3limit) ap_poc_db-# AS "147", ap_poc_db-# SUM (se_cov3deduct) ap_poc_db-# AS "148", ap_poc_db-# SUM (se_cov4limit) ap_poc_db-# AS "149", ap_poc_db-# SUM (se_cov4deduct) ap_poc_db-# AS "150", ap_poc_db-# SUM (se_site_bl_deduct) ap_poc_db-# AS "212", ap_poc_db-# SUM (se_agg_limit) ap_poc_db-# AS "213", ap_poc_db-# SUM (se_site_bl_limit) ap_poc_db-# AS "211", ap_poc_db-# SUM (pe_premium) ap_poc_db-# AS "93", ap_poc_db-# SUM (pe_policy_deduct) ap_poc_db-# AS "92", ap_poc_db-# SUM (pe_undercover) ap_poc_db-# AS "127", ap_poc_db-# SUM (pe_prorata) ap_poc_db-# AS "126", ap_poc_db-# SUM (pe_policy_bl_deduct) ap_poc_db-# AS "139", ap_poc_db-# SUM (pe_policy_bl_grosslimit) ap_poc_db-# AS "142", ap_poc_db-# SUM (pe_policy_limit) ap_poc_db-# AS "128", ap_poc_db-# SUM (pe_agg_deduct) ap_poc_db-# AS "155" ap_poc_db-# FROM (SELECT SUM (se.site_limit) AS se_site_limit, ap_poc_db(# SUM (se.cov1val) AS se_cov1val, ap_poc_db(# SUM (se.cov2val) AS se_cov2val, ap_poc_db(# SUM (se.cov3val) AS se_cov3val, ap_poc_db(# SUM (se.cov4val) AS se_cov4val, ap_poc_db(# SUM (se.site_deduct) AS se_site_deduct, ap_poc_db(# SUM (se.risk_count) AS se_risk_count, ap_poc_db(# SUM (se.cov1limit) AS se_cov1limit, ap_poc_db(# SUM (se.cov1deduct) AS se_cov1deduct, ap_poc_db(# SUM (se.cov2limit) AS se_cov2limit, ap_poc_db(# SUM (se.cov3limit) AS se_cov3limit, ap_poc_db(# SUM (se.cov3deduct) AS se_cov3deduct, ap_poc_db(# SUM (se.cov4limit) AS se_cov4limit, ap_poc_db(# SUM (se.cov4deduct) AS se_cov4deduct, ap_poc_db(# SUM (se.site_bl_deduct) AS se_site_bl_deduct, ap_poc_db(# SUM (se.agg_limit) AS se_agg_limit, ap_poc_db(# SUM (se.site_bl_limit) AS se_site_bl_limit, ap_poc_db(# se.peril_id AS se_peril_id, ap_poc_db(# se.portfolio_id AS se_portfolio_id, ap_poc_db(# se.account_id AS se_account_id ap_poc_db(# FROM ap.site_exposure se ap_poc_db(# WHERE se.portfolio_id = -1192662 ap_poc_db(# GROUP BY se.peril_id,se.portfolio_id,se.account_id ap_poc_db(# ) s ap_poc_db-# INNER JOIN ap_poc_db-# ( ap_poc_db(# SELECT SUM(pe.premium) AS pe_premium,SUM(pe.policy_deduct) AS pe_policy_deduct,SUM(pe.undercover) AS pe_undercover,SUM(pe.prorata) AS pe_prorata,SUM(pe.policy_bl_deduct) AS pe_policy_bl_deduct,SUM(pe.policy_bl_grosslimit) AS pe_policy_bl_grosslimit,SUM(pe.policy_limit) AS pe_policy_limit,SUM(pe.agg_deduct) AS pe_agg_deduct,pe.peril_id AS pe_peril_id,pe.portfolio_id AS pe_portfolio_id,pe.account_id AS pe_account_id ap_poc_db(# FROM ap.policy_exposure pe ap_poc_db(# WHERE pe.portfolio_id = -1192662 ap_poc_db(# GROUP BY pe.peril_id,pe.portfolio_id,pe.account_id ap_poc_db(# ) p ap_poc_db-# ON (s.se_peril_id=p.pe_peril_id AND s.se_account_id=p.pe_account_id AND s.se_portfolio_id=p.pe_portfolio_id) ap_poc_db-# WHERE s.se_portfolio_id= -1192662 ap_poc_db-# ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------- Aggregate (cost=4726102.31..4726102.32 rows=1 width=232) (actual time=54328.936..54328.937 rows=1 loops=1) Buffers: shared hit=3752963 read=59417, temp read=11847 written=11849 -> Merge Join (cost=440038.79..2204278.11 rows=34783782 width=200) (actual time=3127.807..49736.792 rows=3704652 loops=1) Merge Cond: ((se.peril_id = pe.peril_id) AND (se.account_id = pe.account_id)) Buffers: shared hit=3752963 read=59417, temp read=11847 written=11849 -> Finalize GroupAggregate (cost=440038.35..902756.46 rows=407064 width=152) (actual time=3127.713..17665.926 rows=3704652 loops=1) Group Key: se.peril_id, se.portfolio_id, se.account_id Buffers: shared hit=63 read=45004, temp read=11847 written=11849 -> Gather Merge (cost=440038.35..776566.62 rows=2442384 width=152) (actual time=3127.700..10304.074 rows=3730994 loops=1) Workers Planned: 6 Workers Launched: 6 Buffers: shared hit=63 read=45004, temp read=11847 written=11849 -> Partial GroupAggregate (cost=439038.25..478727.04 rows=407064 width=152) (actual time=3096.245..4818.033 rows=532999 loops=7) Group Key: se.peril_id, se.portfolio_id, se.account_id Buffers: shared hit=644 read=306628, temp read=80671 written=80685 -> Sort (cost=439038.25..440734.35 rows=678441 width=148) (actual time=3096.215..3562.408 rows=581538 loops=7) Sort Key: se.peril_id, se.account_id Sort Method: external merge Disk: 94776kB Buffers: shared hit=644 read=306628, temp read=80671 written=80685 -> Result (cost=0.00..322308.92 rows=678441 width=148) (actual time=0.036..2168.563 rows=581538 loops=7) Buffers: shared hit=416 read=306628 -> Append (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.033..1501.786 rows=581538 loops=7) Buffers: shared hit=416 read=306628 -> Parallel Seq Scan on site_exposure_1192662 se (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.031..878.266 rows=581538 loops=7) Filter: (portfolio_id = '-1192662'::integer) Buffers: shared hit=416 read=306628 -> Materialize (cost=0.44..695244.55 rows=3418016 width=80) (actual time=0.086..24680.513 rows=3704652 loops=1) Buffers: shared hit=3752900 read=14413 -> GroupAggregate (cost=0.44..652519.35 rows=3418016 width=80) (actual time=0.082..20480.530 rows=3704652 loops=1) Group Key: pe.peril_id, pe.portfolio_id, pe.account_id Buffers: shared hit=3752900 read=14413 -> Result (cost=0.44..514907.67 rows=3761146 width=80) (actual time=0.070..14034.587 rows=3761146 loops=1) Buffers: shared hit=3752900 read=14413 -> Merge Append (cost=0.44..477296.21 rows=3761146 width=80) (actual time=0.068..10125.794 rows=3761146 loops=1) Sort Key: pe.peril_id, pe.account_id Buffers: shared hit=3752900 read=14413 -> Index Scan using policy_exposure_1192662_portfolio_id_peril_id_account_id_idx on policy_exposure_1192662 pe (cost=0.43..449087.61 rows=3761146 width=80) (actual time=0.066..6505.959 rows=3761146 loops=1) Index Cond: (portfolio_id = '-1192662'::integer) Buffers: shared hit=3752900 read=14413 Planning time: 2.642 ms Execution time: 54345.845 ms (41 rows) Time: 54351.557 ms (00:54.352) ap_poc_db=#