explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ImtA

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 4,610.422 ↑ 1.0 1 1

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

2. 0.250 4,610.394 ↑ 10.5 20 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
4. 43.586 4,175.509 ↑ 12.6 7 3 / 3

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

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

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

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

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

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

Sort (cost=60,901.44..60,901.66 rows=88 width=4) (actual time=177.908..177.910 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. 177.878 177.878 ↑ 12.6 7 3 / 3

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

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

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

  • Group Key: rrm.roadworkrequestid
10. 1,898.183 1,898.183 ↑ 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.219..1,898.183 rows=1,361,194 loops=3)

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

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

  • Group Key: rrp.roadworkrequestid
12. 105.468 234.321 ↑ 1.1 278,929 3 / 3

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

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

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

  • Hash Cond: (rrp.penaltytypeid = p.id)
14. 82.404 82.404 ↓ 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.177..82.404 rows=298,557 loops=3)

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

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

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

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

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

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

  • Group Key: rra.roadworkrequestid
18. 623.641 623.641 ↑ 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.132..623.641 rows=1,156,526 loops=3)

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

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

  • Group Key: rrp_1.roadworkrequestid
20. 50.397 88.089 ↓ 4.2 6,608 1

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

  • Workers Planned: 2
  • Workers Launched: 2
21. 1.129 37.692 ↓ 2.8 2,203 3 / 3

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

  • Group Key: rrp_1.roadworkrequestid
22. 0.875 36.563 ↓ 2.8 2,203 3 / 3

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

  • Sort Key: rrp_1.roadworkrequestid
  • Sort Method: quicksort Memory: 517kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
23. 4.901 35.688 ↓ 3.0 2,307 3 / 3

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

  • Hash Cond: (rrp_1.penaltytypeid = p_1.id)
24. 28.118 28.118 ↑ 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.143..28.118 rows=99,519 loops=3)

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

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

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

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

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