explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l2WP

Settings
# exclusive inclusive rows x rows loops node
1. 0.861 361,922.120 ↓ 281.4 16,882 1

Append (cost=137,645.19..137,651.87 rows=60 width=581) (actual time=361,817.180..361,922.120 rows=16,882 loops=1)

2.          

CTE entity_type_hier

3. 22.742 196.174 ↓ 18,141.0 18,141 1

Sort (cost=591.72..591.73 rows=1 width=98) (actual time=195.510..196.174 rows=18,141 loops=1)

  • Sort Key: (CASE WHEN ((crop.major IS NOT NULL) AND (crop.minor IS NOT NULL)) THEN ((crop.major || ' - '::text) || crop.minor) ELSE COALESCE(crop.major, crop.minor, ''::text) END)
  • Sort Method: quicksort Memory: 2,811kB
4. 2.402 173.432 ↓ 18,141.0 18,141 1

Subquery Scan on crop (cost=591.68..591.71 rows=1 width=98) (actual time=166.268..173.432 rows=18,141 loops=1)

5. 3.896 171.030 ↓ 18,141.0 18,141 1

Group (cost=591.68..591.70 rows=1 width=290) (actual time=166.267..171.030 rows=18,141 loops=1)

  • Group Key: crop_1.major_name, crop_1.sub_fam_name, crop_1.family_name, crop_1.minor_name, crop_1.id, crop_1.code, crop_1.custom_name
6. 24.153 167.134 ↓ 18,141.0 18,141 1

Sort (cost=591.68..591.68 rows=1 width=226) (actual time=166.265..167.134 rows=18,141 loops=1)

  • Sort Key: crop_1.major_name, crop_1.sub_fam_name, crop_1.family_name, crop_1.minor_name, crop_1.id, crop_1.code, crop_1.custom_name
  • Sort Method: quicksort Memory: 2,984kB
7. 1.381 142.981 ↓ 18,141.0 18,141 1

Subquery Scan on crop_1 (cost=587.36..591.67 rows=1 width=226) (actual time=0.049..142.981 rows=18,141 loops=1)

8. 141.600 141.600 ↓ 18,141.0 18,141 1

CTE Scan on entity_type_rec (cost=587.36..591.66 rows=1 width=390) (actual time=0.049..141.600 rows=18,141 loops=1)

  • Filter: (langs_column_name = 'name'::text)
  • Rows Removed by Filter: 36,282
9.          

CTE entity_type_rec

10. 14.762 108.482 ↓ 284.9 54,423 1

Recursive Union (cost=4.34..587.36 rows=191 width=342) (actual time=0.047..108.482 rows=54,423 loops=1)

11. 0.011 0.074 ↑ 1.0 21 1

Nested Loop (cost=4.34..32.90 rows=21 width=342) (actual time=0.045..0.074 rows=21 loops=1)

12. 0.030 0.056 ↑ 1.0 7 1

Bitmap Heap Scan on entity_type et (cost=4.34..30.76 rows=7 width=93) (actual time=0.039..0.056 rows=7 loops=1)

  • Recheck Cond: (super_type_id IS NULL)
  • Heap Blocks: exact=5
13. 0.026 0.026 ↑ 1.0 7 1

Bitmap Index Scan on entity_type_super_type_id_index (cost=0.00..4.34 rows=7 width=0) (actual time=0.026..0.026 rows=7 loops=1)

  • Index Cond: (super_type_id IS NULL)
14. 0.004 0.007 ↑ 1.0 3 7

Materialize (cost=0.00..0.05 rows=3 width=32) (actual time=0.000..0.001 rows=3 loops=7)

15. 0.003 0.003 ↑ 1.0 3 1

Values Scan on ""*VALUES*"" (cost=0.00..0.04 rows=3 width=32) (actual time=0.002..0.003 rows=3 loops=1)

16. 21.989 93.646 ↓ 457.2 7,772 7

Nested Loop (cost=0.36..55.06 rows=17 width=342) (actual time=0.279..13.378 rows=7,772 loops=7)

17. 13.276 17.234 ↓ 2,591.7 7,775 7

Hash Join (cost=0.08..5.09 rows=3 width=268) (actual time=0.002..2.462 rows=7,775 loops=7)

  • Hash Cond: (etr.langs_column_name = ""*VALUES*_1"".column1)
18. 3.955 3.955 ↓ 37.0 7,775 7

WorkTable Scan on entity_type_rec etr (cost=0.00..4.20 rows=210 width=268) (actual time=0.000..0.565 rows=7,775 loops=7)

19. 0.002 0.003 ↑ 1.0 3 1

Hash (cost=0.04..0.04 rows=3 width=32) (actual time=0.002..0.003 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.001 0.001 ↑ 1.0 3 1

Values Scan on ""*VALUES*_1"" (cost=0.00..0.04 rows=3 width=32) (actual time=0.001..0.001 rows=3 loops=1)

21. 54.423 54.423 ↑ 5.0 1 54,423

Index Scan using entity_type_super_type_id_index on entity_type et_1 (cost=0.29..16.11 rows=5 width=101) (actual time=0.001..0.001 rows=1 loops=54,423)

  • Index Cond: (super_type_id = etr.id)
22.          

CTE resource_type_loc

23. 36.472 36.472 ↑ 1.0 10,669 1

Seq Scan on resource_type rt (cost=0.00..3,619.94 rows=10,669 width=3,147) (actual time=0.024..36.472 rows=10,669 loops=1)

24.          

CTE action_resource_type_hist_price

25. 220.735 325.965 ↓ 1.1 323,675 1

HashAggregate (cost=20,445.57..23,422.97 rows=297,740 width=56) (actual time=225.531..325.965 rows=323,675 loops=1)

  • Group Key: ""*SELECT* 1"".action_id, ""*SELECT* 1"".usage, ""*SELECT* 1"".cost, ""*SELECT* 1"".conc, ""*SELECT* 1"".resource_id, ""*SELECT* 1"".price_hist, ((0)::bigint)
26. 15.177 105.230 ↓ 1.1 326,189 1

Append (cost=0.00..15,235.12 rows=297,740 width=56) (actual time=0.023..105.230 rows=326,189 loops=1)

27. 21.553 72.771 ↓ 1.0 297,447 1

Subquery Scan on "*SELECT* 1" (cost=0.00..11,912.77 rows=297,446 width=56) (actual time=0.023..72.771 rows=297,447 loops=1)

28. 51.218 51.218 ↓ 1.0 297,447 1

Seq Scan on action_resource_type art (cost=0.00..8,194.69 rows=297,446 width=52) (actual time=0.022..51.218 rows=297,447 loops=1)

29. 4.569 11.230 ↓ 79.7 23,206 1

Hash Join (cost=980.71..1,661.09 rows=291 width=56) (actual time=3.984..11.230 rows=23,206 loops=1)

  • Hash Cond: (ar.type_id = rt_1.id)
30. 2.702 2.702 ↑ 1.0 28,744 1

Seq Scan on action_resource ar (cost=0.00..603.44 rows=28,744 width=48) (actual time=0.009..2.702 rows=28,744 loops=1)

31. 0.021 3.959 ↓ 1.5 162 1

Hash (cost=979.36..979.36 rows=108 width=8) (actual time=3.958..3.959 rows=162 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
32. 3.938 3.938 ↓ 1.5 162 1

Seq Scan on resource_type rt_1 (cost=0.00..979.36 rows=108 width=8) (actual time=0.423..3.938 rows=162 loops=1)

  • Filter: ((code)::text ~~ 'TL%'::text)
  • Rows Removed by Filter: 10,507
33. 2.736 6.052 ↓ 1,845.3 5,536 1

Hash Join (cost=979.38..1,658.32 rows=3 width=56) (actual time=1.721..6.052 rows=5,536 loops=1)

  • Hash Cond: (ar_1.type_id = rt_2.id)
34. 1.636 1.636 ↑ 1.0 28,744 1

Seq Scan on action_resource ar_1 (cost=0.00..603.44 rows=28,744 width=48) (actual time=0.005..1.636 rows=28,744 loops=1)

35. 0.008 1.680 ↓ 47.0 47 1

Hash (cost=979.36..979.36 rows=1 width=8) (actual time=1.680..1.680 rows=47 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 1.672 1.672 ↓ 47.0 47 1

Seq Scan on resource_type rt_2 (cost=0.00..979.36 rows=1 width=8) (actual time=0.169..1.672 rows=47 loops=1)

  • Filter: ((code)::text ~~ 'WR%'::text)
  • Rows Removed by Filter: 10,622
37.          

CTE activity_index_tasks

38. 0.023 4.034 ↓ 12.0 24 1

Nested Loop (cost=0.56..617.22 rows=2 width=8) (actual time=1.938..4.034 rows=24 loops=1)

39. 0.005 3.915 ↓ 12.0 24 1

Nested Loop (cost=0.28..616.55 rows=2 width=8) (actual time=1.909..3.915 rows=24 loops=1)

40. 3.837 3.837 ↑ 1.0 1 1

Seq Scan on property_definition pd (cost=0.00..605.26 rows=1 width=8) (actual time=1.903..3.837 rows=1 loops=1)

  • Filter: ((name)::text ~~* 'activity_index_%'::text)
  • Rows Removed by Filter: 2,660
41. 0.073 0.073 ↓ 8.0 24 1

Index Scan using task_property_def_property_id_idx on task_property_def tpd (cost=0.28..11.26 rows=3 width=16) (actual time=0.005..0.073 rows=24 loops=1)

  • Index Cond: (property_id = pd.id)
42. 0.096 0.096 ↑ 1.0 1 24

Index Only Scan using task_type_pkey on task_type tt (cost=0.28..0.34 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=24)

  • Index Cond: (id = tpd.owner_id)
  • Heap Fetches: 0
43.          

CTE raw_data

44. 154.717 361,752.383 ↓ 966.5 11,598 1

WindowAgg (cost=109,385.56..109,392.79 rows=12 width=1,060) (actual time=349,279.678..361,752.383 rows=11,598 loops=1)

45. 28.363 349,083.424 ↓ 966.5 11,598 1

Sort (cost=109,385.56..109,385.59 rows=12 width=3,864) (actual time=349,080.857..349,083.424 rows=11,598 loops=1)

  • Sort Key: pm.group_id, art_1.resource_id, art_1.resource_low_id
  • Sort Method: quicksort Memory: 3,468kB
46. 23.558 349,055.061 ↓ 966.5 11,598 1

Nested Loop Left Join (cost=76,089.26..109,385.35 rows=12 width=3,864) (actual time=1,137.371..349,055.061 rows=11,598 loops=1)

47. 579.980 348,996.709 ↓ 966.5 11,598 1

Nested Loop Left Join (cost=76,088.98..109,324.10 rows=12 width=3,820) (actual time=1,137.360..348,996.709 rows=11,598 loops=1)

  • Join Filter: (SubPlan 7)
  • Rows Removed by Join Filter: 266,923
48. 22.503 1,798.901 ↓ 966.5 11,598 1

Nested Loop Left Join (cost=76,088.98..84,773.91 rows=12 width=3,821) (actual time=1,124.833..1,798.901 rows=11,598 loops=1)

49. 12.910 1,753.202 ↓ 966.5 11,598 1

Nested Loop Left Join (cost=76,088.70..84,712.73 rows=12 width=3,820) (actual time=1,124.800..1,753.202 rows=11,598 loops=1)

  • Join Filter: false
  • Filter: (COALESCE(rt_adj.id, rt_ins.id, rt_fun.id, rt_her.id, rt_fer_all.id, rt_fer_foliar.id, rt_fer_ground.id, rt_seed.id, rt_irr_cst.id, rt_fuel_cst.id, rt_offc_cst.id, rt_prsnl_cst.id, rt_rent.id, id, id) IS NOT NULL)
  • Rows Removed by Filter: 2
50. 12.540 1,740.292 ↓ 966.7 11,600 1

Nested Loop Left Join (cost=76,088.70..84,712.61 rows=12 width=3,780) (actual time=1,124.799..1,740.292 rows=11,600 loops=1)

  • Join Filter: false
51. 13.742 1,727.752 ↓ 966.7 11,600 1

Hash Left Join (cost=76,088.70..84,712.49 rows=12 width=3,740) (actual time=1,124.797..1,727.752 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_rent.id)
52. 10.729 1,712.924 ↓ 966.7 11,600 1

Hash Left Join (cost=75,847.98..84,471.64 rows=12 width=3,700) (actual time=1,123.707..1,712.924 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_prsnl_cst.id)
53. 11.183 1,701.092 ↓ 966.7 11,600 1

Hash Left Join (cost=75,607.27..84,230.79 rows=12 width=3,660) (actual time=1,122.602..1,701.092 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_offc_cst.id)
54. 10.508 1,688.823 ↓ 966.7 11,600 1

Hash Left Join (cost=75,366.55..83,989.94 rows=12 width=3,620) (actual time=1,121.513..1,688.823 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_fuel_cst.id)
55. 9.468 1,677.232 ↓ 966.7 11,600 1

Hash Left Join (cost=75,125.84..83,749.09 rows=12 width=3,580) (actual time=1,120.427..1,677.232 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_irr_cst.id)
56. 9.314 1,666.633 ↓ 966.7 11,600 1

Hash Left Join (cost=74,885.12..83,508.24 rows=12 width=3,540) (actual time=1,119.293..1,666.633 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_seed.id)
57. 9.826 1,656.053 ↓ 966.7 11,600 1

Hash Left Join (cost=74,644.41..83,267.39 rows=12 width=2,984) (actual time=1,118.024..1,656.053 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_fer_ground.id)
58. 10.142 1,645.097 ↓ 966.7 11,600 1

Hash Left Join (cost=74,403.69..83,026.54 rows=12 width=2,976) (actual time=1,116.891..1,645.097 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_fer_foliar.id)
59. 9.889 1,633.653 ↓ 966.7 11,600 1

Hash Left Join (cost=74,162.98..82,785.69 rows=12 width=2,968) (actual time=1,115.580..1,633.653 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_fer_all.id)
60. 11.395 1,622.046 ↓ 966.7 11,600 1

Hash Left Join (cost=73,922.26..82,544.84 rows=12 width=2,412) (actual time=1,113.857..1,622.046 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_her.id)
61. 12.503 1,607.151 ↓ 966.7 11,600 1

Hash Left Join (cost=73,640.20..82,262.41 rows=12 width=1,856) (actual time=1,110.353..1,607.151 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_fun.id)
62. 12.534 1,591.303 ↓ 966.7 11,600 1

Hash Left Join (cost=73,358.14..81,979.98 rows=12 width=1,300) (actual time=1,107.005..1,591.303 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_ins.id)
63. 13.402 1,575.596 ↓ 966.7 11,600 1

Hash Left Join (cost=73,076.08..81,697.55 rows=12 width=744) (actual time=1,103.827..1,575.596 rows=11,600 loops=1)

  • Hash Cond: (art_1.resource_id = rt_adj.id)
64. 22.521 1,516.283 ↓ 966.7 11,600 1

Nested Loop (cost=72,808.69..81,430.03 rows=12 width=188) (actual time=1,057.909..1,516.283 rows=11,600 loops=1)

65. 165.736 1,435.762 ↓ 966.7 11,600 1

Hash Join (cost=72,808.41..81,368.52 rows=12 width=196) (actual time=1,057.891..1,435.762 rows=11,600 loops=1)

  • Hash Cond: (art_1.action_id = pm.id)
66. 437.771 437.771 ↑ 1.0 292,591 1

CTE Scan on action_resource_type_hist_price art_1 (cost=0.00..7,443.50 rows=297,733 width=56) (actual time=225.533..437.771 rows=292,591 loops=1)

  • Filter: ((usage <> '0'::double precision) OR (cost <> '0'::double precision))
  • Rows Removed by Filter: 31,084
67. 147.840 832.255 ↓ 158.0 525,822 1

Hash (cost=72,766.79..72,766.79 rows=3,329 width=148) (actual time=832.252..832.255 rows=525,822 loops=1)

  • Buckets: 524,288 (originally 4096) Batches: 2 (originally 1) Memory Usage: 61,441kB
68. 46.239 684.415 ↓ 158.0 525,822 1

Nested Loop (cost=4,585.45..72,766.79 rows=3,329 width=148) (actual time=16.764..684.415 rows=525,822 loops=1)

69. 1.593 17.864 ↓ 25.3 76 1

GroupAggregate (cost=4,584.88..4,585.71 rows=3 width=96) (actual time=16.584..17.864 rows=76 loops=1)

  • Group Key: l.id
70. 0.071 16.271 ↓ 32.3 97 1

Sort (cost=4,584.88..4,584.89 rows=3 width=63) (actual time=16.234..16.271 rows=97 loops=1)

  • Sort Key: l.id
  • Sort Method: quicksort Memory: 38kB
71. 0.047 16.200 ↓ 32.3 97 1

Hash Join (cost=1,499.01..4,584.86 rows=3 width=63) (actual time=6.597..16.200 rows=97 loops=1)

  • Hash Cond: (season.location_id = l.id)
72. 15.090 15.090 ↑ 1.2 184 1

Seq Scan on entity season (cost=0.00..3,085.26 rows=221 width=27) (actual time=5.521..15.090 rows=184 loops=1)

  • Filter: ((properties -> 'p:season_year'::text) = '2019-2020'::text)
  • Rows Removed by Filter: 43,967
73. 0.103 1.063 ↑ 1.0 642 1

Hash (cost=1,490.99..1,490.99 rows=642 width=44) (actual time=1.062..1.063 rows=642 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
74. 0.572 0.960 ↑ 1.0 642 1

Bitmap Heap Scan on location l (cost=25.27..1,490.99 rows=642 width=44) (actual time=0.414..0.960 rows=642 loops=1)

  • Recheck Cond: (container_id = '72150085065900033'::bigint)
  • Heap Blocks: exact=104
75. 0.388 0.388 ↑ 1.0 642 1

Bitmap Index Scan on location_container_id_index (cost=0.00..25.10 rows=642 width=0) (actual time=0.388..0.388 rows=642 loops=1)

  • Index Cond: (container_id = '72150085065900033'::bigint)
76. 620.312 620.312 ↓ 6.2 6,919 76

Index Scan using pest_measurement_location_id_idx on pest_measurement pm (cost=0.57..22,715.92 rows=1,110 width=76) (actual time=0.324..8.162 rows=6,919 loops=76)

  • Index Cond: (location_id = l.id)
  • Filter: ((plan_status = ANY ('{0,7}'::integer[])) AND (start_time >= COALESCE((min(COALESCE(season.active_from, '1900-01-01'::date))), '1900-01-01'::date)) AND (start_time <= COALESCE((max(COALESCE(season.active_until, '2100-01-01'::date))), '2100-01-01'::date)))
  • Rows Removed by Filter: 10,780
77. 58.000 58.000 ↑ 1.0 1 11,600

Index Scan using entity_type_pkey on entity_type et_2 (cost=0.29..5.13 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=11,600)

  • Index Cond: (id = pm.pest_id)
  • Filter: ((code)::text ~~ '%'::text)
78. 0.070 45.911 ↓ 7.2 383 1

Hash (cost=266.73..266.73 rows=53 width=556) (actual time=45.911..45.911 rows=383 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
79. 45.841 45.841 ↓ 7.2 383 1

CTE Scan on resource_type_loc rt_adj (cost=0.00..266.73 rows=53 width=556) (actual time=0.048..45.841 rows=383 loops=1)

  • Filter: (substr((code)::text, 1, 5) = 'MT203'::text)
  • Rows Removed by Filter: 10,286
80. 0.335 3.173 ↓ 17.3 2,772 1

Hash (cost=280.06..280.06 rows=160 width=556) (actual time=3.173..3.173 rows=2,772 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 204kB
81. 2.838 2.838 ↓ 17.3 2,772 1

CTE Scan on resource_type_loc rt_ins (cost=0.00..280.06 rows=160 width=556) (actual time=0.003..2.838 rows=2,772 loops=1)

  • Filter: (substr((code)::text, 1, 7) = ANY ('{MT20000,MT20100,MT20200}'::text[]))
  • Rows Removed by Filter: 7,897
82. 0.255 3.345 ↓ 10.8 1,735 1

Hash (cost=280.06..280.06 rows=160 width=556) (actual time=3.344..3.345 rows=1,735 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 124kB
83. 3.090 3.090 ↓ 10.8 1,735 1

CTE Scan on resource_type_loc rt_fun (cost=0.00..280.06 rows=160 width=556) (actual time=0.009..3.090 rows=1,735 loops=1)

  • Filter: (substr((code)::text, 1, 7) = ANY ('{MT20001,MT20101,MT20201}'::text[]))
  • Rows Removed by Filter: 8,934
84. 0.382 3.500 ↓ 17.3 2,773 1

Hash (cost=280.06..280.06 rows=160 width=556) (actual time=3.499..3.500 rows=2,773 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 207kB
85. 3.118 3.118 ↓ 17.3 2,773 1

CTE Scan on resource_type_loc rt_her (cost=0.00..280.06 rows=160 width=556) (actual time=0.010..3.118 rows=2,773 loops=1)

  • Filter: (substr((code)::text, 1, 6) = ANY ('{MT2001,MT2011,MT2021}'::text[]))
  • Rows Removed by Filter: 7,896
86. 0.220 1.718 ↓ 31.7 1,679 1

Hash (cost=240.05..240.05 rows=53 width=556) (actual time=1.717..1.718 rows=1,679 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 146kB
87. 1.498 1.498 ↓ 31.7 1,679 1

CTE Scan on resource_type_loc rt_fer_all (cost=0.00..240.05 rows=53 width=556) (actual time=0.001..1.498 rows=1,679 loops=1)

  • Filter: ((code)::text ~~ 'MT13%'::text)
  • Rows Removed by Filter: 8,990
88. 0.075 1.302 ↓ 15.7 832 1

Hash (cost=240.05..240.05 rows=53 width=8) (actual time=1.301..1.302 rows=832 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
89. 1.227 1.227 ↓ 15.7 832 1

CTE Scan on resource_type_loc rt_fer_foliar (cost=0.00..240.05 rows=53 width=8) (actual time=0.001..1.227 rows=832 loops=1)

  • Filter: ((code)::text ~~ 'MT13A%'::text)
  • Rows Removed by Filter: 9,837
90. 0.006 1.130 ↑ 1.1 48 1

Hash (cost=240.05..240.05 rows=53 width=8) (actual time=1.130..1.130 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
91. 1.124 1.124 ↑ 1.1 48 1

CTE Scan on resource_type_loc rt_fer_ground (cost=0.00..240.05 rows=53 width=8) (actual time=0.013..1.124 rows=48 loops=1)

  • Filter: ((code)::text ~~ 'MT13B%'::text)
  • Rows Removed by Filter: 10,621
92. 0.070 1.266 ↓ 10.2 541 1

Hash (cost=240.05..240.05 rows=53 width=556) (actual time=1.266..1.266 rows=541 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
93. 1.196 1.196 ↓ 10.2 541 1

CTE Scan on resource_type_loc rt_seed (cost=0.00..240.05 rows=53 width=556) (actual time=0.001..1.196 rows=541 loops=1)

  • Filter: ((code)::text ~~ 'MT14%'::text)
  • Rows Removed by Filter: 10,128
94. 0.001 1.131 ↑ 53.0 1 1

Hash (cost=240.05..240.05 rows=53 width=40) (actual time=1.131..1.131 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
95. 1.130 1.130 ↑ 53.0 1 1

CTE Scan on resource_type_loc rt_irr_cst (cost=0.00..240.05 rows=53 width=40) (actual time=0.214..1.130 rows=1 loops=1)

  • Filter: ((code)::text ~~ 'MT2041'::text)
  • Rows Removed by Filter: 10,668
96. 0.001 1.083 ↑ 53.0 1 1

Hash (cost=240.05..240.05 rows=53 width=40) (actual time=1.083..1.083 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
97. 1.082 1.082 ↑ 53.0 1 1

CTE Scan on resource_type_loc rt_fuel_cst (cost=0.00..240.05 rows=53 width=40) (actual time=0.203..1.082 rows=1 loops=1)

  • Filter: ((code)::text ~~ 'MT1605'::text)
  • Rows Removed by Filter: 10,668
98. 0.001 1.086 ↑ 53.0 1 1

Hash (cost=240.05..240.05 rows=53 width=40) (actual time=1.086..1.086 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
99. 1.085 1.085 ↑ 53.0 1 1

CTE Scan on resource_type_loc rt_offc_cst (cost=0.00..240.05 rows=53 width=40) (actual time=0.200..1.085 rows=1 loops=1)

  • Filter: ((code)::text ~~ 'WO2030'::text)
  • Rows Removed by Filter: 10,668
100. 0.001 1.103 ↑ 53.0 1 1

Hash (cost=240.05..240.05 rows=53 width=40) (actual time=1.103..1.103 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
101. 1.102 1.102 ↑ 53.0 1 1

CTE Scan on resource_type_loc rt_prsnl_cst (cost=0.00..240.05 rows=53 width=40) (actual time=0.014..1.102 rows=1 loops=1)

  • Filter: ((code)::text ~~ 'WO2040'::text)
  • Rows Removed by Filter: 10,668
102. 0.001 1.086 ↑ 53.0 1 1

Hash (cost=240.05..240.05 rows=53 width=40) (actual time=1.086..1.086 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
103. 1.085 1.085 ↑ 53.0 1 1

CTE Scan on resource_type_loc rt_rent (cost=0.00..240.05 rows=53 width=40) (actual time=0.019..1.085 rows=1 loops=1)

  • Filter: ((code)::text ~~ 'WO2050'::text)
  • Rows Removed by Filter: 10,668
104. 0.000 0.000 ↓ 0.0 0 11,600

Result (cost=0.00..0.00 rows=0 width=40) (actual time=0.000..0.000 rows=0 loops=11,600)

  • One-Time Filter: false
105. 0.000 0.000 ↓ 0.0 0 11,600

Result (cost=0.00..0.00 rows=0 width=40) (actual time=0.000..0.000 rows=0 loops=11,600)

  • One-Time Filter: false
106. 23.196 23.196 ↑ 1.0 1 11,598

Index Scan using task_type_pkey on task_type tt_1 (cost=0.28..5.10 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=11,598)

  • Index Cond: (id = pm.type_id)
107. 69.588 69.588 ↓ 12.0 24 11,598

CTE Scan on activity_index_tasks ait (cost=0.00..0.04 rows=2 width=8) (actual time=0.000..0.006 rows=24 loops=11,598)

108.          

SubPlan (for Nested Loop Left Join)

109. 346,548.240 346,548.240 ↑ 7.8 4 278,352

Seq Scan on task_type th (cost=0.00..2,045.72 rows=31 width=8) (actual time=0.246..1.245 rows=4 loops=278,352)

  • Filter: ((tt_1.code)::text ~~ ((code)::text || '%'::text))
  • Rows Removed by Filter: 5,917
110. 34.794 34.794 ↑ 1.0 1 11,598

Index Scan using contact_info_pkey on contact_info ci (cost=0.28..5.10 rows=1 width=60) (actual time=0.003..0.003 rows=1 loops=11,598)

  • Index Cond: (id = pm.user_id)
111.          

SubPlan (for WindowAgg)

112. 12,514.242 12,514.242 ↑ 1.0 1 11,598

CTE Scan on entity_type_hier crop_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.373..1.079 rows=1 loops=11,598)

  • Filter: (id = pm.crop_id)
  • Rows Removed by Filter: 18,140
113. 4.366 361,828.322 ↓ 1.2 14 1

GroupAggregate (cost=0.55..1.93 rows=12 width=484) (actual time=361,817.180..361,828.322 rows=14 loops=1)

  • Group Key: x.crop_name
114. 6.445 361,823.956 ↓ 966.5 11,598 1

WindowAgg (cost=0.55..0.91 rows=12 width=1,076) (actual time=361,816.939..361,823.956 rows=11,598 loops=1)

115. 37.537 361,817.511 ↓ 966.5 11,598 1

Sort (cost=0.55..0.58 rows=12 width=248) (actual time=361,816.933..361,817.511 rows=11,598 loops=1)

  • Sort Key: x.crop_name, x.plot_name, (((x.pm_id)::text || (x.material_name)::text))
  • Sort Method: quicksort Memory: 3,468kB
116. 361,779.974 361,779.974 ↓ 966.5 11,598 1

CTE Scan on raw_data x (cost=0.00..0.33 rows=12 width=248) (actual time=349,279.682..361,779.974 rows=11,598 loops=1)

117. 17.217 21.159 ↓ 966.5 11,598 1

Sort (cost=0.46..0.49 rows=12 width=968) (actual time=20.689..21.159 rows=11,598 loops=1)

  • Sort Key: x_1.start_time, x_1.plot_name, x_1.crop_name, x_1.material_type, x_1.material_name, x_1.material_price, x_1.dose, x_1.cost_ha_raw, x_1.total_use, x_1.total_money
  • Sort Method: quicksort Memory: 3,469kB
118. 3.942 3.942 ↓ 966.5 11,598 1

CTE Scan on raw_data x_1 (cost=0.00..0.24 rows=12 width=968) (actual time=0.002..3.942 rows=11,598 loops=1)

119. 0.490 25.328 ↓ 262.6 3,151 1

Subquery Scan on "*SELECT* 3" (cost=0.46..1.54 rows=12 width=484) (actual time=14.735..25.328 rows=3,151 loops=1)

120. 9.592 24.838 ↓ 262.6 3,151 1

GroupAggregate (cost=0.46..1.42 rows=12 width=492) (actual time=14.734..24.838 rows=3,151 loops=1)

  • Group Key: x_2.time_period_day, x_2.plot_name, x_2.crop_name, x_2.location_id
121. 11.867 15.246 ↓ 966.5 11,598 1

Sort (cost=0.46..0.49 rows=12 width=224) (actual time=14.722..15.246 rows=11,598 loops=1)

  • Sort Key: x_2.time_period_day, x_2.plot_name, x_2.crop_name, x_2.location_id
  • Sort Method: quicksort Memory: 3,465kB
122. 3.379 3.379 ↓ 966.5 11,598 1

CTE Scan on raw_data x_2 (cost=0.00..0.24 rows=12 width=224) (actual time=0.001..3.379 rows=11,598 loops=1)

123. 0.305 24.752 ↓ 168.9 2,027 1

Subquery Scan on "*SELECT* 4" (cost=0.46..1.54 rows=12 width=484) (actual time=16.118..24.752 rows=2,027 loops=1)

124. 7.860 24.447 ↓ 168.9 2,027 1

GroupAggregate (cost=0.46..1.42 rows=12 width=492) (actual time=16.117..24.447 rows=2,027 loops=1)

  • Group Key: x_3.time_period_week, x_3.plot_name, x_3.crop_name, x_3.location_id
125. 13.141 16.587 ↓ 966.5 11,598 1

Sort (cost=0.46..0.49 rows=12 width=224) (actual time=16.105..16.587 rows=11,598 loops=1)

  • Sort Key: x_3.time_period_week, x_3.plot_name, x_3.crop_name, x_3.location_id
  • Sort Method: quicksort Memory: 3,465kB
126. 3.446 3.446 ↓ 966.5 11,598 1

CTE Scan on raw_data x_3 (cost=0.00..0.24 rows=12 width=224) (actual time=0.001..3.446 rows=11,598 loops=1)

127. 0.018 21.698 ↓ 7.7 92 1

Subquery Scan on "*SELECT* 5" (cost=0.46..1.51 rows=12 width=484) (actual time=15.195..21.698 rows=92 loops=1)

128. 5.918 21.680 ↓ 7.7 92 1

GroupAggregate (cost=0.46..1.39 rows=12 width=492) (actual time=15.194..21.680 rows=92 loops=1)

  • Group Key: x_4.plot_name, x_4.crop_name, x_4.location_id
129. 12.441 15.762 ↓ 966.5 11,598 1

Sort (cost=0.46..0.49 rows=12 width=216) (actual time=15.151..15.762 rows=11,598 loops=1)

  • Sort Key: x_4.plot_name, x_4.crop_name, x_4.location_id
  • Sort Method: quicksort Memory: 3,465kB
130. 3.321 3.321 ↓ 966.5 11,598 1

CTE Scan on raw_data x_4 (cost=0.00..0.24 rows=12 width=216) (actual time=0.001..3.321 rows=11,598 loops=1)

Planning time : 17.319 ms
Execution time : 361,935.099 ms