explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1IwM

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 692.536 ↑ 1.0 1 1

Aggregate (cost=129,199.13..129,199.15 rows=1 width=160) (actual time=692.536..692.536 rows=1 loops=1)

2. 0.014 692.522 ↑ 21.0 20 1

Nested Loop Left Join (cost=115,656.18..129,190.71 rows=420 width=128) (actual time=665.939..692.522 rows=20 loops=1)

3. 0.017 692.288 ↑ 21.0 20 1

Nested Loop Left Join (cost=115,655.75..125,625.96 rows=420 width=100) (actual time=665.900..692.288 rows=20 loops=1)

4. 0.228 692.011 ↑ 10.5 20 1

Merge Left Join (cost=115,655.32..123,802.68 rows=210 width=68) (actual time=665.841..692.011 rows=20 loops=1)

  • Merge Cond: (ownrq.id = rrp_1.roadworkrequestid)
5. 187.656 602.726 ↑ 10.5 20 1

Gather Merge (cost=105,979.75..113,872.44 rows=210 width=36) (actual time=582.022..602.726 rows=20 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 6.505 415.070 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,979.73..112,848.18 rows=88 width=36) (actual time=408.183..415.070 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rrp.roadworkrequestid)
7. 0.029 177.903 ↑ 12.6 7 3 / 3

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

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

  • Filter: ((psmexportid = 110,250) OR (ownerexportid = 110,250))
  • Rows Removed by Filter: 458,866
9. 73.043 230.662 ↓ 1.2 274,876 2 / 3

GroupAggregate (cost=44,078.29..49,116.32 rows=226,387 width=36) (actual time=214.330..345.993 rows=274,876 loops=2)

  • Group Key: rrp.roadworkrequestid
10. 70.919 157.619 ↑ 1.0 287,081 2 / 3

Sort (cost=44,078.29..44,814.35 rows=294,426 width=9) (actual time=214.316..236.428 rows=287,081 loops=2)

  • Sort Key: rrp.roadworkrequestid
  • Sort Method: external merge Disk: 5,648kB
  • Worker 0: Sort Method: external merge Disk: 5,648kB
11. 27.980 86.699 ↑ 1.0 291,637 2 / 3

Hash Join (cost=674.22..12,300.30 rows=294,426 width=9) (actual time=5.709..130.049 rows=291,637 loops=2)

  • Hash Cond: (rrp.penaltytypeid = p.id)
12. 55.077 55.077 ↓ 1.0 298,557 2 / 3

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

  • Filter: ispsm
  • Rows Removed by Filter: 207,558
13. 1.527 3.643 ↑ 1.0 20,618 2 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 981kB
14. 2.116 2.116 ↑ 1.0 20,618 2 / 3

Seq Scan on penalty p (cost=0.00..416.49 rows=20,618 width=4) (actual time=0.113..3.174 rows=20,618 loops=2)

  • Filter: (NOT islegalfee)
  • Rows Removed by Filter: 131
15. 0.000 89.057 ↓ 3.5 6,607 1

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

  • Group Key: rrp_1.roadworkrequestid
16. 52.500 91.400 ↓ 4.2 6,608 1

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

  • Workers Planned: 2
  • Workers Launched: 2
17. 1.130 38.900 ↓ 2.8 2,203 3 / 3

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

  • Group Key: rrp_1.roadworkrequestid
18. 0.662 37.770 ↓ 2.8 2,203 3 / 3

Sort (cost=8,675.55..8,677.50 rows=780 width=9) (actual time=37.664..37.770 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
19. 5.009 37.108 ↓ 3.0 2,307 3 / 3

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

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

  • Filter: ispsm
  • Rows Removed by Filter: 69,186
21. 0.023 2.659 ↑ 1.0 131 3 / 3

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

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

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

  • Filter: islegalfee
  • Rows Removed by Filter: 20,618
23. 0.020 0.260 ↑ 2.0 1 20

GroupAggregate (cost=0.43..8.64 rows=2 width=36) (actual time=0.013..0.013 rows=1 loops=20)

  • Group Key: rrm.roadworkrequestid
24. 0.240 0.240 ↑ 2.0 1 20

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial rrm (cost=0.43..8.61 rows=2 width=10) (actual time=0.012..0.012 rows=1 loops=20)

  • Index Cond: (roadworkrequestid = ownrq.id)
  • Filter: ispsm
  • Rows Removed by Filter: 1
25. 0.020 0.220 ↑ 1.0 1 20

GroupAggregate (cost=0.43..8.47 rows=1 width=36) (actual time=0.011..0.011 rows=1 loops=20)

  • Group Key: rra.roadworkrequestid
26. 0.200 0.200 ↑ 1.0 1 20

Index Scan using idx_roadworkrequestadditional_roadworkrequestid_ispsm on roadworkrequestadditional rra (cost=0.43..8.45 rows=1 width=7) (actual time=0.010..0.010 rows=1 loops=20)

  • Index Cond: ((roadworkrequestid = ownrq.id) AND (ispsm = true))
  • Filter: ispsm