explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZLSB : Optimization for: plan #nXAu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 6,631.002 ↑ 443.5 2,235 1

Finalize GroupAggregate (cost=892,842.99..1,033,601.28 rows=991,112 width=346) (actual time=6,237.580..6,631.002 rows=2,235 loops=1)

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
2. 244.743 6,780.557 ↑ 148.8 5,550 1

Gather Merge (cost=892,842.99..1,002,629.05 rows=825,926 width=346) (actual time=6,237.569..6,780.557 rows=5,550 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 111.360 6,535.814 ↑ 223.2 1,850 3 / 3

Partial GroupAggregate (cost=891,842.96..906,296.67 rows=412,963 width=346) (actual time=6,183.747..6,535.814 rows=1,850 loops=3)

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
4. 910.865 6,424.454 ↑ 1.5 275,239 3 / 3

Sort (cost=891,842.96..892,875.37 rows=412,963 width=309) (actual time=6,183.719..6,424.454 rows=275,239 loops=3)

  • Sort Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
  • Sort Method: external merge Disk: 15384kB
  • Worker 0: Sort Method: external merge Disk: 15784kB
  • Worker 1: Sort Method: external merge Disk: 14496kB
5. 51.386 5,513.589 ↑ 1.5 275,239 3 / 3

Hash Join (cost=203,526.65..734,756.49 rows=412,963 width=309) (actual time=4,085.643..5,513.589 rows=275,239 loops=3)

  • Hash Cond: (mr."BillingCodeID" = bc."ID")
6. 59.640 5,462.092 ↑ 1.4 285,478 3 / 3

Hash Join (cost=203,525.47..733,198.06 rows=412,963 width=175) (actual time=4,085.457..5,462.092 rows=285,478 loops=3)

  • Hash Cond: (sa."MemberID" = mbr."ID")
7. 63.632 5,401.550 ↑ 1.4 285,478 3 / 3

Hash Join (cost=203,456.74..732,042.89 rows=412,963 width=41) (actual time=4,084.533..5,401.550 rows=285,478 loops=3)

  • Hash Cond: (mr."ServiceAreaID" = sa."ID")
8. 2,883.335 5,336.785 ↑ 1.2 350,795 3 / 3

Parallel Hash Join (cost=203,352.66..730,853.08 rows=412,963 width=35) (actual time=4,083.380..5,336.785 rows=350,795 loops=3)

  • Hash Cond: (mr."MessageID" = m."ID")
9. 1,737.289 1,737.289 ↑ 1.3 5,879,710 3 / 3

Parallel Seq Scan on "MessageRecipients" mr (cost=0.00..362,676.57 rows=7,349,657 width=51) (actual time=0.099..1,737.289 rows=5,879,710 loops=3)

10. 135.156 716.161 ↑ 1.2 342,705 3 / 3

Parallel Hash (cost=196,293.94..196,293.94 rows=406,057 width=16) (actual time=716.161..716.161 rows=342,705 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 4064kB
11. 581.005 581.005 ↑ 1.2 342,705 3 / 3

Parallel Index Scan using "IX_Messages_DeliveryDate" on "Messages" m (cost=0.44..196,293.94 rows=406,057 width=16) (actual time=0.781..581.005 rows=342,705 loops=3)

  • Index Cond: (("DeliveryDate" >= '2020-01-01 00:00:00-05'::timestamp with time zone) AND ("DeliveryDate" < '2020-02-01 00:00:00-05'::timestamp with time zone))
12. 0.383 1.133 ↑ 1.0 2,226 3 / 3

Hash (cost=76.26..76.26 rows=2,226 width=38) (actual time=1.133..1.133 rows=2,226 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
13. 0.750 0.750 ↑ 1.0 2,226 3 / 3

Seq Scan on "ServiceAreas" sa (cost=0.00..76.26 rows=2,226 width=38) (actual time=0.093..0.750 rows=2,226 loops=3)

14. 0.419 0.902 ↑ 1.0 1,899 3 / 3

Hash (cost=44.99..44.99 rows=1,899 width=166) (actual time=0.902..0.902 rows=1,899 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 168kB
15. 0.483 0.483 ↑ 1.0 1,899 3 / 3

Seq Scan on "Members" mbr (cost=0.00..44.99 rows=1,899 width=166) (actual time=0.059..0.483 rows=1,899 loops=3)

16. 0.013 0.111 ↑ 1.0 8 3 / 3

Hash (cost=1.08..1.08 rows=8 width=166) (actual time=0.111..0.111 rows=8 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.098 0.098 ↑ 1.0 8 3 / 3

Seq Scan on "BillingCodes" bc (cost=0.00..1.08 rows=8 width=166) (actual time=0.096..0.098 rows=8 loops=3)

Planning time : 2.335 ms
Execution time : 6,793.089 ms