explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nXAu

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 6,339.702 ↑ 365.4 2,235 1

Finalize GroupAggregate (cost=835,420.39..951,409.83 rows=816,709 width=346) (actual time=5,961.026..6,339.702 rows=2,235 loops=1)

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
2. 250.426 6,485.456 ↑ 122.8 5,541 1

Gather Merge (cost=835,420.39..925,887.69 rows=680,590 width=346) (actual time=5,961.014..6,485.456 rows=5,541 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 110.932 6,235.030 ↑ 184.2 1,847 3 / 3

Partial GroupAggregate (cost=834,420.37..846,330.69 rows=340,295 width=346) (actual time=5,887.034..6,235.030 rows=1,847 loops=3)

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
4. 918.540 6,124.098 ↑ 1.2 275,239 3 / 3

Sort (cost=834,420.37..835,271.11 rows=340,295 width=309) (actual time=5,887.007..6,124.098 rows=275,239 loops=3)

  • Sort Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
  • Sort Method: external merge Disk: 14504kB
  • Worker 0: Sort Method: external merge Disk: 16000kB
  • Worker 1: Sort Method: external merge Disk: 15152kB
5. 54.859 5,205.558 ↑ 1.2 275,239 3 / 3

Hash Join (cost=203,526.65..705,447.34 rows=340,295 width=309) (actual time=3,831.678..5,205.558 rows=275,239 loops=3)

  • Hash Cond: (mr."BillingCodeID" = bc."ID")
6. 61.149 5,150.534 ↑ 1.2 275,239 3 / 3

Hash Join (cost=203,525.47..704,162.93 rows=340,295 width=175) (actual time=3,831.443..5,150.534 rows=275,239 loops=3)

  • Hash Cond: (sa."MemberID" = mbr."ID")
7. 61.498 5,088.365 ↑ 1.2 275,239 3 / 3

Hash Join (cost=203,456.74..703,198.94 rows=340,295 width=41) (actual time=3,830.407..5,088.365 rows=275,239 loops=3)

  • Hash Cond: (mr."ServiceAreaID" = sa."ID")
8. 2,568.039 5,025.747 ↑ 1.2 275,239 3 / 3

Parallel Hash Join (cost=203,352.66..702,200.18 rows=340,295 width=35) (actual time=3,829.258..5,025.747 rows=275,239 loops=3)

  • Hash Cond: (mr."MessageID" = m."ID")
9. 1,721.439 1,721.439 ↑ 1.2 4,874,888 3 / 3

Parallel Seq Scan on "MessageRecipients" mr (cost=0.00..362,676.57 rows=6,056,362 width=51) (actual time=0.098..1,721.439 rows=4,874,888 loops=3)

  • Filter: "Billable
  • Rows Removed by Filter: 1004822
10. 137.829 736.269 ↑ 1.2 342,705 3 / 3

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

  • Buckets: 131072 Batches: 16 Memory Usage: 4064kB
11. 598.440 598.440 ↑ 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.807..598.440 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.401 1.120 ↑ 1.0 2,226 3 / 3

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

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

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

14. 0.421 1.020 ↑ 1.0 1,899 3 / 3

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

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

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

16. 0.020 0.165 ↑ 1.0 8 3 / 3

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

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

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

Planning time : 2.170 ms
Execution time : 6,496.832 ms