explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ja9v

Settings
# exclusive inclusive rows x rows loops node
1. 354,853.133 354,853.133 ↓ 966.5 11,598 1

CTE Scan on raw_data (cost=137,644.65..137,644.89 rows=12 width=1,036) (actual time=343,352.104..354,853.133 rows=11,598 loops=1)

2.          

CTE entity_type_hier

3. 23.643 188.454 ↓ 18,141.0 18,141 1

Sort (cost=591.72..591.73 rows=1 width=98) (actual time=187.816..188.454 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.274 164.811 ↓ 18,141.0 18,141 1

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

5. 3.747 162.537 ↓ 18,141.0 18,141 1

Group (cost=591.68..591.70 rows=1 width=290) (actual time=158.051..162.537 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. 23.225 158.790 ↓ 18,141.0 18,141 1

Sort (cost=591.68..591.68 rows=1 width=226) (actual time=158.049..158.790 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.423 135.565 ↓ 18,141.0 18,141 1

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

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

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

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

CTE entity_type_rec

10. 14.035 108.227 ↓ 284.9 54,423 1

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

11. 0.012 0.063 ↑ 1.0 21 1

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

12. 0.025 0.044 ↑ 1.0 7 1

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

  • Recheck Cond: (super_type_id IS NULL)
  • Heap Blocks: exact=5
13. 0.019 0.019 ↑ 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.019..0.019 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.001..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. 22.451 94.129 ↓ 457.2 7,772 7

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

17. 13.268 17.255 ↓ 2,591.7 7,775 7

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

  • Hash Cond: (etr.langs_column_name = ""*VALUES*_1"".column1)
18. 3.983 3.983 ↓ 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.569 rows=7,775 loops=7)

19. 0.003 0.004 ↑ 1.0 3 1

Hash (cost=0.04..0.04 rows=3 width=32) (actual time=0.003..0.004 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.225 36.225 ↑ 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.032..36.225 rows=10,669 loops=1)

24.          

CTE action_resource_type_hist_price

25. 224.554 346.152 ↓ 1.1 323,678 1

HashAggregate (cost=20,445.57..23,422.97 rows=297,740 width=56) (actual time=246.835..346.152 rows=323,678 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. 17.682 121.598 ↓ 1.1 326,192 1

Append (cost=0.00..15,235.12 rows=297,740 width=56) (actual time=0.051..121.598 rows=326,192 loops=1)

27. 26.712 84.382 ↓ 1.0 297,447 1

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

28. 57.670 57.670 ↓ 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.048..57.670 rows=297,447 loops=1)

29. 5.064 13.339 ↓ 79.8 23,209 1

Hash Join (cost=980.71..1,661.09 rows=291 width=56) (actual time=5.452..13.339 rows=23,209 loops=1)

  • Hash Cond: (ar.type_id = rt_1.id)
30. 2.862 2.862 ↓ 1.0 28,747 1

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

31. 0.043 5.413 ↓ 1.5 162 1

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

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

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

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

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

  • Hash Cond: (ar_1.type_id = rt_2.id)
34. 1.781 1.781 ↓ 1.0 28,747 1

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

35. 0.006 1.681 ↓ 47.0 47 1

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

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

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

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

CTE activity_index_tasks

38. 0.032 3.171 ↓ 12.0 24 1

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

39. 0.009 3.043 ↓ 12.0 24 1

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

40. 2.958 2.958 ↑ 1.0 1 1

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

  • Filter: ((name)::text ~~* 'activity_index_%'::text)
  • Rows Removed by Filter: 2,660
41. 0.076 0.076 ↓ 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.006..0.076 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. 121.204 354,835.419 ↓ 966.5 11,598 1

WindowAgg (cost=109,385.56..109,392.79 rows=12 width=1,060) (actual time=343,352.100..354,835.419 rows=11,598 loops=1)

45. 25.561 343,162.607 ↓ 966.5 11,598 1

Sort (cost=109,385.56..109,385.59 rows=12 width=3,864) (actual time=343,160.689..343,162.607 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. 25.017 343,137.046 ↓ 966.5 11,598 1

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

47. 504.280 343,077.235 ↓ 966.5 11,598 1

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

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

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

49. 12.881 1,752.508 ↓ 966.5 11,598 1

Nested Loop Left Join (cost=76,088.70..84,712.73 rows=12 width=3,820) (actual time=1,148.934..1,752.508 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.258 1,739.627 ↓ 966.7 11,600 1

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

  • Join Filter: false
51. 14.987 1,727.369 ↓ 966.7 11,600 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

65. 161.126 1,438.749 ↓ 966.7 11,600 1

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

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

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

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

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

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

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

69. 1.934 17.100 ↓ 25.3 76 1

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

  • Group Key: l.id
70. 0.077 15.166 ↓ 32.3 97 1

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

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

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

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

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

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

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

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

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

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

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

  • Index Cond: (container_id = '72150085065900033'::bigint)
76. 621.072 621.072 ↓ 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.291..8.172 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.074 45.223 ↓ 7.2 383 1

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

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

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

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

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 204kB
81. 2.918 2.918 ↓ 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.004..2.918 rows=2,772 loops=1)

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

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 124kB
83. 2.887 2.887 ↓ 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..2.887 rows=1,735 loops=1)

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

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 207kB
85. 3.229 3.229 ↓ 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.012..3.229 rows=2,773 loops=1)

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

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 146kB
87. 1.683 1.683 ↓ 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.683 rows=1,679 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
89. 1.364 1.364 ↓ 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.002..1.364 rows=832 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
91. 1.236 1.236 ↑ 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.014..1.236 rows=48 loops=1)

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

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
95. 1.160 1.160 ↑ 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.217..1.160 rows=1 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
97. 1.147 1.147 ↑ 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.216..1.147 rows=1 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
99. 1.237 1.237 ↑ 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.216..1.237 rows=1 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
101. 1.096 1.096 ↑ 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.015..1.096 rows=1 loops=1)

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

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

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

CTE Scan on resource_type_loc rt_rent (cost=0.00..240.05 rows=53 width=40) (actual time=0.020..1.099 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. 340,702.848 340,702.848 ↑ 7.8 4 278,352

Seq Scan on task_type th (cost=0.00..2,045.72 rows=31 width=8) (actual time=0.236..1.224 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. 11,551.608 11,551.608 ↑ 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.340..0.996 rows=1 loops=11,598)

  • Filter: (id = pm.crop_id)
  • Rows Removed by Filter: 18,140
Planning time : 42.584 ms
Execution time : 354,863.508 ms