explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l6I9

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 145,935.919 ↑ 1.0 1 1

Aggregate (cost=373,733.81..373,733.83 rows=1 width=160) (actual time=145,935.919..145,935.919 rows=1 loops=1)

2. 0.330 145,935.882 ↑ 10.5 20 1

Merge Left Join (cost=115,656.18..373,729.59 rows=210 width=128) (actual time=142,387.251..145,935.882 rows=20 loops=1)

  • Merge Cond: (ownrq.id = rrp_1.roadworkrequestid)
3. 96,909.137 145,512.617 ↑ 10.5 20 1

Gather Merge (cost=105,980.61..363,799.35 rows=210 width=100) (actual time=141,970.609..145,512.617 rows=20 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 35.202 48,603.480 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,980.59..362,775.08 rows=88 width=100) (actual time=47,422.832..48,603.480 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rra.roadworkrequestid)
5. 3.638 40,944.555 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,980.16..248,888.38 rows=88 width=68) (actual time=40,123.097..40,944.555 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rrp.roadworkrequestid)
6. 33.883 40,710.883 ↑ 12.6 7 3 / 3

Merge Left Join (cost=60,901.87..196,941.86 rows=88 width=36) (actual time=39,901.050..40,710.883 rows=7 loops=3)

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

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

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

  • Filter: ((psmexportid = 110,250) OR (ownerexportid = 110,250))
  • Rows Removed by Filter: 458,866
9. 414.341 40,502.378 ↓ 1.7 962,553 1 / 3

GroupAggregate (cost=0.43..128,938.23 rows=568,112 width=36) (actual time=0.128..121,507.134 rows=962,553 loops=1)

  • Group Key: rrm.roadworkrequestid
10. 40,088.037 40,088.037 ↑ 1.0 1,396,145 1 / 3

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial rrm (cost=0.43..114,842.02 rows=1,398,961 width=10) (actual time=0.090..120,264.110 rows=1,396,145 loops=1)

  • Filter: ispsm
  • Rows Removed by Filter: 1,161,670
11. 40.564 230.034 ↓ 1.2 277,713 1 / 3

GroupAggregate (cost=44,078.29..49,116.32 rows=226,387 width=36) (actual time=543.892..690.101 rows=277,713 loops=1)

  • Group Key: rrp.roadworkrequestid
12. 43.643 189.470 ↑ 1.0 290,021 1 / 3

Sort (cost=44,078.29..44,814.35 rows=294,426 width=9) (actual time=543.881..568.409 rows=290,021 loops=1)

  • Sort Key: rrp.roadworkrequestid
  • Sort Method: external merge Disk: 5,640kB
13. 16.435 145.827 ↑ 1.0 291,637 1 / 3

Hash Join (cost=674.22..12,300.30 rows=294,426 width=9) (actual time=10.660..437.480 rows=291,637 loops=1)

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

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

  • Filter: ispsm
  • Rows Removed by Filter: 207,558
15. 1.327 3.444 ↑ 1.0 20,618 1 / 3

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

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

Seq Scan on penalty p (cost=0.00..416.49 rows=20,618 width=4) (actual time=0.038..6.351 rows=20,618 loops=1)

  • Filter: (NOT islegalfee)
  • Rows Removed by Filter: 131
17. 346.178 7,623.723 ↓ 1.3 1,188,912 1 / 3

GroupAggregate (cost=0.43..102,673.74 rows=896,974 width=36) (actual time=109.960..22,871.169 rows=1,188,912 loops=1)

  • Group Key: rra.roadworkrequestid
18. 7,277.545 7,277.545 ↑ 1.0 1,188,913 1 / 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=102.215..21,832.636 rows=1,188,913 loops=1)

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

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

  • Group Key: rrp_1.roadworkrequestid
20. 191.288 417.906 ↓ 4.2 6,608 1

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

  • Workers Planned: 2
  • Workers Launched: 2
21. 1.566 226.618 ↓ 2.9 2,275 3 / 3

Partial GroupAggregate (cost=8,675.55..8,691.15 rows=780 width=36) (actual time=224.917..226.618 rows=2,275 loops=3)

  • Group Key: rrp_1.roadworkrequestid
22. 1.175 225.052 ↓ 2.9 2,275 3 / 3

Sort (cost=8,675.55..8,677.50 rows=780 width=9) (actual time=224.903..225.052 rows=2,275 loops=3)

  • Sort Key: rrp_1.roadworkrequestid
  • Sort Method: quicksort Memory: 207kB
  • Worker 0: Sort Method: quicksort Memory: 203kB
  • Worker 1: Sort Method: quicksort Memory: 204kB
23. 7.675 223.877 ↓ 3.0 2,307 3 / 3

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

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

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

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

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

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

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