explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r4Fs : Optimization for: plan #nXAu

Settings

Optimization path:

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

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

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
2. 220.966 6,672.239 ↑ 122.7 5,547 1

Gather Merge (cost=835,420.39..925,887.69 rows=680,590 width=346) (actual time=6,086.440..6,672.239 rows=5,547 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 134.029 6,451.273 ↑ 184.0 1,849 3 / 3

Partial GroupAggregate (cost=834,420.37..846,330.69 rows=340,295 width=346) (actual time=6,046.259..6,451.273 rows=1,849 loops=3)

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

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

  • Sort Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
  • Sort Method: external merge Disk: 15200kB
  • Worker 0: Sort Method: external merge Disk: 15216kB
  • Worker 1: Sort Method: external merge Disk: 15224kB
5. 53.419 5,367.754 ↑ 1.2 275,239 3 / 3

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

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

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

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

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

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

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

  • Hash Cond: (mr."MessageID" = m."ID")
9. 1,843.796 1,843.796 ↑ 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.183..1,843.796 rows=4,874,888 loops=3)

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

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

  • Buckets: 131072 Batches: 16 Memory Usage: 4064kB
11. 578.308 578.308 ↑ 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.592..578.308 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.395 1.176 ↑ 1.0 2,226 3 / 3

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

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

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

14. 0.391 0.861 ↑ 1.0 1,899 3 / 3

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

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

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

16. 0.014 0.119 ↑ 1.0 8 3 / 3

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

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

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

Planning time : 1.806 ms
Execution time : 6,686.016 ms