|
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
Attachment:
AggQuery.sql
Description: AggQuery.sql
