explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l4vcH

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,854.110 ↑ 5.4 14 1

Limit (cost=145,432.78..146,492.96 rows=76 width=590) (actual time=1,853.501..1,854.110 rows=14 loops=1)

2.          

CTE ownertreetmp

3. 0.138 0.138 ↑ 1,000.0 1 1

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

4.          

CTE roadworkrequesttemp

5. 3.320 1,852.127 ↑ 1.8 14 1

Hash Join (cost=104,733.58..143,161.27 rows=25 width=24) (actual time=1,743.421..1,852.127 rows=14 loops=1)

  • Hash Cond: (roadworkrequest.id = x.roadworkrequestid)
6. 105.616 105.616 ↓ 9.4 22,560 1

Index Scan using ix_statustype on roadworkrequest (cost=0.43..38,421.80 rows=2,407 width=24) (actual time=0.037..105.616 rows=22,560 loops=1)

  • Index Cond: (roadworkstatustypeid = 16)
7. 0.056 1,743.191 ↑ 46.1 310 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 139kB
8. 0.015 1,743.135 ↑ 46.1 310 1

Subquery Scan on x (cost=104,196.83..104,554.38 rows=14,302 width=4) (actual time=1,743.070..1,743.135 rows=310 loops=1)

9. 0.049 1,743.120 ↑ 46.1 310 1

GroupAggregate (cost=104,196.83..104,411.36 rows=14,302 width=12) (actual time=1,743.070..1,743.120 rows=310 loops=1)

  • Group Key: rh.roadworkrequestid
10. 0.103 1,743.071 ↑ 44.0 325 1

Sort (cost=104,196.83..104,232.58 rows=14,302 width=4) (actual time=1,743.066..1,743.071 rows=325 loops=1)

  • Sort Key: rh.roadworkrequestid
  • Sort Method: quicksort Memory: 40kB
11. 89.028 1,742.968 ↑ 44.0 325 1

Hash Join (cost=1,642.84..103,209.71 rows=14,302 width=4) (actual time=1,600.116..1,742.968 rows=325 loops=1)

  • Hash Cond: (rh.userid = "user".id)
12. 1,581.961 1,581.961 ↓ 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.049..1,581.961 rows=1,434,285 loops=1)

  • Filter: (roadworkstatustypeid = 8)
  • Rows Removed by Filter: 1,376,575
13. 0.007 71.979 ↑ 326.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 71.972 71.972 ↑ 326.0 1 1

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

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

CTE validroadworkids

16. 0.011 1,852.654 ↑ 5.4 14 1

HashAggregate (cost=527.08..527.84 rows=76 width=4) (actual time=1,852.651..1,852.654 rows=14 loops=1)

  • Group Key: roadworkrequest_1.id
17. 0.003 1,852.643 ↑ 1.8 42 1

Append (cost=26.13..526.89 rows=76 width=4) (actual time=1,852.306..1,852.643 rows=42 loops=1)

18. 0.006 1,852.307 ↑ 1.8 14 1

HashAggregate (cost=26.13..26.38 rows=25 width=4) (actual time=1,852.306..1,852.307 rows=14 loops=1)

  • Group Key: roadworkrequest_1.id
19. 0.007 1,852.301 ↑ 8.9 14 1

Hash Join (cost=0.81..25.81 rows=125 width=4) (actual time=1,852.298..1,852.301 rows=14 loops=1)

  • Hash Cond: (t.id = roadworkrequest_1.roadworkrequestownerorganisationid)
20. 0.140 0.140 ↑ 1,000.0 1 1

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

21. 0.008 1,852.154 ↑ 1.8 14 1

Hash (cost=0.50..0.50 rows=25 width=8) (actual time=1,852.154..1,852.154 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 1,852.146 1,852.146 ↑ 1.8 14 1

CTE Scan on roadworkrequesttemp roadworkrequest_1 (cost=0.00..0.50 rows=25 width=8) (actual time=1,743.423..1,852.146 rows=14 loops=1)

23. 0.001 0.018 ↓ 0.0 0 1

HashAggregate (cost=26.13..26.38 rows=25 width=4) (actual time=0.018..0.018 rows=0 loops=1)

  • Group Key: roadworkrequest_2.id
24. 0.010 0.017 ↓ 0.0 0 1

Hash Join (cost=0.81..25.81 rows=125 width=4) (actual time=0.017..0.017 rows=0 loops=1)

  • Hash Cond: (t_1.id = roadworkrequest_2.roadworkrequestownercontractororganisationid)
25. 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.000..0.001 rows=1 loops=1)

26. 0.004 0.006 ↑ 1.8 14 1

Hash (cost=0.50..0.50 rows=25 width=8) (actual time=0.006..0.006 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.002 0.002 ↑ 1.8 14 1

CTE Scan on roadworkrequesttemp roadworkrequest_2 (cost=0.00..0.50 rows=25 width=8) (actual time=0.001..0.002 rows=14 loops=1)

28. 0.002 0.267 ↓ 14.0 14 1

Unique (cost=235.76..235.77 rows=1 width=4) (actual time=0.265..0.267 rows=14 loops=1)

29. 0.006 0.265 ↓ 14.0 14 1

Sort (cost=235.76..235.76 rows=1 width=4) (actual time=0.265..0.265 rows=14 loops=1)

  • Sort Key: roadworkrequest_3.id
  • Sort Method: quicksort Memory: 25kB
30. 0.007 0.259 ↓ 14.0 14 1

Hash Join (cost=211.95..235.75 rows=1 width=4) (actual time=0.257..0.259 rows=14 loops=1)

  • Hash Cond: (t_2.id = roadworkrequest_3.roadworkrequestownerorganisationid)
31. 0.000 0.000 ↑ 1,000.0 1 1

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

32. 0.005 0.252 ↓ 14.0 14 1

Hash (cost=211.94..211.94 rows=1 width=12) (actual time=0.252..0.252 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.008 0.247 ↓ 14.0 14 1

Nested Loop (cost=0.43..211.94 rows=1 width=12) (actual time=0.043..0.247 rows=14 loops=1)

34. 0.001 0.001 ↑ 1.8 14 1

CTE Scan on roadworkrequesttemp roadworkrequest_3 (cost=0.00..0.50 rows=25 width=8) (actual time=0.000..0.001 rows=14 loops=1)

35. 0.238 0.238 ↑ 1.0 1 14

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c (cost=0.43..8.45 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=14)

  • Index Cond: ((roadworkrequestid = roadworkrequest_3.id) AND (organisationid = roadworkrequest_3.roadworkrequestownerorganisationid))
  • Heap Fetches: 14
36. 0.001 0.048 ↑ 1.8 14 1

Unique (cost=237.08..237.23 rows=25 width=4) (actual time=0.046..0.048 rows=14 loops=1)

37. 0.006 0.047 ↑ 2.2 14 1

Sort (cost=237.08..237.16 rows=31 width=4) (actual time=0.046..0.047 rows=14 loops=1)

  • Sort Key: roadworkrequest_4.id
  • Sort Method: quicksort Memory: 25kB
38. 0.005 0.041 ↑ 2.2 14 1

Hash Join (cost=212.25..236.31 rows=31 width=4) (actual time=0.040..0.041 rows=14 loops=1)

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

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

40. 0.003 0.036 ↑ 2.1 14 1

Hash (cost=211.88..211.88 rows=30 width=8) (actual time=0.036..0.036 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.004 0.033 ↑ 2.1 14 1

Nested Loop (cost=0.43..211.88 rows=30 width=8) (actual time=0.012..0.033 rows=14 loops=1)

42. 0.001 0.001 ↑ 1.8 14 1

CTE Scan on roadworkrequesttemp roadworkrequest_4 (cost=0.00..0.50 rows=25 width=4) (actual time=0.000..0.001 rows=14 loops=1)

43. 0.028 0.028 ↑ 1.0 1 14

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

  • Index Cond: (roadworkrequestid = roadworkrequest_4.id)
  • Heap Fetches: 14
44.          

CTE cte_validroadworks

45. 0.011 1,853.173 ↑ 5.4 14 1

WindowAgg (cost=717.21..718.54 rows=76 width=28) (actual time=1,853.164..1,853.173 rows=14 loops=1)

46. 0.007 1,853.162 ↑ 5.4 14 1

Sort (cost=717.21..717.40 rows=76 width=20) (actual time=1,853.160..1,853.162 rows=14 loops=1)

  • Sort Key: rwreq_1.submissiondate DESC NULLS LAST
  • Sort Method: quicksort Memory: 26kB
47. 0.012 1,853.155 ↑ 5.4 14 1

WindowAgg (cost=2.79..714.84 rows=76 width=20) (actual time=1,853.152..1,853.155 rows=14 loops=1)

48. 0.008 1,853.143 ↑ 5.4 14 1

Hash Join (cost=2.79..713.89 rows=76 width=12) (actual time=1,852.769..1,853.143 rows=14 loops=1)

  • Hash Cond: (rwreq_1.roadworkstatustypeid = rwstatus_1.id)
49. 0.001 1,853.124 ↑ 5.4 14 1

Nested Loop (cost=1.28..712.14 rows=76 width=16) (actual time=1,852.754..1,853.124 rows=14 loops=1)

50. 0.000 1,853.039 ↑ 5.4 14 1

Nested Loop (cost=0.99..689.27 rows=76 width=20) (actual time=1,852.740..1,853.039 rows=14 loops=1)

51. 0.007 1,852.997 ↑ 5.4 14 1

Nested Loop (cost=0.71..666.40 rows=76 width=24) (actual time=1,852.715..1,852.997 rows=14 loops=1)

52. 0.010 1,852.892 ↑ 5.4 14 1

Nested Loop (cost=0.43..643.34 rows=76 width=28) (actual time=1,852.686..1,852.892 rows=14 loops=1)

53. 1,852.658 1,852.658 ↑ 5.4 14 1

CTE Scan on validroadworkids vr (cost=0.00..1.52 rows=76 width=4) (actual time=1,852.652..1,852.658 rows=14 loops=1)

54. 0.224 0.224 ↑ 1.0 1 14

Index Scan using pk_request on roadworkrequest rwreq_1 (cost=0.43..8.45 rows=1 width=40) (actual time=0.016..0.016 rows=1 loops=14)

  • Index Cond: (id = vr.roadworkid)
55. 0.098 0.098 ↑ 1.0 1 14

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

  • Index Cond: (id = rwreq_1.roadworkcontractid)
  • Heap Fetches: 14
56. 0.042 0.042 ↑ 1.0 1 14

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

  • Index Cond: (id = rwreq_1.roadworkrequestownerorganisationid)
  • Heap Fetches: 14
57. 0.084 0.084 ↑ 1.0 1 14

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

  • Index Cond: (id = rwreq_1.roadworkrequestpsmorganisationid)
  • Heap Fetches: 14
58. 0.003 0.011 ↑ 1.0 23 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 0.008 0.008 ↑ 1.0 23 1

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

60.          

CTE cte_combi

61. 0.000 0.145 ↓ 0.0 0 1

Unique (cost=1,008.89..1,009.13 rows=47 width=5) (actual time=0.145..0.145 rows=0 loops=1)

62. 0.003 0.145 ↓ 0.0 0 1

Sort (cost=1,008.89..1,009.01 rows=47 width=5) (actual time=0.145..0.145 rows=0 loops=1)

  • Sort Key: vr_1.roadworkid
  • Sort Method: quicksort Memory: 25kB
63. 0.049 0.142 ↓ 0.0 0 1

Hash Join (cost=982.12..1,007.59 rows=47 width=5) (actual time=0.142..0.142 rows=0 loops=1)

  • Hash Cond: (t_4.id = cp.organisationid)
64. 0.000 0.000 ↑ 1,000.0 1 1

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

65. 0.000 0.093 ↓ 0.0 0 1

Hash (cost=981.54..981.54 rows=46 width=8) (actual time=0.093..0.093 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
66. 0.008 0.093 ↓ 0.0 0 1

Nested Loop (cost=0.43..981.54 rows=46 width=8) (actual time=0.093..0.093 rows=0 loops=1)

67. 0.001 0.001 ↑ 5.4 14 1

CTE Scan on validroadworkids vr_1 (cost=0.00..1.52 rows=76 width=4) (actual time=0.000..0.001 rows=14 loops=1)

68. 0.028 0.084 ↓ 0.0 0 14

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners cp (cost=0.43..12.88 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=14)

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

SubPlan (for Index Only Scan)

70. 0.056 0.056 ↑ 1.0 1 14

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

  • Index Cond: (id = vr_1.roadworkid)
71. 0.031 1,854.109 ↑ 5.4 14 1

Nested Loop Left Join (cost=5.75..1,065.93 rows=76 width=590) (actual time=1,853.501..1,854.109 rows=14 loops=1)

72. 0.006 1,853.798 ↑ 5.4 14 1

Nested Loop Left Join (cost=5.32..802.53 rows=76 width=558) (actual time=1,853.451..1,853.798 rows=14 loops=1)

73. 0.011 1,853.554 ↑ 5.4 14 1

Hash Left Join (cost=4.89..761.91 rows=76 width=534) (actual time=1,853.394..1,853.554 rows=14 loops=1)

  • Hash Cond: (cte_validroadworks.rdwrkid = cte_combi.roadworkid)
74. 0.014 1,853.397 ↑ 5.4 14 1

Hash Join (cost=3.36..759.63 rows=76 width=533) (actual time=1,853.242..1,853.397 rows=14 loops=1)

  • Hash Cond: (rwreq.roadworkstatustypeid = rwstatus.id)
75. 0.011 1,853.352 ↑ 5.4 14 1

Nested Loop Left Join (cost=1.84..757.88 rows=76 width=520) (actual time=1,853.204..1,853.352 rows=14 loops=1)

76. 0.010 1,853.341 ↑ 5.4 14 1

Nested Loop Left Join (cost=1.56..735.01 rows=76 width=491) (actual time=1,853.200..1,853.341 rows=14 loops=1)

77. 0.013 1,853.303 ↑ 5.4 14 1

Nested Loop (cost=1.28..712.14 rows=76 width=462) (actual time=1,853.182..1,853.303 rows=14 loops=1)

78. 0.011 1,853.276 ↑ 5.4 14 1

Nested Loop (cost=0.99..689.27 rows=76 width=433) (actual time=1,853.177..1,853.276 rows=14 loops=1)

79. 0.016 1,853.251 ↑ 5.4 14 1

Nested Loop (cost=0.71..666.40 rows=76 width=396) (actual time=1,853.174..1,853.251 rows=14 loops=1)

80. 0.014 1,853.221 ↑ 5.4 14 1

Nested Loop (cost=0.43..643.34 rows=76 width=384) (actual time=1,853.171..1,853.221 rows=14 loops=1)

81. 1,853.179 1,853.179 ↑ 5.4 14 1

CTE Scan on cte_validroadworks (cost=0.00..1.52 rows=76 width=12) (actual time=1,853.165..1,853.179 rows=14 loops=1)

82. 0.028 0.028 ↑ 1.0 1 14

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

  • Index Cond: (id = cte_validroadworks.rdwrkid)
83. 0.014 0.014 ↑ 1.0 1 14

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=14)

  • Index Cond: (id = rwreq.roadworkcontractid)
84. 0.014 0.014 ↑ 1.0 1 14

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=14)

  • Index Cond: (id = rwreq.roadworkrequestownerorganisationid)
85. 0.014 0.014 ↑ 1.0 1 14

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=14)

  • Index Cond: (id = rwreq.roadworkrequestpsmorganisationid)
86. 0.028 0.028 ↑ 1.0 1 14

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

  • Index Cond: (id = rwreq.roadworkrequestownercontractororganisationid)
87. 0.000 0.000 ↓ 0.0 0 14

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=14)

  • Index Cond: (id = rwreq.roadworkrequestpsmcontractororganisationid)
88. 0.009 0.031 ↑ 1.0 23 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
89. 0.022 0.022 ↑ 1.0 23 1

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

90. 0.000 0.146 ↓ 0.0 0 1

Hash (cost=0.94..0.94 rows=47 width=5) (actual time=0.146..0.146 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
91. 0.146 0.146 ↓ 0.0 0 1

CTE Scan on cte_combi (cost=0.00..0.94 rows=47 width=5) (actual time=0.146..0.146 rows=0 loops=1)

92. 0.238 0.238 ↑ 1.0 1 14

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=14)

  • Index Cond: (id = rwreq.plannedroadworkrequestdatesid)
93. 0.042 0.042 ↑ 1.0 1 14

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

  • Index Cond: (id = rwreq.actualroadworkrequestdatesid)
94.          

SubPlan (for Nested Loop Left Join)

95. 0.000 0.238 ↑ 1.0 1 14

Result (cost=2.92..2.93 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=14)

96.          

Initplan (for Result)

97. 0.000 0.238 ↑ 1.0 1 14

Limit (cost=0.56..2.92 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=14)

98. 0.238 0.238 ↑ 11.0 1 14

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

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