It did not seem to help.
See attachment.
Regards,
Virendra
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx]
Sent: Tuesday, January 09, 2018 6:00 PM
To: Kumar, Virendra
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Performance of a Query
On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra <Virendra.Kumar@xxxxxxxxxxx> wrote:
> Thank you Scott!
> I have current work_mem set as 4MB, shared_buffers to 8GB, hugepages on.
> I gradually increased the work_mem to 1GB but it did not help a bit. Am I missing something obvious.
> From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx]
> Sent: Tuesday, January 09, 2018 5:08 PM
> To: Kumar, Virendra
> Cc: pgsql-performance@xxxxxxxxxxxxxx
> Subject: Re: Performance of a Query
Try it with something reasonable like 64MB and then post your query plans to explain.depesz and then here and let's compare. Note that some queries are just slow, and this one is handling a lot of data, so there's only so much to do if an index won't fix it.
________________________________
This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.
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=#
