pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of a Query
8+ messages / 4 participants
[nested] [flat]
* Performance of a Query
@ 2018-01-09 21:18 Kumar, Virendra <Virendra.Kumar@guycarp.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kumar, Virendra @ 2018-01-09 21:18 UTC (permalink / raw)
To: pgsql-performance
Hello Gurus,
I am struggling to tune a query which is doing join on top of aggregate for around 3 million rows. The plan and SQL is attached to the email.
Below is system Details:
PGSQL version - 10.1
OS - RHEL 3.10.0-693.5.2.el7.x86_64
Binary - Dowloaded from postgres.org compiled and installed.
Hardware - Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem.
Please let me know if you need more information.
Regards,
Virendra
________________________________
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.
------------------------------------------------------------------------------------
Aggregate (cost=4794108.63..4794108.64 rows=1 width=232) (actual time=54451.640..54451.641 rows=1 loops=1)
Buffers: shared hit=3771629 read=46189, temp read=20532 written=20551
-> Merge Join (cost=491054.79..2272373.76 rows=34782550 width=200) (actual time=3278.909..49768.897 rows=3704652 loops=1)
Merge Cond: ((se.peril_id = pe.peril_id) AND (se.account_id = pe.account_id))
Buffers: shared hit=3771629 read=46189, temp read=20532 written=20551
-> Finalize GroupAggregate (cost=491054.35..953772.46 rows=407064 width=152) (actual time=3278.847..17842.707 rows=3704652 loops=1)
Group Key: se.peril_id, se.portfolio_id, se.account_id
Buffers: shared hit=66 read=46189, temp read=20532 written=20551
-> Gather Merge (cost=491054.35..827582.62 rows=2442384 width=152) (actual time=3278.833..10355.885 rows=3731100 loops=1)
Workers Planned: 6
Workers Launched: 6
Buffers: shared hit=66 read=46189, temp read=20532 written=20551
-> Partial GroupAggregate (cost=490054.25..529743.04 rows=407064 width=152) (actual time=3264.819..4961.425 rows=533014 loops=7)
Group Key: se.peril_id, se.portfolio_id, se.account_id
Buffers: shared hit=676 read=306596, temp read=135840 written=135972
-> Sort (cost=490054.25..491750.35 rows=678441 width=148) (actual time=3264.796..3731.927 rows=581538 loops=7)
Sort Key: se.peril_id, se.account_id
Sort Method: external merge Disk: 97240kB
Buffers: shared hit=676 read=306596, temp read=135840 written=135972
-> Result (cost=0.00..322308.92 rows=678441 width=148) (actual time=0.036..2205.533 rows=581538 loops=7)
Buffers: shared hit=448 read=306596
-> Append (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.033..1523.998 rows=581538 loops=7)
Buffers: shared hit=448 read=306596
-> Parallel Seq Scan on site_exposure_1192662 se (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.030..896.668 rows=581538 loops=7)
Filter: (portfolio_id = '-1192662'::integer)
Buffers: shared hit=448 read=306596
-> Materialize (cost=0.44..712345.45 rows=3417895 width=80) (actual time=0.055..24504.782 rows=3704652 loops=1)
Buffers: shared hit=3771563
-> GroupAggregate (cost=0.44..669621.76 rows=3417895 width=80) (actual time=0.051..20359.291 rows=3704652 loops=1)
Group Key: pe.peril_id, pe.portfolio_id, pe.account_id
Buffers: shared hit=3771563
-> Result (cost=0.44..532014.98 rows=3761012 width=80) (actual time=0.040..13908.465 rows=3761146 loops=1)
Buffers: shared hit=3771563
-> Merge Append (cost=0.44..494404.86 rows=3761012 width=80) (actual time=0.038..10094.668 rows=3761146 loops=1)
Sort Key: pe.peril_id, pe.account_id
Buffers: shared hit=3771563
-> Index Scan using policy_exposure_1192662_portfolio_id_peril_id_account_id_idx on policy_exposure_1192662 pe (cost=0.43..466197.26 rows=3761012 width=80) (actual time=0.036..6448.117 rows=3761146 loops=1)
Index Cond: (portfolio_id = '-1192662'::integer)
Buffers: shared hit=3771563
Attachments:
AggQuery_Plan.txt (text/plain, 3.9K) download
------------------------------------------------------------------------------------
Aggregate (cost=4794108.63..4794108.64 rows=1 width=232) (actual time=54451.640..54451.641 rows=1 loops=1)
Buffers: shared hit=3771629 read=46189, temp read=20532 written=20551
-> Merge Join (cost=491054.79..2272373.76 rows=34782550 width=200) (actual time=3278.909..49768.897 rows=3704652 loops=1)
Merge Cond: ((se.peril_id = pe.peril_id) AND (se.account_id = pe.account_id))
Buffers: shared hit=3771629 read=46189, temp read=20532 written=20551
-> Finalize GroupAggregate (cost=491054.35..953772.46 rows=407064 width=152) (actual time=3278.847..17842.707 rows=3704652 loops=1)
Group Key: se.peril_id, se.portfolio_id, se.account_id
Buffers: shared hit=66 read=46189, temp read=20532 written=20551
-> Gather Merge (cost=491054.35..827582.62 rows=2442384 width=152) (actual time=3278.833..10355.885 rows=3731100 loops=1)
Workers Planned: 6
Workers Launched: 6
Buffers: shared hit=66 read=46189, temp read=20532 written=20551
-> Partial GroupAggregate (cost=490054.25..529743.04 rows=407064 width=152) (actual time=3264.819..4961.425 rows=533014 loops=7)
Group Key: se.peril_id, se.portfolio_id, se.account_id
Buffers: shared hit=676 read=306596, temp read=135840 written=135972
-> Sort (cost=490054.25..491750.35 rows=678441 width=148) (actual time=3264.796..3731.927 rows=581538 loops=7)
Sort Key: se.peril_id, se.account_id
Sort Method: external merge Disk: 97240kB
Buffers: shared hit=676 read=306596, temp read=135840 written=135972
-> Result (cost=0.00..322308.92 rows=678441 width=148) (actual time=0.036..2205.533 rows=581538 loops=7)
Buffers: shared hit=448 read=306596
-> Append (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.033..1523.998 rows=581538 loops=7)
Buffers: shared hit=448 read=306596
-> Parallel Seq Scan on site_exposure_1192662 se (cost=0.00..315524.51 rows=678441 width=148) (actual time=0.030..896.668 rows=581538 loops=7)
Filter: (portfolio_id = '-1192662'::integer)
Buffers: shared hit=448 read=306596
-> Materialize (cost=0.44..712345.45 rows=3417895 width=80) (actual time=0.055..24504.782 rows=3704652 loops=1)
Buffers: shared hit=3771563
-> GroupAggregate (cost=0.44..669621.76 rows=3417895 width=80) (actual time=0.051..20359.291 rows=3704652 loops=1)
Group Key: pe.peril_id, pe.portfolio_id, pe.account_id
Buffers: shared hit=3771563
-> Result (cost=0.44..532014.98 rows=3761012 width=80) (actual time=0.040..13908.465 rows=3761146 loops=1)
Buffers: shared hit=3771563
-> Merge Append (cost=0.44..494404.86 rows=3761012 width=80) (actual time=0.038..10094.668 rows=3761146 loops=1)
Sort Key: pe.peril_id, pe.account_id
Buffers: shared hit=3771563
-> Index Scan using policy_exposure_1192662_portfolio_id_peril_id_account_id_idx on policy_exposure_1192662 pe (cost=0.43..466197.26 rows=3761012 width=80) (actual time=0.036..6448.117 rows=3761146 loops=1)
Index Cond: (portfolio_id = '-1192662'::integer)
Buffers: shared hit=3771563
* Re: Performance of a Query
@ 2018-01-09 22:08 Scott Marlowe <scott.marlowe@gmail.com>
parent: Kumar, Virendra <Virendra.Kumar@guycarp.com>
0 siblings, 2 replies; 8+ messages in thread
From: Scott Marlowe @ 2018-01-09 22:08 UTC (permalink / raw)
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>; +Cc: pgsql-performance
On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra
<Virendra.Kumar@guycarp.com> wrote:
> Hello Gurus,
>
> I am struggling to tune a query which is doing join on top of aggregate for
> around 3 million rows. The plan and SQL is attached to the email.
>
> Below is system Details:
>
> PGSQL version – 10.1
>
> OS – RHEL 3.10.0-693.5.2.el7.x86_64
>
> Binary – Dowloaded from postgres.org compiled and installed.
>
> Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem.
I uploaded your query plan here: https://explain.depesz.com/s/14r6
The most expensive part is the merge join at the end.
Lines like this one: "Buffers: shared hit=676 read=306596, temp
read=135840 written=135972"
Tell me that your sorts etc are spilling to disk, so the first thing
to try is upping work_mem a bit. Don't go crazy, as it can run your
machine out of memory if you do. but doubling or tripling it and
seeing the effect on the query performance is a good place to start.
The good news is that most of your row estimates are about right, so
the query planner is doing what it can to make the query fast, but I'm
guessing if you get the work_mem high enough it will switch from a
merge join to a hash_join or something more efficient for large
numbers of rows.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Performance of a Query
@ 2018-01-09 22:25 Kumar, Virendra <Virendra.Kumar@guycarp.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Kumar, Virendra @ 2018-01-09 22:25 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
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.
Regards,
Virendra
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tuesday, January 09, 2018 5:08 PM
To: Kumar, Virendra
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of a Query
On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra <Virendra.Kumar@guycarp.com> wrote:
> Hello Gurus,
>
> I am struggling to tune a query which is doing join on top of
> aggregate for around 3 million rows. The plan and SQL is attached to the email.
>
> Below is system Details:
>
> PGSQL version – 10.1
>
> OS – RHEL 3.10.0-693.5.2.el7.x86_64
>
> Binary – Dowloaded from postgres.org compiled and installed.
>
> Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem.
I uploaded your query plan here: https://explain.depesz.com/s/14r6
The most expensive part is the merge join at the end.
Lines like this one: "Buffers: shared hit=676 read=306596, temp
read=135840 written=135972"
Tell me that your sorts etc are spilling to disk, so the first thing to try is upping work_mem a bit. Don't go crazy, as it can run your machine out of memory if you do. but doubling or tripling it and seeing the effect on the query performance is a good place to start.
The good news is that most of your row estimates are about right, so the query planner is doing what it can to make the query fast, but I'm guessing if you get the work_mem high enough it will switch from a merge join to a hash_join or something more efficient for large numbers of rows.
________________________________
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of a Query
@ 2018-01-09 22:59 Scott Marlowe <scott.marlowe@gmail.com>
parent: Kumar, Virendra <Virendra.Kumar@guycarp.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2018-01-09 22:59 UTC (permalink / raw)
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>; +Cc: pgsql-performance
On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra
<Virendra.Kumar@guycarp.com> 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@gmail.com]
> Sent: Tuesday, January 09, 2018 5:08 PM
> To: Kumar, Virendra
> Cc: pgsql-performance@postgresql.org
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Performance of a Query
@ 2018-01-09 23:09 Kumar, Virendra <Virendra.Kumar@guycarp.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Kumar, Virendra @ 2018-01-09 23:09 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
It did not seem to help.
See attachment.
Regards,
Virendra
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tuesday, January 09, 2018 6:00 PM
To: Kumar, Virendra
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of a Query
On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra <Virendra.Kumar@guycarp.com> 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@gmail.com]
> Sent: Tuesday, January 09, 2018 5:08 PM
> To: Kumar, Virendra
> Cc: pgsql-performance@postgresql.org
> 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=#
Attachments:
output.txt (text/plain, 8.7K) download
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=#
* Re: Performance of a Query
@ 2018-01-09 23:25 Scott Marlowe <scott.marlowe@gmail.com>
parent: Kumar, Virendra <Virendra.Kumar@guycarp.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2018-01-09 23:25 UTC (permalink / raw)
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>; +Cc: pgsql-performance
On Tue, Jan 9, 2018 at 4:09 PM, Kumar, Virendra
<Virendra.Kumar@guycarp.com> wrote:
> It did not seem to help.
> See attachment.
Yeah while it's still writing, it's about half as much but most of the
time seems to be in merging etc multiple data sets. I'm wondering
what non-default values you might have set otherwise. Are you running
on SSDs? If so lowering random_page_cost might help, but again, this
might just be a very expensive query as well.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Performance of a Query
@ 2018-01-10 03:29 Danylo Hlynskyi <abcz2.uprola@gmail.com>
parent: Kumar, Virendra <Virendra.Kumar@guycarp.com>
1 sibling, 0 replies; 8+ messages in thread
From: Danylo Hlynskyi @ 2018-01-10 03:29 UTC (permalink / raw)
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
Can you try to extract filter part as CTE? Like
with filtered as (select ... where policyid = 123456)
select ... (here comes original query but uses filtered table instead)
10 янв. 2018 г. 1:10 пользователь "Kumar, Virendra" <
Virendra.Kumar@guycarp.com> написал:
It did not seem to help.
See attachment.
Regards,
Virendra
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tuesday, January 09, 2018 6:00 PM
To: Kumar, Virendra
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of a Query
On Tue, Jan 9, 2018 at 3:25 PM, Kumar, Virendra <Virendra.Kumar@guycarp.com>
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@gmail.com]
> Sent: Tuesday, January 09, 2018 5:08 PM
> To: Kumar, Virendra
> Cc: pgsql-performance@postgresql.org
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of a Query
@ 2018-01-10 08:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2018-01-10 08:51 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Kumar, Virendra <Virendra.Kumar@guycarp.com>; +Cc: pgsql-performance
Scott Marlowe wrote:
> On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra
> <Virendra.Kumar@guycarp.com> wrote:
> > Hello Gurus,
> >
> > I am struggling to tune a query which is doing join on top of aggregate for
> > around 3 million rows. The plan and SQL is attached to the email.
> >
> > Below is system Details:
> >
> > PGSQL version – 10.1
> >
> > OS – RHEL 3.10.0-693.5.2.el7.x86_64
> >
> > Binary – Dowloaded from postgres.org compiled and installed.
> >
> > Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem.
>
> I uploaded your query plan here: https://explain.depesz.com/s/14r6
>
> The most expensive part is the merge join at the end.
>
> Lines like this one: "Buffers: shared hit=676 read=306596, temp
> read=135840 written=135972"
>
> Tell me that your sorts etc are spilling to disk, so the first thing
> to try is upping work_mem a bit. Don't go crazy, as it can run your
> machine out of memory if you do. but doubling or tripling it and
> seeing the effect on the query performance is a good place to start.
>
> The good news is that most of your row estimates are about right, so
> the query planner is doing what it can to make the query fast, but I'm
> guessing if you get the work_mem high enough it will switch from a
> merge join to a hash_join or something more efficient for large
> numbers of rows.
Looking at the plan, I'd guess that the following index could be helpful:
CREATE INDEX ON ap.site_exposure(portfolio_id, peril_id, account_id);
Don't know how much it would buy you, but you could avoid the
sequential scan and the sort that way.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-01-10 08:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-09 21:18 Performance of a Query Kumar, Virendra <Virendra.Kumar@guycarp.com>
2018-01-09 22:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2018-01-09 22:25 ` Kumar, Virendra <Virendra.Kumar@guycarp.com>
2018-01-09 22:59 ` Scott Marlowe <scott.marlowe@gmail.com>
2018-01-09 23:09 ` Kumar, Virendra <Virendra.Kumar@guycarp.com>
2018-01-09 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2018-01-10 03:29 ` Danylo Hlynskyi <abcz2.uprola@gmail.com>
2018-01-10 08:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox