explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wagG

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 78,681.478 ↑ 16.7 6 1

Unique (cost=39,688.49..39,689.74 rows=100 width=65) (actual time=78,681.475..78,681.478 rows=6 loops=1)

2.          

CTE asset_list

3. 0.004 1.434 ↑ 1.0 1 1

Nested Loop Left Join (cost=39,509.20..39,511.46 rows=1 width=48) (actual time=1.424..1.434 rows=1 loops=1)

  • Filter: ((obs_ad.value IS NULL) OR (obs_ad.value = '1'::double precision))
4.          

Initplan (for Nested Loop Left Join)

5. 0.006 0.006 ↑ 1.0 1 1

Index Scan using scenario_pk on scenario (cost=0.28..2.50 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (scenarioid = 1696)
6. 0.000 1.397 ↑ 1.0 1 1

Unique (cost=39,506.13..39,506.15 rows=1 width=48) (actual time=1.387..1.397 rows=1 loops=1)

7.          

Initplan (for Unique)

8. 0.003 0.013 ↑ 1.0 1 1

Nested Loop (cost=0.56..5.00 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

9. 0.003 0.003 ↑ 1.0 1 1

Index Scan using scenario_pk on scenario s (cost=0.28..2.50 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (scenarioid = 1696)
10. 0.007 0.007 ↑ 1.0 1 1

Index Scan using work_pk on work w (cost=0.28..2.49 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (workid = s.workid)
11. 0.010 0.010 ↑ 1.0 1 1

Index Scan using scenario_pk on scenario scenario_1 (cost=0.28..2.50 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (scenarioid = 1696)
12. 0.025 1.388 ↓ 31.0 31 1

Sort (cost=39,498.63..39,498.63 rows=1 width=48) (actual time=1.385..1.388 rows=31 loops=1)

  • Sort Key: a.assetid, pipe_ad.assetdatasourceid, ar.assetid, (round((st_length(st_transform(a.geometry, $2)))::numeric, 3))
  • Sort Method: quicksort Memory: 26kB
13. 0.047 1.363 ↓ 31.0 31 1

Nested Loop (cost=2.00..39,498.62 rows=1 width=48) (actual time=0.099..1.363 rows=31 loops=1)

  • Join Filter: (aat.assetid = ar.relatedassetid)
14. 0.003 1.192 ↓ 31.0 31 1

Nested Loop (cost=1.44..39,494.35 rows=1 width=202) (actual time=0.058..1.192 rows=31 loops=1)

15. 0.010 1.096 ↓ 31.0 31 1

Nested Loop (cost=1.00..39,493.72 rows=1 width=16) (actual time=0.047..1.096 rows=31 loops=1)

16. 0.975 0.975 ↓ 18.5 37 1

Index Scan using assetdata_idx on assetdata pipe_ad (cost=0.56..39,489.48 rows=2 width=12) (actual time=0.031..0.975 rows=37 loops=1)

  • Index Cond: (scenariocomponentid = $3)
  • Filter: (assetdatasourceid = ANY ('{56861}'::integer[]))
  • Rows Removed by Filter: 1650
17. 0.111 0.111 ↑ 1.0 1 37

Index Only Scan using assetassettype_unique_constraint on assetassettype aat (cost=0.44..2.11 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=37)

  • Index Cond: ((assetid = pipe_ad.assetid) AND (assettypeid = 3))
  • Heap Fetches: 31
18. 0.093 0.093 ↑ 1.0 1 31

Index Scan using asset_pk on asset a (cost=0.43..0.63 rows=1 width=186) (actual time=0.002..0.003 rows=1 loops=31)

  • Index Cond: (assetid = aat.assetid)
19. 0.124 0.124 ↑ 13.0 1 31

Index Scan using assetrelationship_idx1 on assetrelationship ar (cost=0.56..4.07 rows=13 width=8) (actual time=0.003..0.004 rows=1 loops=31)

  • Index Cond: (relatedassetid = a.assetid)
  • Filter: (assetrelationshiptypeid = 31)
20. 0.027 0.027 ↓ 0.0 0 1

Index Scan using assetdata_uidx on assetdata obs_ad (cost=0.56..2.79 rows=1 width=12) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: ((assetid = ar.assetid) AND (scenariocomponentid = $0) AND (assetdatatypeid = 255))
21.          

CTE ads_list

22. 0.004 0.004 ↑ 1.0 1 1

HashAggregate (cost=0.03..0.04 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=1)

  • Group Key: al.pipeid, al.scenariocomponentid, al.assetdatasourceid
23. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on asset_list al (cost=0.00..0.02 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=1)

24.          

CTE input_data

25. 0.077 1.784 ↑ 2.0 1 1

GroupAggregate (cost=41.12..41.72 rows=2 width=40) (actual time=1.784..1.784 rows=1 loops=1)

  • Group Key: al_1.pipeid
26. 0.000 1.707 ↑ 2.0 1 1

Unique (cost=41.12..41.17 rows=2 width=114) (actual time=1.706..1.707 rows=1 loops=1)

27. 0.018 1.707 ↑ 2.0 1 1

Sort (cost=41.12..41.12 rows=2 width=114) (actual time=1.706..1.707 rows=1 loops=1)

  • Sort Key: al_1.pipeid, ld.value, (row_number() OVER (?)), ado.alias, cnt.stringvalue, ado2.alias, setup.value, al_1.gislength, inspectedlength.value, pipesetup.stringvalue
  • Sort Method: quicksort Memory: 25kB
28. 0.007 1.689 ↑ 2.0 1 1

WindowAgg (cost=41.07..41.11 rows=2 width=114) (actual time=1.687..1.689 rows=1 loops=1)

29. 0.011 1.682 ↑ 2.0 1 1

Sort (cost=41.07..41.07 rows=2 width=106) (actual time=1.682..1.682 rows=1 loops=1)

  • Sort Key: al_1.pipeid, ld.value
  • Sort Method: quicksort Memory: 25kB
30. 0.003 1.671 ↑ 2.0 1 1

Nested Loop Left Join (cost=6.19..41.06 rows=2 width=106) (actual time=1.607..1.671 rows=1 loops=1)

  • Join Filter: (ado2.value = qnt.value)
  • Rows Removed by Join Filter: 3
31. 0.003 1.588 ↑ 2.0 1 1

Nested Loop Left Join (cost=6.19..31.80 rows=2 width=102) (actual time=1.524..1.588 rows=1 loops=1)

32. 0.003 1.582 ↑ 2.0 1 1

Nested Loop Left Join (cost=5.63..26.20 rows=2 width=102) (actual time=1.518..1.582 rows=1 loops=1)

33. 0.003 1.471 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.82..14.02 rows=1 width=94) (actual time=1.458..1.471 rows=1 loops=1)

34. 0.002 1.463 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.26..11.22 rows=1 width=83) (actual time=1.451..1.463 rows=1 loops=1)

35. 0.003 1.458 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.69..8.42 rows=1 width=75) (actual time=1.446..1.458 rows=1 loops=1)

36. 0.002 1.452 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.13..5.62 rows=1 width=67) (actual time=1.440..1.452 rows=1 loops=1)

37. 0.005 1.446 ↑ 1.0 1 1

Nested Loop (cost=0.56..2.82 rows=1 width=56) (actual time=1.435..1.446 rows=1 loops=1)

38. 1.435 1.435 ↑ 1.0 1 1

CTE Scan on asset_list al_1 (cost=0.00..0.02 rows=1 width=48) (actual time=1.425..1.435 rows=1 loops=1)

39. 0.006 0.006 ↑ 1.0 1 1

Index Scan using assetdata_uidx on assetdata ld (cost=0.56..2.79 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: ((assetid = al_1.assetid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 761) AND (assetdatasourceid = al_1.assetdatasourceid))
40. 0.004 0.004 ↓ 0.0 0 1

Index Scan using assetdata_uidx on assetdata cnt (cost=0.56..2.79 rows=1 width=23) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((assetid = al_1.assetid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 762) AND (al_1.assetdatasourceid = assetdatasourceid))
41. 0.003 0.003 ↓ 0.0 0 1

Index Scan using assetdata_uidx on assetdata qnt (cost=0.56..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((assetid = al_1.assetid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 505) AND (al_1.assetdatasourceid = assetdatasourceid))
42. 0.003 0.003 ↑ 1.0 1 1

Index Scan using assetdata_uidx on assetdata setup (cost=0.56..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((assetid = al_1.assetid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 763) AND (al_1.assetdatasourceid = assetdatasourceid))
43. 0.005 0.005 ↑ 1.0 1 1

Index Scan using assetdata_uidx on assetdata pipesetup (cost=0.56..2.79 rows=1 width=23) (actual time=0.003..0.005 rows=1 loops=1)

  • Index Cond: ((assetid = al_1.pipeid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 666) AND (al_1.assetdatasourceid = assetdatasourceid))
44. 0.027 0.108 ↑ 2.0 1 1

Hash Right Join (cost=2.80..12.16 rows=2 width=24) (actual time=0.057..0.108 rows=1 loops=1)

  • Hash Cond: (ado.value = cc.value)
45. 0.071 0.071 ↑ 1.1 39 1

Seq Scan on assetdataoption ado (cost=0.00..9.19 rows=41 width=20) (actual time=0.018..0.071 rows=39 loops=1)

  • Filter: (assetdataoptionsetid = 9)
  • Rows Removed by Filter: 376
46. 0.005 0.010 ↑ 1.0 1 1

Hash (cost=2.79..2.79 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.005 0.005 ↑ 1.0 1 1

Index Scan using assetdata_uidx on assetdata cc (cost=0.56..2.79 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((assetid = al_1.assetid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 506) AND (al_1.assetdatasourceid = assetdatasourceid))
48. 0.003 0.003 ↓ 0.0 0 1

Index Scan using assetdata_uidx on assetdata inspectedlength (cost=0.56..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((assetid = al_1.pipeid) AND (scenariocomponentid = al_1.scenariocomponentid) AND (assetdatatypeid = 678) AND (al_1.assetdatasourceid = assetdatasourceid))
49. 0.005 0.080 ↓ 1.5 3 1

Materialize (cost=0.00..9.20 rows=2 width=20) (actual time=0.024..0.080 rows=3 loops=1)

50. 0.075 0.075 ↓ 1.5 3 1

Seq Scan on assetdataoption ado2 (cost=0.00..9.19 rows=2 width=20) (actual time=0.020..0.075 rows=3 loops=1)

  • Filter: (assetdataoptionsetid = 10)
  • Rows Removed by Filter: 412
51.          

CTE input_array

52. 0.011 1.797 ↑ 2.0 1 1

HashAggregate (cost=0.05..0.08 rows=2 width=36) (actual time=1.797..1.797 rows=1 loops=1)

  • Group Key: input_data.grp
53. 1.786 1.786 ↑ 2.0 1 1

CTE Scan on input_data (cost=0.00..0.04 rows=2 width=36) (actual time=1.786..1.786 rows=1 loops=1)

54.          

CTE non_adjusted_service_lookup_scoring

55. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

56.          

CTE non_adjusted_structural_lookup_scoring

57. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

58.          

CTE adjusted_lookup_scoring

59. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

60.          

CTE lookup_structural_peak

61. 0.007 0.189 ↓ 5.0 5 1

WindowAgg (cost=11.22..11.25 rows=1 width=28) (actual time=0.183..0.189 rows=5 loops=1)

62. 0.009 0.182 ↓ 5.0 5 1

Sort (cost=11.22..11.22 rows=1 width=17) (actual time=0.181..0.182 rows=5 loops=1)

  • Sort Key: ((mro1.value)::double precision)
  • Sort Method: quicksort Memory: 25kB
63. 0.019 0.173 ↓ 5.0 5 1

Nested Loop (cost=4.80..11.21 rows=1 width=17) (actual time=0.160..0.173 rows=5 loops=1)

64. 0.016 0.064 ↓ 11.2 45 1

Hash Join (cost=4.52..8.65 rows=4 width=22) (actual time=0.048..0.064 rows=45 loops=1)

  • Hash Cond: (mro1.modelrunoptiontypeid = (mro2.modelrunoptiontypeid - 5))
65. 0.022 0.022 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro1 (cost=0.28..3.62 rows=50 width=13) (actual time=0.012..0.022 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
66. 0.015 0.026 ↑ 1.0 50 1

Hash (cost=3.62..3.62 rows=50 width=13) (actual time=0.026..0.026 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
67. 0.011 0.011 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro2 (cost=0.28..3.62 rows=50 width=13) (actual time=0.004..0.011 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
68. 0.090 0.090 ↓ 0.0 0 45

Index Scan using modelrunoptiontype_pk on modelrunoptiontype mrot1 (cost=0.28..0.63 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=45)

  • Index Cond: (modelrunoptiontypeid = mro1.modelrunoptiontypeid)
  • Filter: (((name)::text ~~* '%lower%'::text) AND ((description)::text = 'Peak Structural - Grading Threshold CCTV'::text))
  • Rows Removed by Filter: 1
69.          

CTE lookup_structural_mean

70. 0.028 0.295 ↓ 5.0 5 1

WindowAgg (cost=11.22..11.25 rows=1 width=28) (actual time=0.269..0.295 rows=5 loops=1)

71. 0.016 0.267 ↓ 5.0 5 1

Sort (cost=11.22..11.22 rows=1 width=17) (actual time=0.261..0.267 rows=5 loops=1)

  • Sort Key: ((mro1_1.value)::double precision)
  • Sort Method: quicksort Memory: 25kB
72. 0.029 0.251 ↓ 5.0 5 1

Nested Loop (cost=4.80..11.21 rows=1 width=17) (actual time=0.189..0.251 rows=5 loops=1)

73. 0.032 0.087 ↓ 11.2 45 1

Hash Join (cost=4.52..8.65 rows=4 width=22) (actual time=0.060..0.087 rows=45 loops=1)

  • Hash Cond: (mro1_1.modelrunoptiontypeid = (mro2_1.modelrunoptiontypeid - 5))
74. 0.012 0.012 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro1_1 (cost=0.28..3.62 rows=50 width=13) (actual time=0.005..0.012 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
75. 0.022 0.043 ↑ 1.0 50 1

Hash (cost=3.62..3.62 rows=50 width=13) (actual time=0.043..0.043 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
76. 0.021 0.021 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro2_1 (cost=0.28..3.62 rows=50 width=13) (actual time=0.005..0.021 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
77. 0.135 0.135 ↓ 0.0 0 45

Index Scan using modelrunoptiontype_pk on modelrunoptiontype mrot1_1 (cost=0.28..0.63 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=45)

  • Index Cond: (modelrunoptiontypeid = mro1_1.modelrunoptiontypeid)
  • Filter: (((name)::text ~~* '%lower%'::text) AND ((description)::text = 'Mean Structural - Grading Threshold CCTV'::text))
  • Rows Removed by Filter: 1
78.          

CTE lookup_service_peak

79. 0.030 0.327 ↓ 5.0 5 1

WindowAgg (cost=11.22..11.25 rows=1 width=28) (actual time=0.296..0.327 rows=5 loops=1)

80. 0.015 0.297 ↓ 5.0 5 1

Sort (cost=11.22..11.22 rows=1 width=17) (actual time=0.290..0.297 rows=5 loops=1)

  • Sort Key: ((mro1_2.value)::double precision)
  • Sort Method: quicksort Memory: 25kB
81. 0.004 0.282 ↓ 5.0 5 1

Nested Loop (cost=4.80..11.21 rows=1 width=17) (actual time=0.177..0.282 rows=5 loops=1)

82. 0.034 0.098 ↓ 11.2 45 1

Hash Join (cost=4.52..8.65 rows=4 width=22) (actual time=0.065..0.098 rows=45 loops=1)

  • Hash Cond: (mro1_2.modelrunoptiontypeid = (mro2_2.modelrunoptiontypeid - 5))
83. 0.024 0.024 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro1_2 (cost=0.28..3.62 rows=50 width=13) (actual time=0.011..0.024 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
84. 0.023 0.040 ↑ 1.0 50 1

Hash (cost=3.62..3.62 rows=50 width=13) (actual time=0.040..0.040 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
85. 0.017 0.017 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro2_2 (cost=0.28..3.62 rows=50 width=13) (actual time=0.003..0.017 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
86. 0.180 0.180 ↓ 0.0 0 45

Index Scan using modelrunoptiontype_pk on modelrunoptiontype mrot1_2 (cost=0.28..0.63 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=45)

  • Index Cond: (modelrunoptiontypeid = mro1_2.modelrunoptiontypeid)
  • Filter: (((name)::text ~~* '%lower%'::text) AND ((description)::text = 'Peak Service - Grading Threshold CCTV'::text))
  • Rows Removed by Filter: 1
87.          

CTE lookup_service_mean

88. 0.025 0.279 ↓ 5.0 5 1

WindowAgg (cost=11.22..11.25 rows=1 width=28) (actual time=0.253..0.279 rows=5 loops=1)

89. 0.009 0.254 ↓ 5.0 5 1

Sort (cost=11.22..11.22 rows=1 width=17) (actual time=0.249..0.254 rows=5 loops=1)

  • Sort Key: ((mro1_3.value)::double precision)
  • Sort Method: quicksort Memory: 25kB
90. 0.022 0.245 ↓ 5.0 5 1

Nested Loop (cost=4.80..11.21 rows=1 width=17) (actual time=0.104..0.245 rows=5 loops=1)

91. 0.035 0.088 ↓ 11.2 45 1

Hash Join (cost=4.52..8.65 rows=4 width=22) (actual time=0.054..0.088 rows=45 loops=1)

  • Hash Cond: (mro1_3.modelrunoptiontypeid = (mro2_3.modelrunoptiontypeid - 5))
92. 0.016 0.016 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro1_3 (cost=0.28..3.62 rows=50 width=13) (actual time=0.003..0.016 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
93. 0.014 0.037 ↑ 1.0 50 1

Hash (cost=3.62..3.62 rows=50 width=13) (actual time=0.037..0.037 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
94. 0.023 0.023 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro2_3 (cost=0.28..3.62 rows=50 width=13) (actual time=0.004..0.023 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
95. 0.135 0.135 ↓ 0.0 0 45

Index Scan using modelrunoptiontype_pk on modelrunoptiontype mrot1_3 (cost=0.28..0.63 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=45)

  • Index Cond: (modelrunoptiontypeid = mro1_3.modelrunoptiontypeid)
  • Filter: (((name)::text ~~* '%lower%'::text) AND ((description)::text = 'Mean Service - Grading Threshold CCTV'::text))
  • Rows Removed by Filter: 1
96.          

CTE lookup_structural_peak_modified

97. 0.031 0.285 ↓ 5.0 5 1

WindowAgg (cost=11.22..11.25 rows=1 width=28) (actual time=0.257..0.285 rows=5 loops=1)

98. 0.011 0.254 ↓ 5.0 5 1

Sort (cost=11.22..11.22 rows=1 width=17) (actual time=0.250..0.254 rows=5 loops=1)

  • Sort Key: ((mro1_4.value)::double precision)
  • Sort Method: quicksort Memory: 25kB
99. 0.020 0.243 ↓ 5.0 5 1

Nested Loop (cost=4.80..11.21 rows=1 width=17) (actual time=0.064..0.243 rows=5 loops=1)

100. 0.027 0.088 ↓ 11.2 45 1

Hash Join (cost=4.52..8.65 rows=4 width=22) (actual time=0.056..0.088 rows=45 loops=1)

  • Hash Cond: (mro1_4.modelrunoptiontypeid = (mro2_4.modelrunoptiontypeid - 5))
101. 0.021 0.021 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro1_4 (cost=0.28..3.62 rows=50 width=13) (actual time=0.004..0.021 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
102. 0.018 0.040 ↑ 1.0 50 1

Hash (cost=3.62..3.62 rows=50 width=13) (actual time=0.040..0.040 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
103. 0.022 0.022 ↑ 1.0 50 1

Index Scan using modelrunoption_idx on modelrunoption mro2_4 (cost=0.28..3.62 rows=50 width=13) (actual time=0.004..0.022 rows=50 loops=1)

  • Index Cond: (modelrunoptionsetid = 329)
104. 0.135 0.135 ↓ 0.0 0 45

Index Scan using modelrunoptiontype_pk on modelrunoptiontype mrot1_4 (cost=0.28..0.63 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=45)

  • Index Cond: (modelrunoptiontypeid = mro1_4.modelrunoptiontypeid)
  • Filter: (((name)::text ~~* '%lower%'::text) AND ((description)::text = 'Peak Structural Modified- Grading Threshold CCTV'::text))
  • Rows Removed by Filter: 1
105.          

CTE grading

106. 0.003 78,681.427 ↑ 1.0 1 1

Nested Loop (cost=0.80..74.06 rows=1 width=76) (actual time=308.758..78,681.427 rows=1 loops=1)

107. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on non_adjusted_structural_lookup_scoring (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

108. 0.005 78,681.421 ↑ 1.0 1 1

Nested Loop (cost=0.80..74.03 rows=1 width=76) (actual time=308.753..78,681.421 rows=1 loops=1)

  • Join Filter: ((nasts.peak_score_by_meter >= lsp.min) AND (nasts.peak_score_by_meter <= lsp.max))
  • Rows Removed by Join Filter: 4
109. 0.003 78,681.225 ↑ 1.0 1 1

Nested Loop (cost=0.80..73.99 rows=1 width=76) (actual time=308.564..78,681.225 rows=1 loops=1)

110. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on non_adjusted_service_lookup_scoring (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

111. 0.015 78,681.220 ↑ 1.0 1 1

Nested Loop (cost=0.80..73.96 rows=1 width=76) (actual time=308.561..78,681.220 rows=1 loops=1)

  • Join Filter: ((nasvs.peak_score_by_meter >= lsvp.min) AND (nasvs.peak_score_by_meter <= lsvp.max))
  • Rows Removed by Join Filter: 4
112. 0.330 0.330 ↓ 5.0 5 1

CTE Scan on lookup_service_peak lsvp (cost=0.00..0.02 rows=1 width=24) (actual time=0.297..0.330 rows=5 loops=1)

113. 0.095 78,680.875 ↑ 1.0 1 5

Nested Loop (cost=0.80..73.93 rows=1 width=76) (actual time=305.238..15,736.175 rows=1 loops=5)

  • Join Filter: ((nasvs.avg_score_by_meter >= lsvm.min) AND (nasvs.avg_score_by_meter <= lsvm.max))
  • Rows Removed by Join Filter: 4
114. 0.305 0.305 ↓ 5.0 5 5

CTE Scan on lookup_service_mean lsvm (cost=0.00..0.02 rows=1 width=24) (actual time=0.051..0.061 rows=5 loops=5)

115. 0.150 78,680.475 ↑ 5.0 1 25

Nested Loop (cost=0.80..73.83 rows=5 width=76) (actual time=304.758..3,147.219 rows=1 loops=25)

  • Join Filter: (ads_list.pipeid = nasvs.pipeid)
116. 0.075 76,141.750 ↑ 1.0 1 25

Nested Loop (cost=0.54..51.08 rows=1 width=100) (actual time=203.211..3,045.670 rows=1 loops=25)

117. 0.050 0.050 ↑ 1.0 1 25

CTE Scan on adjusted_lookup_scoring (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=25)

118. 0.500 76,141.625 ↑ 1.0 1 25

Nested Loop (cost=0.54..51.05 rows=1 width=100) (actual time=203.207..3,045.665 rows=1 loops=25)

  • Join Filter: ((asts.peak_score_by_meter >= lspm.min) AND (asts.peak_score_by_meter <= lspm.max))
  • Rows Removed by Join Filter: 4
119. 0.375 0.375 ↓ 5.0 5 25

CTE Scan on lookup_structural_peak_modified lspm (cost=0.00..0.02 rows=1 width=24) (actual time=0.011..0.015 rows=5 loops=25)

120. 0.750 76,140.750 ↑ 5.0 1 125

Nested Loop (cost=0.54..50.95 rows=5 width=92) (actual time=203.049..609.126 rows=1 loops=125)

  • Join Filter: (ads_list.pipeid = asts.pipeid)
121. 1.750 63,453.125 ↑ 1.0 1 125

Nested Loop (cost=0.29..28.20 rows=1 width=64) (actual time=101.549..507.625 rows=1 loops=125)

  • Join Filter: ((nasts.avg_score_by_meter >= lsm.min) AND (nasts.avg_score_by_meter <= lsm.max))
  • Rows Removed by Join Filter: 4
122. 0.750 0.750 ↓ 5.0 5 125

CTE Scan on lookup_structural_mean lsm (cost=0.00..0.02 rows=1 width=24) (actual time=0.003..0.006 rows=5 loops=125)

123. 1.875 63,450.625 ↑ 10.0 1 625

Nested Loop (cost=0.29..28.03 rows=10 width=64) (actual time=101.519..101.521 rows=1 loops=625)

124. 2.500 2.500 ↑ 2.0 1 625

CTE Scan on input_array ia (cost=0.00..0.04 rows=2 width=32) (actual time=0.003..0.004 rows=1 loops=625)

125. 3.117 63,446.250 ↑ 5.0 1 625

Hash Join (cost=0.29..14.09 rows=5 width=32) (actual time=101.513..101.514 rows=1 loops=625)

  • Hash Cond: (nasts.pipeid = ads_list.pipeid)
126. 63,443.125 63,443.125 ↑ 1,000.0 1 625

Function Scan on pyt_cctv_adjusted_scores nasts (cost=0.26..10.26 rows=1,000 width=20) (actual time=101.508..101.509 rows=1 loops=625)

127. 0.003 0.008 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
128. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on ads_list (cost=0.00..0.02 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1)

129. 12,686.875 12,686.875 ↑ 1,000.0 1 125

Function Scan on pyt_cctv_adjusted_scores asts (cost=0.26..10.26 rows=1,000 width=28) (actual time=101.495..101.495 rows=1 loops=125)

130. 2,538.575 2,538.575 ↑ 1,000.0 1 25

Function Scan on pyt_cctv_adjusted_scores nasvs (cost=0.26..10.26 rows=1,000 width=20) (actual time=101.542..101.543 rows=1 loops=25)

131. 0.191 0.191 ↓ 5.0 5 1

CTE Scan on lookup_structural_peak lsp (cost=0.00..0.02 rows=1 width=24) (actual time=0.185..0.191 rows=5 loops=1)

132. 0.013 78,681.477 ↑ 16.7 6 1

Sort (cost=4.86..5.11 rows=100 width=65) (actual time=78,681.474..78,681.477 rows=6 loops=1)

  • Sort Key: grading.pipeid, grading.scenariocomponentid, grading.assetdatasourceid, (unnest('{371,533,451,536,538,652}'::integer[]))
  • Sort Method: quicksort Memory: 25kB
133. 78,681.464 78,681.464 ↑ 16.7 6 1

CTE Scan on grading (cost=0.00..0.54 rows=100 width=65) (actual time=308.786..78,681.464 rows=6 loops=1)

Planning time : 37.878 ms
Execution time : 78,682.282 ms