explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QGxA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 14,862.810 ↑ 378.9 2,347 1

Finalize GroupAggregate (cost=566,645.37..711,491.41 rows=889,390 width=203) (actual time=14,678.587..14,862.810 rows=2,347 loops=1)

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
  • Functions: 193
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 24.164 ms, Inlining 386.401 ms, Optimization 1472.688 ms, Emission 972.470 ms, Total 2855.723 ms
2. 376.080 15,049.050 ↑ 103.4 8,598 1

Gather Merge (cost=566,645.37..680,362.72 rows=889,392 width=203) (actual time=14,678.569..15,049.050 rows=8,598 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
3. 70.492 14,672.970 ↑ 129.3 1,720 5 / 5

Partial GroupAggregate (cost=565,645.32..573,427.50 rows=222,348 width=203) (actual time=14,517.295..14,672.970 rows=1,720 loops=5)

  • Group Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
4. 2,096.025 14,602.478 ↑ 1.0 221,515 5 / 5

Sort (cost=565,645.32..566,201.19 rows=222,348 width=166) (actual time=14,517.189..14,602.478 rows=221,515 loops=5)

  • Sort Key: mbr."Code", sa."Code", bc."Code", bc."TicketPrice", mbr."ContractNumber", mbr."Name
  • Sort Method: external merge Disk: 12096kB
  • Worker 0: Sort Method: external merge Disk: 12616kB
  • Worker 1: Sort Method: external merge Disk: 12672kB
  • Worker 2: Sort Method: external merge Disk: 11496kB
  • Worker 3: Sort Method: external merge Disk: 12448kB
5. 68.758 12,506.453 ↑ 1.0 221,515 5 / 5

Hash Join (cost=220.97..535,213.48 rows=222,348 width=166) (actual time=606.816..12,506.453 rows=221,515 loops=5)

  • Hash Cond: (mr."BillingCodeID" = bc."ID")
6. 120.255 11,870.347 ↑ 1.0 221,515 5 / 5

Hash Join (cost=219.79..534,381.46 rows=222,348 width=175) (actual time=39.404..11,870.347 rows=221,515 loops=5)

  • Hash Cond: (sa."MemberID" = mbr."ID")
7. 122.681 11,749.066 ↑ 1.0 221,515 5 / 5

Hash Join (cost=151.06..533,727.77 rows=222,348 width=41) (actual time=38.355..11,749.066 rows=221,515 loops=5)

  • Hash Cond: (mr."ServiceAreaID" = sa."ID")
8. 103.490 11,625.058 ↑ 1.0 221,515 5 / 5

Nested Loop (cost=1.00..532,993.12 rows=222,348 width=35) (actual time=36.987..11,625.058 rows=221,515 loops=5)

9. 2,364.103 2,364.103 ↓ 1.1 269,337 5 / 5

Parallel Index Scan using "IX_Messages_DeliveryDate" on "Messages" m (cost=0.44..195,314.40 rows=253,370 width=16) (actual time=1.321..2,364.103 rows=269,337 loops=5)

  • 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))
10. 9,157.465 9,157.465 ↑ 2.0 1 1,346,686 / 5

Index Scan using "IX_MessageRecipients_MessageID" on "MessageRecipients" mr (cost=0.56..1.31 rows=2 width=51) (actual time=0.034..0.034 rows=1 loops=1,346,686)

  • Index Cond: ("MessageID" = m."ID")
  • Filter: "Billable
  • Rows Removed by Filter: 0
11. 0.337 1.327 ↓ 1.0 2,226 5 / 5

Hash (cost=122.25..122.25 rows=2,225 width=38) (actual time=1.326..1.327 rows=2,226 loops=5)

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
12. 0.990 0.990 ↓ 1.0 2,226 5 / 5

Seq Scan on "ServiceAreas" sa (cost=0.00..122.25 rows=2,225 width=38) (actual time=0.026..0.990 rows=2,226 loops=5)

13. 0.295 1.026 ↑ 1.0 1,899 5 / 5

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

  • Buckets: 2048 Batches: 1 Memory Usage: 168kB
14. 0.731 0.731 ↑ 1.0 1,899 5 / 5

Seq Scan on "Members" mbr (cost=0.00..44.99 rows=1,899 width=166) (actual time=0.087..0.731 rows=1,899 loops=5)

15. 0.029 567.348 ↑ 1.0 8 5 / 5

Hash (cost=1.08..1.08 rows=8 width=23) (actual time=567.347..567.348 rows=8 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 567.319 567.319 ↑ 1.0 8 5 / 5

Seq Scan on "BillingCodes" bc (cost=0.00..1.08 rows=8 width=23) (actual time=567.310..567.319 rows=8 loops=5)

Execution time : 15,106.535 ms