explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cby6

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 4,586.227 ↑ 1.0 1 1

Aggregate (cost=373,732.04..373,732.06 rows=1 width=160) (actual time=4,586.226..4,586.227 rows=1 loops=1)

2. 0.232 4,586.202 ↑ 10.5 20 1

Merge Left Join (cost=115,656.18..373,727.82 rows=210 width=128) (actual time=4,206.309..4,586.202 rows=20 loops=1)

  • Merge Cond: (ownrq.id = rrp_1.roadworkrequestid)
3. 352.947 4,493.902 ↑ 10.5 20 1

Gather Merge (cost=105,980.61..363,797.57 rows=210 width=100) (actual time=4,119.633..4,493.902 rows=20 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 43.983 4,140.955 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,980.59..362,773.31 rows=88 width=100) (actual time=4,016.220..4,140.955 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rra.roadworkrequestid)
5. 9.492 3,035.708 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,980.16..248,886.61 rows=88 width=68) (actual time=2,945.356..3,035.708 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rrp.roadworkrequestid)
6. 42.818 2,683.609 ↑ 12.6 7 3 / 3

Merge Left Join (cost=60,901.87..196,940.09 rows=88 width=36) (actual time=2,600.132..2,683.609 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rrm.roadworkrequestid)
7. 0.030 175.726 ↑ 12.6 7 3 / 3

Sort (cost=60,901.44..60,901.66 rows=88 width=4) (actual time=175.724..175.726 rows=7 loops=3)

  • Sort Key: ownrq.id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
8. 175.696 175.696 ↑ 12.6 7 3 / 3

Parallel Seq Scan on roadworkrequest ownrq (cost=0.00..60,898.59 rows=88 width=4) (actual time=36.511..175.696 rows=7 loops=3)

  • Filter: ((psmexportid = 110,250) OR (ownerexportid = 110,250))
  • Rows Removed by Filter: 458,866
9. 573.199 2,465.065 ↓ 1.6 936,771 3 / 3

GroupAggregate (cost=0.43..128,936.67 rows=568,095 width=36) (actual time=0.232..2,465.065 rows=936,771 loops=3)

  • Group Key: rrm.roadworkrequestid
10. 1,891.866 1,891.866 ↑ 1.0 1,361,194 3 / 3

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial rrm (cost=0.43..114,840.88 rows=1,398,919 width=10) (actual time=0.210..1,891.866 rows=1,361,194 loops=3)

  • Filter: ispsm
  • Rows Removed by Filter: 1,122,942
11. 107.438 342.607 ↓ 1.2 267,028 3 / 3

GroupAggregate (cost=44,078.29..49,116.32 rows=226,387 width=36) (actual time=213.336..342.607 rows=267,028 loops=3)

  • Group Key: rrp.roadworkrequestid
12. 106.156 235.169 ↑ 1.1 278,929 3 / 3

Sort (cost=44,078.29..44,814.35 rows=294,426 width=9) (actual time=213.328..235.169 rows=278,929 loops=3)

  • Sort Key: rrp.roadworkrequestid
  • Sort Method: external merge Disk: 5,640kB
  • Worker 0: Sort Method: external merge Disk: 5,648kB
  • Worker 1: Sort Method: external merge Disk: 5,648kB
13. 41.938 129.013 ↑ 1.0 291,637 3 / 3

Hash Join (cost=674.22..12,300.30 rows=294,426 width=9) (actual time=5.541..129.013 rows=291,637 loops=3)

  • Hash Cond: (rrp.penaltytypeid = p.id)
14. 82.164 82.164 ↓ 1.0 298,557 3 / 3

Seq Scan on roadworkrequestpenalty rrp (cost=0.00..10,848.15 rows=296,297 width=13) (actual time=0.184..82.164 rows=298,557 loops=3)

  • Filter: ispsm
  • Rows Removed by Filter: 207,558
15. 2.151 4.911 ↑ 1.0 20,618 3 / 3

Hash (cost=416.49..416.49 rows=20,618 width=4) (actual time=4.910..4.911 rows=20,618 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 981kB
16. 2.760 2.760 ↑ 1.0 20,618 3 / 3

Seq Scan on penalty p (cost=0.00..416.49 rows=20,618 width=4) (actual time=0.104..2.760 rows=20,618 loops=3)

  • Filter: (NOT islegalfee)
  • Rows Removed by Filter: 131
17. 452.211 1,061.264 ↓ 1.3 1,156,525 3 / 3

GroupAggregate (cost=0.43..102,673.74 rows=896,974 width=36) (actual time=0.133..1,061.264 rows=1,156,525 loops=3)

  • Group Key: rra.roadworkrequestid
18. 609.053 609.053 ↑ 1.0 1,156,526 3 / 3

Index Scan using idx_roadworkrequestadditional_roadworkrequestid_ispsm on roadworkrequestadditional rra (cost=0.43..85,513.96 rows=1,189,520 width=7) (actual time=0.126..609.053 rows=1,156,526 loops=3)

  • Index Cond: (ispsm = true)
  • Filter: ispsm
19. 3.831 92.068 ↓ 3.5 6,607 1

Finalize GroupAggregate (cost=9,675.57..9,906.32 rows=1,871 width=36) (actual time=84.153..92.068 rows=6,607 loops=1)

  • Group Key: rrp_1.roadworkrequestid
20. 51.136 88.237 ↓ 4.2 6,608 1

Gather Merge (cost=9,675.57..9,871.24 rows=1,560 width=36) (actual time=84.147..88.237 rows=6,608 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 1.131 37.101 ↓ 2.8 2,203 3 / 3

Partial GroupAggregate (cost=8,675.55..8,691.15 rows=780 width=36) (actual time=35.856..37.101 rows=2,203 loops=3)

  • Group Key: rrp_1.roadworkrequestid
22. 0.873 35.970 ↓ 2.8 2,203 3 / 3

Sort (cost=8,675.55..8,677.50 rows=780 width=9) (actual time=35.841..35.970 rows=2,203 loops=3)

  • Sort Key: rrp_1.roadworkrequestid
  • Sort Method: quicksort Memory: 516kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
23. 4.871 35.097 ↓ 3.0 2,307 3 / 3

Hash Join (cost=418.13..8,638.08 rows=780 width=9) (actual time=7.311..35.097 rows=2,307 loops=3)

  • Hash Cond: (rrp_1.penaltytypeid = p_1.id)
24. 27.590 27.590 ↑ 1.2 99,519 3 / 3

Parallel Seq Scan on roadworkrequestpenalty rrp_1 (cost=0.00..7,895.81 rows=123,457 width=13) (actual time=0.120..27.590 rows=99,519 loops=3)

  • Filter: ispsm
  • Rows Removed by Filter: 69,186
25. 0.022 2.636 ↑ 1.0 131 3 / 3

Hash (cost=416.49..416.49 rows=131 width=4) (actual time=2.636..2.636 rows=131 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
26. 2.614 2.614 ↑ 1.0 131 3 / 3

Seq Scan on penalty p_1 (cost=0.00..416.49 rows=131 width=4) (actual time=0.109..2.614 rows=131 loops=3)

  • Filter: islegalfee
  • Rows Removed by Filter: 20,618