explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W0W

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 2,119.638 ↑ 1.0 100 1

Limit (cost=214,700.96..215,565.14 rows=100 width=590) (actual time=2,114.636..2,119.638 rows=100 loops=1)

2.          

CTE ownertreetmp

3. 0.676 0.676 ↑ 26.3 38 1

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

4.          

CTE roadworkrequesttemp

5. 13.606 2,107.228 ↑ 13.6 116 1

Hash Join (cost=104,733.15..176,367.09 rows=1,578 width=24) (actual time=1,753.443..2,107.228 rows=116 loops=1)

  • Hash Cond: (roadworkrequest.id = x.roadworkrequestid)
6. 340.695 340.695 ↓ 1.0 156,320 1

Seq Scan on roadworkrequest (cost=0.00..71,234.91 rows=152,013 width=24) (actual time=0.021..340.695 rows=156,320 loops=1)

  • Filter: (roadworkstatustypeid <> ALL ('{15,3,14}'::integer[]))
  • Rows Removed by Filter: 1,220,299
7. 0.050 1,752.927 ↑ 48.3 296 1

Hash (cost=104,554.38..104,554.38 rows=14,302 width=4) (actual time=1,752.927..1,752.927 rows=296 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 139kB
8. 0.016 1,752.877 ↑ 48.3 296 1

Subquery Scan on x (cost=104,196.83..104,554.38 rows=14,302 width=4) (actual time=1,752.812..1,752.877 rows=296 loops=1)

9. 0.044 1,752.861 ↑ 48.3 296 1

GroupAggregate (cost=104,196.83..104,411.36 rows=14,302 width=12) (actual time=1,752.811..1,752.861 rows=296 loops=1)

  • Group Key: rh.roadworkrequestid
10. 0.205 1,752.817 ↑ 47.5 301 1

Sort (cost=104,196.83..104,232.58 rows=14,302 width=4) (actual time=1,752.807..1,752.817 rows=301 loops=1)

  • Sort Key: rh.roadworkrequestid
  • Sort Method: quicksort Memory: 39kB
11. 88.910 1,752.612 ↑ 47.5 301 1

Hash Join (cost=1,642.84..103,209.71 rows=14,302 width=4) (actual time=273.434..1,752.612 rows=301 loops=1)

  • Hash Cond: (rh.userid = "user".id)
12. 1,577.329 1,577.329 ↓ 1.0 1,434,285 1

Index Scan using ix_roadworkrequesthistory_newapprovalrequested on roadworkrequesthistory rh (cost=0.43..97,857.24 rows=1,413,173 width=8) (actual time=0.055..1,577.329 rows=1,434,285 loops=1)

  • Filter: (roadworkstatustypeid = 8)
  • Rows Removed by Filter: 1,376,575
13. 0.010 86.373 ↑ 163.0 2 1

Hash (cost=1,638.33..1,638.33 rows=326 width=4) (actual time=86.373..86.373 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 86.363 86.363 ↑ 163.0 2 1

Seq Scan on "user" (cost=0.00..1,638.33 rows=326 width=4) (actual time=68.210..86.363 rows=2 loops=1)

  • Filter: (((firstname)::text ~~* '%Peter Rutgers%'::text) OR ((lastname)::text ~~* '%Peter Rutgers%'::text) OR ((((firstname)::text || ' '::text) || (lastname)::text) = 'Peter Rutgers'::text) OR ((((lastname)::text || ' '::text) || (firstname)::text) = 'Peter Rutgers'::text))
  • Rows Removed by Filter: 32,209
15.          

CTE validroadworkids

16. 0.077 2,110.791 ↑ 5.3 116 1

HashAggregate (cost=24,972.04..24,978.14 rows=610 width=4) (actual time=2,110.765..2,110.791 rows=116 loops=1)

  • Group Key: roadworkrequest_1.id
17. 0.012 2,110.714 ↑ 1.8 348 1

Append (cost=328.29..24,970.52 rows=610 width=4) (actual time=2,108.140..2,110.714 rows=348 loops=1)

18. 0.031 2,108.150 ↑ 1.7 116 1

HashAggregate (cost=328.29..330.29 rows=200 width=4) (actual time=2,108.139..2,108.150 rows=116 loops=1)

  • Group Key: roadworkrequest_1.id
19. 0.015 2,108.119 ↑ 68.0 116 1

Merge Join (cost=185.21..308.56 rows=7,890 width=4) (actual time=2,108.107..2,108.119 rows=116 loops=1)

  • Merge Cond: (t.id = roadworkrequest_1.roadworkrequestownerorganisationid)
20. 0.011 0.696 ↑ 26.3 38 1

Sort (cost=69.83..72.33 rows=1,000 width=4) (actual time=0.694..0.696 rows=38 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 26kB
21. 0.685 0.685 ↑ 26.3 38 1

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

22. 0.051 2,107.408 ↑ 13.6 116 1

Sort (cost=115.38..119.33 rows=1,578 width=8) (actual time=2,107.405..2,107.408 rows=116 loops=1)

  • Sort Key: roadworkrequest_1.roadworkrequestownerorganisationid
  • Sort Method: quicksort Memory: 30kB
23. 2,107.357 2,107.357 ↑ 13.6 116 1

CTE Scan on roadworkrequesttemp roadworkrequest_1 (cost=0.00..31.56 rows=1,578 width=8) (actual time=1,753.445..2,107.357 rows=116 loops=1)

24. 0.002 0.046 ↓ 0.0 0 1

HashAggregate (cost=328.29..330.29 rows=200 width=4) (actual time=0.046..0.046 rows=0 loops=1)

  • Group Key: roadworkrequest_2.id
25. 0.005 0.044 ↓ 0.0 0 1

Merge Join (cost=185.21..308.56 rows=7,890 width=4) (actual time=0.044..0.044 rows=0 loops=1)

  • Merge Cond: (t_1.id = roadworkrequest_2.roadworkrequestownercontractororganisationid)
26. 0.010 0.013 ↑ 1,000.0 1 1

Sort (cost=69.83..72.33 rows=1,000 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Sort Key: t_1.id
  • Sort Method: quicksort Memory: 26kB
27. 0.003 0.003 ↑ 26.3 38 1

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

28. 0.017 0.026 ↑ 13.6 116 1

Sort (cost=115.38..119.33 rows=1,578 width=8) (actual time=0.023..0.026 rows=116 loops=1)

  • Sort Key: roadworkrequest_2.roadworkrequestownercontractororganisationid
  • Sort Method: quicksort Memory: 30kB
29. 0.009 0.009 ↑ 13.6 116 1

CTE Scan on roadworkrequesttemp roadworkrequest_2 (cost=0.00..31.56 rows=1,578 width=8) (actual time=0.001..0.009 rows=116 loops=1)

30. 0.013 2.213 ↓ 11.6 116 1

Unique (cost=12,035.67..12,035.72 rows=10 width=4) (actual time=2.199..2.213 rows=116 loops=1)

31. 0.019 2.200 ↓ 11.6 116 1

Sort (cost=12,035.67..12,035.69 rows=10 width=4) (actual time=2.199..2.200 rows=116 loops=1)

  • Sort Key: roadworkrequest_3.id
  • Sort Method: quicksort Memory: 30kB
32. 0.241 2.181 ↓ 11.6 116 1

Nested Loop (cost=0.43..12,035.50 rows=10 width=4) (actual time=1.933..2.181 rows=116 loops=1)

  • Join Filter: (roadworkrequest_3.roadworkrequestownerorganisationid = t_2.id)
  • Rows Removed by Join Filter: 4,292
33. 0.002 0.002 ↑ 26.3 38 1

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

34. 0.114 1.938 ↓ 58.0 116 38

Materialize (cost=0.43..11,985.51 rows=2 width=12) (actual time=0.003..0.051 rows=116 loops=38)

35. 0.072 1.824 ↓ 58.0 116 1

Nested Loop (cost=0.43..11,985.50 rows=2 width=12) (actual time=0.094..1.824 rows=116 loops=1)

36. 0.012 0.012 ↑ 13.6 116 1

CTE Scan on roadworkrequesttemp roadworkrequest_3 (cost=0.00..31.56 rows=1,578 width=8) (actual time=0.000..0.012 rows=116 loops=1)

37. 1.740 1.740 ↑ 1.0 1 116

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c (cost=0.43..7.57 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=116)

  • Index Cond: ((roadworkrequestid = roadworkrequest_3.id) AND (organisationid = roadworkrequest_3.roadworkrequestownerorganisationid))
  • Heap Fetches: 116
38. 0.010 0.293 ↑ 1.7 116 1

Unique (cost=12,255.31..12,265.08 rows=200 width=4) (actual time=0.281..0.293 rows=116 loops=1)

39. 0.022 0.283 ↑ 16.8 116 1

Sort (cost=12,255.31..12,260.20 rows=1,954 width=4) (actual time=0.281..0.283 rows=116 loops=1)

  • Sort Key: roadworkrequest_4.id
  • Sort Method: quicksort Memory: 30kB
40. 0.021 0.261 ↑ 16.8 116 1

Hash Join (cost=12,005.21..12,148.50 rows=1,954 width=4) (actual time=0.251..0.261 rows=116 loops=1)

  • Hash Cond: (t_3.id = c_1.organisationid)
41. 0.001 0.001 ↑ 26.3 38 1

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

42. 0.014 0.239 ↑ 16.3 116 1

Hash (cost=11,981.55..11,981.55 rows=1,893 width=8) (actual time=0.239..0.239 rows=116 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 21kB
43. 0.000 0.225 ↑ 16.3 116 1

Nested Loop (cost=0.43..11,981.55 rows=1,893 width=8) (actual time=0.006..0.225 rows=116 loops=1)

44. 0.006 0.006 ↑ 13.6 116 1

CTE Scan on roadworkrequesttemp roadworkrequest_4 (cost=0.00..31.56 rows=1,578 width=4) (actual time=0.000..0.006 rows=116 loops=1)

45. 0.232 0.232 ↑ 1.0 1 116

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c_1 (cost=0.43..7.56 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=116)

  • Index Cond: (roadworkrequestid = roadworkrequest_4.id)
  • Heap Fetches: 116
46.          

CTE cte_validroadworks

47. 0.078 2,113.878 ↑ 6.1 100 1

WindowAgg (cost=5,563.12..5,573.80 rows=610 width=28) (actual time=2,113.789..2,113.878 rows=100 loops=1)

48. 0.040 2,113.800 ↑ 6.0 101 1

Sort (cost=5,563.12..5,564.65 rows=610 width=20) (actual time=2,113.785..2,113.800 rows=101 loops=1)

  • Sort Key: rwreq_1.submissiondate DESC NULLS LAST
  • Sort Method: quicksort Memory: 34kB
49. 0.055 2,113.760 ↑ 5.3 116 1

WindowAgg (cost=2.79..5,534.90 rows=610 width=20) (actual time=2,113.746..2,113.760 rows=116 loops=1)

50. 0.038 2,113.705 ↑ 5.3 116 1

Hash Join (cost=2.79..5,527.28 rows=610 width=12) (actual time=2,110.919..2,113.705 rows=116 loops=1)

  • Hash Cond: (rwreq_1.roadworkstatustypeid = rwstatus_1.id)
51. 0.050 2,113.653 ↑ 5.3 116 1

Nested Loop (cost=1.28..5,523.86 rows=610 width=16) (actual time=2,110.893..2,113.653 rows=116 loops=1)

52. 0.052 2,113.371 ↑ 5.3 116 1

Nested Loop (cost=0.99..5,340.30 rows=610 width=20) (actual time=2,110.871..2,113.371 rows=116 loops=1)

53. 0.034 2,113.203 ↑ 5.3 116 1

Nested Loop (cost=0.71..5,156.74 rows=610 width=24) (actual time=2,110.834..2,113.203 rows=116 loops=1)

54. 0.027 2,112.821 ↑ 5.3 116 1

Nested Loop (cost=0.43..4,971.65 rows=610 width=28) (actual time=2,110.807..2,112.821 rows=116 loops=1)

55. 2,110.822 2,110.822 ↑ 5.3 116 1

CTE Scan on validroadworkids vr (cost=0.00..12.20 rows=610 width=4) (actual time=2,110.765..2,110.822 rows=116 loops=1)

56. 1.972 1.972 ↑ 1.0 1 116

Index Scan using pk_request on roadworkrequest rwreq_1 (cost=0.43..8.13 rows=1 width=40) (actual time=0.017..0.017 rows=1 loops=116)

  • Index Cond: (id = vr.roadworkid)
57. 0.348 0.348 ↑ 1.0 1 116

Index Only Scan using pk_contract on roadworkcontract rwcont_1 (cost=0.29..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=116)

  • Index Cond: (id = rwreq_1.roadworkcontractid)
  • Heap Fetches: 116
58. 0.116 0.116 ↑ 1.0 1 116

Index Only Scan using ix_organisation_id_emailenabled on organisation orgowner_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=116)

  • Index Cond: (id = rwreq_1.roadworkrequestownerorganisationid)
  • Heap Fetches: 116
59. 0.232 0.232 ↑ 1.0 1 116

Index Only Scan using ix_organisation_id_emailenabled on organisation orgpsm_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=116)

  • Index Cond: (id = rwreq_1.roadworkrequestpsmorganisationid)
  • Heap Fetches: 116
60. 0.005 0.014 ↑ 1.0 23 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
61. 0.009 0.009 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rwstatus_1 (cost=0.00..1.23 rows=23 width=4) (actual time=0.007..0.009 rows=23 loops=1)

62.          

CTE cte_combi

63. 0.000 0.598 ↓ 0.0 0 1

Unique (cost=7,753.28..7,755.17 rows=378 width=5) (actual time=0.598..0.598 rows=0 loops=1)

64. 0.006 0.598 ↓ 0.0 0 1

Sort (cost=7,753.28..7,754.22 rows=378 width=5) (actual time=0.598..0.598 rows=0 loops=1)

  • Sort Key: vr_1.roadworkid
  • Sort Method: quicksort Memory: 25kB
65. 0.001 0.592 ↓ 0.0 0 1

Hash Join (cost=32.93..7,737.10 rows=378 width=5) (actual time=0.592..0.592 rows=0 loops=1)

  • Hash Cond: (cp.organisationid = t_4.id)
66. 0.000 0.591 ↓ 0.0 0 1

Nested Loop (cost=0.43..7,654.15 rows=366 width=8) (actual time=0.591..0.591 rows=0 loops=1)

67. 0.011 0.011 ↑ 5.3 116 1

CTE Scan on validroadworkids vr_1 (cost=0.00..12.20 rows=610 width=4) (actual time=0.000..0.011 rows=116 loops=1)

68. 0.348 0.580 ↓ 0.0 0 116

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners cp (cost=0.43..12.52 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=116)

  • Index Cond: (roadworkrequestid = vr_1.roadworkid)
  • Filter: (NOT (SubPlan 5))
  • Rows Removed by Filter: 1
  • Heap Fetches: 116
69.          

SubPlan (for Index Only Scan)

70. 0.232 0.232 ↑ 1.0 1 116

Index Scan using pk_request on roadworkrequest roadworkrequest_5 (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=116)

  • Index Cond: (id = vr_1.roadworkid)
71. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.00..20.00 rows=1,000 width=4) (never executed)

72. 0.000 0.000 ↓ 0.0 0

CTE Scan on ownertreetmp t_4 (cost=0.00..20.00 rows=1,000 width=4) (never executed)

73. 0.160 2,119.631 ↑ 11.5 100 1

Hash Left Join (cost=16.51..9,980.54 rows=1,153 width=590) (actual time=2,114.635..2,119.631 rows=100 loops=1)

  • Hash Cond: (cte_validroadworks.rdwrkid = cte_combi.roadworkid)
74. 0.093 2,117.371 ↑ 6.1 100 1

Nested Loop Left Join (cost=4.22..6,546.41 rows=610 width=581) (actual time=2,113.967..2,117.371 rows=100 loops=1)

75. 0.030 2,116.878 ↑ 6.1 100 1

Nested Loop Left Join (cost=3.79..6,220.40 rows=610 width=557) (actual time=2,113.961..2,116.878 rows=100 loops=1)

76. 0.058 2,115.148 ↑ 6.1 100 1

Hash Join (cost=3.36..5,894.40 rows=610 width=533) (actual time=2,113.870..2,115.148 rows=100 loops=1)

  • Hash Cond: (rwreq.roadworkstatustypeid = rwstatus.id)
77. 0.057 2,115.063 ↑ 6.1 100 1

Nested Loop Left Join (cost=1.84..5,890.98 rows=610 width=520) (actual time=2,113.833..2,115.063 rows=100 loops=1)

78. 0.078 2,115.006 ↑ 6.1 100 1

Nested Loop Left Join (cost=1.56..5,707.42 rows=610 width=491) (actual time=2,113.829..2,115.006 rows=100 loops=1)

79. 0.089 2,114.828 ↑ 6.1 100 1

Nested Loop (cost=1.28..5,523.86 rows=610 width=462) (actual time=2,113.810..2,114.828 rows=100 loops=1)

80. 0.080 2,114.639 ↑ 6.1 100 1

Nested Loop (cost=0.99..5,340.30 rows=610 width=433) (actual time=2,113.807..2,114.639 rows=100 loops=1)

81. 0.103 2,114.459 ↑ 6.1 100 1

Nested Loop (cost=0.71..5,156.74 rows=610 width=396) (actual time=2,113.803..2,114.459 rows=100 loops=1)

82. 0.148 2,114.256 ↑ 6.1 100 1

Nested Loop (cost=0.43..4,971.65 rows=610 width=384) (actual time=2,113.796..2,114.256 rows=100 loops=1)

83. 2,113.908 2,113.908 ↑ 6.1 100 1

CTE Scan on cte_validroadworks (cost=0.00..12.20 rows=610 width=12) (actual time=2,113.790..2,113.908 rows=100 loops=1)

84. 0.200 0.200 ↑ 1.0 1 100

Index Scan using pk_request on roadworkrequest rwreq (cost=0.43..8.13 rows=1 width=372) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (id = cte_validroadworks.rdwrkid)
85. 0.100 0.100 ↑ 1.0 1 100

Index Scan using pk_contract on roadworkcontract rwcont (cost=0.29..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (id = rwreq.roadworkcontractid)
86. 0.100 0.100 ↑ 1.0 1 100

Index Scan using ix_organisation_id_emailenabled on organisation orgowner (cost=0.28..0.30 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (id = rwreq.roadworkrequestownerorganisationid)
87. 0.100 0.100 ↑ 1.0 1 100

Index Scan using ix_organisation_id_emailenabled on organisation orgpsm (cost=0.28..0.30 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (id = rwreq.roadworkrequestpsmorganisationid)
88. 0.100 0.100 ↑ 1.0 1 100

Index Scan using ix_organisation_id_emailenabled on organisation orgownercont (cost=0.28..0.30 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (id = rwreq.roadworkrequestownercontractororganisationid)
89. 0.000 0.000 ↓ 0.0 0 100

Index Scan using ix_organisation_id_emailenabled on organisation orgpsmcont (cost=0.28..0.30 rows=1 width=33) (actual time=0.000..0.000 rows=0 loops=100)

  • Index Cond: (id = rwreq.roadworkrequestpsmcontractororganisationid)
90. 0.008 0.027 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=17) (actual time=0.027..0.027 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
91. 0.019 0.019 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rwstatus (cost=0.00..1.23 rows=23 width=17) (actual time=0.017..0.019 rows=23 loops=1)

92. 1.700 1.700 ↑ 1.0 1 100

Index Scan using pk_roadworkrequestdates on roadworkrequestdates rwrplanneddates (cost=0.43..0.53 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=100)

  • Index Cond: (id = rwreq.plannedroadworkrequestdatesid)
93. 0.400 0.400 ↑ 1.0 1 100

Index Scan using pk_roadworkrequestdates on roadworkrequestdates rwractualdates (cost=0.43..0.53 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (id = rwreq.actualroadworkrequestdatesid)
94. 0.001 0.600 ↓ 0.0 0 1

Hash (cost=7.56..7.56 rows=378 width=5) (actual time=0.600..0.600 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
95. 0.599 0.599 ↓ 0.0 0 1

CTE Scan on cte_combi (cost=0.00..7.56 rows=378 width=5) (actual time=0.599..0.599 rows=0 loops=1)

96.          

SubPlan (for Hash Left Join)

97. 0.000 1.500 ↑ 1.0 1 100

Result (cost=2.92..2.93 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=100)

98.          

Initplan (for Result)

99. 0.000 1.500 ↑ 1.0 1 100

Limit (cost=0.56..2.92 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=100)

100. 1.500 1.500 ↑ 11.0 1 100

Index Only Scan Backward using ix_roadworkrequesthistory_timestamp_roadworkid on roadworkrequesthistory rwhist (cost=0.56..26.53 rows=11 width=8) (actual time=0.015..0.015 rows=1 loops=100)

  • Index Cond: ((roadworkrequestid = rwreq.id) AND ("timestamp" IS NOT NULL))
  • Heap Fetches: 100