explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qWP

Settings
# exclusive inclusive rows x rows loops node
1. 59.857 156,855.466 ↑ 216.0 14,886 1

Merge Join (cost=3,484,002.80..3,611,319.85 rows=3,215,046 width=151) (actual time=156,784.001..156,855.466 rows=14,886 loops=1)

  • Merge Cond: (rwreq.id = roadworkrequest.id)
2.          

CTE ownertreetmp

3. 0.309 0.309 ↑ 1,000.0 1 1

Function Scan on getorgtree (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.309..0.309 rows=1 loops=1)

4.          

CTE roadworkrequesttemp

5. 627.309 1,405.781 ↑ 1.3 225,630 1

Hash Join (cost=36,438.14..127,382.23 rows=286,845 width=28) (actual time=292.225..1,405.781 rows=225,630 loops=1)

  • Hash Cond: (roadworkrequest_4.actualroadworkrequestdatesid = rwractualdates.id)
6. 494.089 494.089 ↑ 1.0 1,376,619 1

Seq Scan on roadworkrequest roadworkrequest_4 (cost=0.00..66,051.41 rows=1,376,641 width=32) (actual time=0.031..494.089 rows=1,376,619 loops=1)

7. 68.716 284.383 ↑ 1.4 450,919 1

Hash (cost=26,121.25..26,121.25 rows=628,791 width=4) (actual time=284.383..284.383 rows=450,919 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,018kB
8. 215.667 215.667 ↑ 1.4 450,919 1

Index Scan using ix_roadworkrequestdates_startdate on roadworkrequestdates rwractualdates (cost=0.43..26,121.25 rows=628,791 width=4) (actual time=66.221..215.667 rows=450,919 loops=1)

  • Index Cond: (startdate >= '2019-01-01 00:00:00'::timestamp without time zone)
  • Filter: (completeddate <= '2020-01-01 23:59:59'::timestamp without time zone)
  • Rows Removed by Filter: 231,244
9. 1,782.152 153,554.156 ↑ 1.1 1,287,069 1

Sort (cost=2,591,214.17..2,594,664.88 rows=1,380,287 width=121) (actual time=153,358.282..153,554.156 rows=1,287,069 loops=1)

  • Sort Key: rwreq.id
  • Sort Method: external merge Disk: 149,000kB
10. 548.128 151,772.004 ↑ 1.0 1,376,619 1

Hash Left Join (cost=5,801.92..2,271,171.79 rows=1,380,287 width=121) (actual time=37.819..151,772.004 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.foremanid = f.id)
11. 2,829.033 151,221.112 ↑ 1.0 1,376,619 1

Hash Left Join (cost=5,449.34..2,267,195.38 rows=1,380,287 width=76) (actual time=35.002..151,221.112 rows=1,376,619 loops=1)

  • Hash Cond: ((SubPlan 4) = usconfirmer.id)
12. 1,361.095 30,361.912 ↑ 1.0 1,376,619 1

Hash Left Join (cost=4,052.59..431,773.30 rows=1,380,287 width=59) (actual time=26.831..30,361.912 rows=1,376,619 loops=1)

  • Hash Cond: ((SubPlan 3) = uscreator.id)
13. 834.928 4,209.731 ↑ 1.0 1,376,619 1

Hash Left Join (cost=2,655.84..118,285.79 rows=1,380,287 width=8) (actual time=14.379..4,209.731 rows=1,376,619 loops=1)

  • Hash Cond: ((rwreq.roadworkcontractid = w.roadworkcontractid) AND (rwreq.worktypeid = w.worktypeid))
14. 345.750 3,364.949 ↑ 1.0 1,376,619 1

Hash Join (cost=1,106.99..82,284.46 rows=1,376,641 width=16) (actual time=4.351..3,364.949 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkstatustypeid = rwstatus.id)
15. 623.177 3,019.181 ↑ 1.0 1,376,619 1

Hash Join (cost=1,105.48..78,003.38 rows=1,376,641 width=20) (actual time=4.330..3,019.181 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkrequestpsmorganisationid = orgpsm.id)
16. 643.646 2,394.927 ↑ 1.0 1,376,619 1

Hash Join (cost=822.69..74,104.74 rows=1,376,641 width=24) (actual time=3.228..2,394.927 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkrequestownerorganisationid = orgowner.id)
17. 1,180.762 1,750.174 ↑ 1.0 1,376,619 1

Hash Join (cost=539.89..70,206.10 rows=1,376,641 width=28) (actual time=2.097..1,750.174 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkcontractid = rwcont.id)
18. 567.395 567.395 ↑ 1.0 1,376,619 1

Seq Scan on roadworkrequest rwreq (cost=0.00..66,051.41 rows=1,376,641 width=40) (actual time=0.032..567.395 rows=1,376,619 loops=1)

19. 1.192 2.017 ↑ 1.0 13,804 1

Hash (cost=367.34..367.34 rows=13,804 width=4) (actual time=2.017..2.017 rows=13,804 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 614kB
20. 0.825 0.825 ↑ 1.0 13,804 1

Index Only Scan using pk_contract on roadworkcontract rwcont (cost=0.29..367.34 rows=13,804 width=4) (actual time=0.009..0.825 rows=13,804 loops=1)

  • Heap Fetches: 0
21. 0.629 1.107 ↑ 1.0 7,073 1

Hash (cost=194.38..194.38 rows=7,073 width=4) (actual time=1.107..1.107 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
22. 0.478 0.478 ↑ 1.0 7,073 1

Index Only Scan using ix_organisation_id_emailenabled on organisation orgowner (cost=0.28..194.38 rows=7,073 width=4) (actual time=0.007..0.478 rows=7,073 loops=1)

  • Heap Fetches: 0
23. 0.593 1.077 ↑ 1.0 7,073 1

Hash (cost=194.38..194.38 rows=7,073 width=4) (actual time=1.077..1.077 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
24. 0.484 0.484 ↑ 1.0 7,073 1

Index Only Scan using ix_organisation_id_emailenabled on organisation orgpsm (cost=0.28..194.38 rows=7,073 width=4) (actual time=0.008..0.484 rows=7,073 loops=1)

  • Heap Fetches: 0
25. 0.006 0.018 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.018..0.018 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.012 0.012 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rwstatus (cost=0.00..1.23 rows=23 width=4) (actual time=0.011..0.012 rows=23 loops=1)

27. 6.212 9.854 ↑ 1.0 49,354 1

Hash (cost=808.54..808.54 rows=49,354 width=8) (actual time=9.854..9.854 rows=49,354 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,440kB
28. 3.642 3.642 ↑ 1.0 49,354 1

Seq Scan on worktypeminimalhours w (cost=0.00..808.54 rows=49,354 width=8) (actual time=0.012..3.642 rows=49,354 loops=1)

29. 5.736 12.331 ↑ 1.0 32,211 1

Hash (cost=994.11..994.11 rows=32,211 width=55) (actual time=12.331..12.331 rows=32,211 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,964kB
30. 6.595 6.595 ↑ 1.0 32,211 1

Seq Scan on ""user"" uscreator (cost=0.00..994.11 rows=32,211 width=55) (actual time=0.030..6.595 rows=32,211 loops=1)

31.          

SubPlan (for Hash Left Join)

32. 0.000 24,778.755 ↑ 1.0 1 2,753,195

Limit (cost=0.43..4.47 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=2,753,195)

33. 24,778.755 24,778.755 ↑ 2.0 1 2,753,195

Index Scan using ix_roadworkrequesthistory_newapprovalrequested on roadworkrequesthistory (cost=0.43..8.51 rows=2 width=4) (actual time=0.009..0.009 rows=1 loops=2,753,195)

  • Index Cond: (roadworkrequestid = rwreq.id)
34. 4.310 7.967 ↑ 1.0 32,211 1

Hash (cost=994.11..994.11 rows=32,211 width=21) (actual time=7.967..7.967 rows=32,211 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,015kB
35. 3.657 3.657 ↑ 1.0 32,211 1

Seq Scan on ""user"" usconfirmer (cost=0.00..994.11 rows=32,211 width=21) (actual time=0.013..3.657 rows=32,211 loops=1)

36.          

SubPlan (for Hash Left Join)

37. 0.000 118,022.200 ↑ 1.0 1 2,565,700

Limit (cost=0.56..26.51 rows=1 width=4) (actual time=0.046..0.046 rows=1 loops=2,565,700)

38. 118,022.200 118,022.200 ↑ 1.0 1 2,565,700

Index Scan using ix_roadworkrequesthistory_timestamp_roadworkid on roadworkrequesthistory roadworkrequesthistory_1 (cost=0.56..26.51 rows=1 width=4) (actual time=0.046..0.046 rows=1 loops=2,565,700)

  • Index Cond: (roadworkrequestid = rwreq.id)
  • Filter: (roadworkstatustypeid = 18)
  • Rows Removed by Filter: 7
39. 1.631 2.764 ↑ 1.0 9,937 1

Hash (cost=228.37..228.37 rows=9,937 width=53) (actual time=2.764..2.764 rows=9,937 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 925kB
40. 1.133 1.133 ↑ 1.0 9,937 1

Seq Scan on foreman f (cost=0.00..228.37 rows=9,937 width=53) (actual time=0.014..1.133 rows=9,937 loops=1)

41. 3.190 3,241.453 ↑ 215.4 14,886 1

Unique (cost=765,396.15..781,428.92 rows=3,206,554 width=4) (actual time=3,233.873..3,241.453 rows=14,886 loops=1)

42. 14.411 3,238.263 ↑ 71.8 44,658 1

Sort (cost=765,396.15..773,412.54 rows=3,206,554 width=4) (actual time=3,233.870..3,238.263 rows=44,658 loops=1)

  • Sort Key: roadworkrequest.id
  • Sort Method: quicksort Memory: 3,459kB
43. 1.708 3,223.852 ↑ 71.8 44,658 1

Append (cost=32.50..331,204.43 rows=3,206,554 width=4) (actual time=294.108..3,223.852 rows=44,658 loops=1)

44. 18.376 1,487.712 ↑ 96.3 14,886 1

Hash Join (cost=32.50..56,684.39 rows=1,434,225 width=4) (actual time=294.107..1,487.712 rows=14,886 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestownerorganisationid = t.id)
45. 1,469.023 1,469.023 ↑ 1.3 225,630 1

CTE Scan on roadworkrequesttemp roadworkrequest (cost=0.00..5,736.90 rows=286,845 width=8) (actual time=292.228..1,469.023 rows=225,630 loops=1)

46. 0.002 0.313 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.313..0.313 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.311 0.311 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.311..0.311 rows=1 loops=1)

48. 13.784 30.405 ↓ 0.0 0 1

Hash Join (cost=32.50..56,684.39 rows=1,434,225 width=4) (actual time=30.405..30.405 rows=0 loops=1)

  • Hash Cond: (roadworkrequest_1.roadworkrequestownercontractororganisationid = t_1.id)
49. 16.614 16.614 ↑ 1.3 225,630 1

CTE Scan on roadworkrequesttemp roadworkrequest_1 (cost=0.00..5,736.90 rows=286,845 width=8) (actual time=0.029..16.614 rows=225,630 loops=1)

50. 0.006 0.007 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.001 0.001 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.001..0.001 rows=1 loops=1)

52. 10.386 754.233 ↓ 8.9 14,886 1

Hash Join (cost=55,393.32..71,300.56 rows=1,682 width=4) (actual time=394.561..754.233 rows=14,886 loops=1)

  • Hash Cond: (roadworkrequest_2.roadworkrequestownerorganisationid = t_2.id)
53. 341.019 743.842 ↓ 556.5 186,995 1

Hash Join (cost=55,360.82..71,208.42 rows=336 width=12) (actual time=387.731..743.842 rows=186,995 loops=1)

  • Hash Cond: ((roadworkrequest_2.roadworkrequestownerorganisationid = c.organisationid) AND (roadworkrequest_2.id = c.roadworkrequestid))
54. 24.479 24.479 ↑ 1.3 225,630 1

CTE Scan on roadworkrequesttemp roadworkrequest_2 (cost=0.00..5,736.90 rows=286,845 width=8) (actual time=0.008..24.479 rows=225,630 loops=1)

55. 216.102 378.344 ↑ 1.0 1,462,553 1

Hash (cost=27,708.53..27,708.53 rows=1,462,553 width=8) (actual time=378.344..378.344 rows=1,462,553 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,792kB
56. 162.242 162.242 ↑ 1.0 1,462,553 1

Seq Scan on combipartners c (cost=0.00..27,708.53 rows=1,462,553 width=8) (actual time=0.036..162.242 rows=1,462,553 loops=1)

57. 0.004 0.005 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
58. 0.001 0.001 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t_2 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.001..0.001 rows=1 loops=1)

59. 22.560 949.794 ↑ 22.6 14,886 1

Hash Join (cost=75,268.82..98,436.79 rows=336,422 width=4) (actual time=935.661..949.794 rows=14,886 loops=1)

  • Hash Cond: (t_3.id = c_1.organisationid)
60. 0.004 0.004 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t_3 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.002..0.004 rows=1 loops=1)

61. 195.390 927.230 ↑ 1.4 243,799 1

Hash (cost=69,803.59..69,803.59 rows=333,058 width=8) (actual time=927.230..927.230 rows=243,799 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,016kB
62. 339.094 731.840 ↑ 1.4 243,799 1

Hash Join (cost=51,704.44..69,803.59 rows=333,058 width=8) (actual time=390.375..731.840 rows=243,799 loops=1)

  • Hash Cond: (roadworkrequest_3.id = c_1.roadworkrequestid)
63. 21.299 21.299 ↑ 1.3 225,630 1

CTE Scan on roadworkrequesttemp roadworkrequest_3 (cost=0.00..5,736.90 rows=286,845 width=4) (actual time=0.012..21.299 rows=225,630 loops=1)

64. 207.864 371.447 ↑ 1.0 1,462,553 1

Hash (cost=27,708.53..27,708.53 rows=1,462,553 width=8) (actual time=371.447..371.447 rows=1,462,553 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,813kB
65. 163.583 163.583 ↑ 1.0 1,462,553 1

Seq Scan on combipartners c_1 (cost=0.00..27,708.53 rows=1,462,553 width=8) (actual time=0.025..163.583 rows=1,462,553 loops=1)

Planning time : 3.481 ms