explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D5tb

Settings
# exclusive inclusive rows x rows loops node
1. 7.155 2,127.692 ↑ 14.1 993 1

GroupAggregate (cost=13,497.73..5,237,589.57 rows=13,975 width=550) (actual time=838.298..2,127.692 rows=993 loops=1)

  • Group Key: tb_init_master_3.project_id, tb_init_master_3.project_desc, tb_init_master_3.group_record_type, tb_init_master_3.project_title, tb_init_master_3.stage, dim_project_type.project_type, dim_category_3.category, (CASE WHEN (hier_1.level = 0) THEN tax_1.element WHEN (hier_1.level = 1) THEN tax_1.element ELSE split_part(array_to_string(hier_1.name_path, '->'::text), '->'::text, 2) END), tb_init_master_3.manual_status, dim_value_type.value_type_name, tb_init_master_3.group_id, tb_init_master_3.in_scope, (count(tb_raid.raid_id)), e.max_actual, e.max_forecasted, (CASE WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values) AND ((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric)) THEN 'Allocation Mismatch, Latest Estimate Missing'::text WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values) AND (vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Allocation Mismatch, Stretch Goal Understated'::text WHEN (((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric) AND (vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Latest Estimate Missing, Stretch Goal Understated'::text WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values) AND ((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric) AND (vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Allocation Mismatch, Latest Estimate Missing, Stretch Goal Understated'::text WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values)) THEN 'Allocation Mismatch'::text WHEN (((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric)) THEN 'Latest Estimate Missing'::text WHEN ((vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Stretch Goal Understated'::text ELSE NULL::text END), (CASE WHEN (vw_warning_callouts.date_count > 0) THEN 'Future Actual Date'::text ELSE NULL::text END), (CASE WHEN (tb_init_master_3.stage = 'Cancelled Project'::text) THEN 'Cancelled Project'::text WHEN (tb_init_master_3.stage = 'Completed Project'::text) THEN 'Completed Project'::text ELSE c."project status" END)
2. 32.713 745.058 ↑ 3.6 3,918 1

Sort (cost=13,497.73..13,532.66 rows=13,975 width=474) (actual time=744.514..745.058 rows=3,918 loops=1)

  • Sort Key: tb_init_master_3.project_id, tb_init_master_3.project_desc, tb_init_master_3.group_record_type, tb_init_master_3.project_title, tb_init_master_3.stage, dim_project_type.project_type, dim_category_3.category, (CASE WHEN (hier_1.level = 0) THEN tax_1.element WHEN (hier_1.level = 1) THEN tax_1.element ELSE split_part(array_to_string(hier_1.name_path, '->'::text), '->'::text, 2) END), tb_init_master_3.manual_status, dim_value_type.value_type_name, tb_init_master_3.group_id, tb_init_master_3.in_scope, (count(tb_raid.raid_id)), e.max_actual, e.max_forecasted, (CASE WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values) AND ((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric)) THEN 'Allocation Mismatch, Latest Estimate Missing'::text WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values) AND (vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Allocation Mismatch, Stretch Goal Understated'::text WHEN (((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric) AND (vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Latest Estimate Missing, Stretch Goal Understated'::text WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values) AND ((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric) AND (vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Allocation Mismatch, Latest Estimate Missing, Stretch Goal Understated'::text WHEN ((vw_warning_callouts.stage <> 'Cancelled Project'::text) AND (vw_warning_callouts.project_values <> vw_warning_callouts.allocation_values)) THEN 'Allocation Mismatch'::text WHEN (((vw_warning_callouts.stage = 'Not Started'::text) OR (vw_warning_callouts.stage = 'Active'::text)) AND (vw_warning_callouts.project_values = '0'::numeric)) THEN 'Latest Estimate Missing'::text WHEN ((vw_warning_callouts.stage = 'Active'::text) AND (vw_warning_callouts.stretch_goal <> 0) AND ((vw_warning_callouts.stretch_goal)::numeric < vw_warning_callouts.project_values)) THEN 'Stretch Goal Understated'::text ELSE NULL::text END), (CASE WHEN (vw_warning_callouts.date_count > 0) THEN 'Future Actual Date'::text ELSE NULL::text END), (CASE WHEN (tb_init_master_3.stage = 'Cancelled Project'::text) THEN 'Cancelled Project'::text WHEN (tb_init_master_3.stage = 'Completed Project'::text) THEN 'Completed Project'::text ELSE c."project status" END)
  • Sort Method: quicksort Memory: 2,071kB
3. 1.718 712.345 ↑ 3.6 3,918 1

Hash Left Join (cost=8,818.81..9,525.51 rows=13,975 width=474) (actual time=707.113..712.345 rows=3,918 loops=1)

  • Hash Cond: ((tb_init_master_3.project_id)::text = (e.project_id)::text)
4. 2.089 703.853 ↑ 3.6 3,918 1

Hash Right Join (cost=8,476.40..9,076.39 rows=13,975 width=466) (actual time=700.319..703.853 rows=3,918 loops=1)

  • Hash Cond: ((vw_warning_callouts.project_id)::text = (tb_init_master_3.project_id)::text)
5. 0.963 138.317 ↑ 4.4 845 1

Subquery Scan on vw_warning_callouts (cost=1,438.81..1,870.86 rows=3,757 width=69) (actual time=136.846..138.317 rows=845 loops=1)

6. 2.630 137.354 ↑ 4.4 845 1

HashAggregate (cost=1,438.81..1,485.77 rows=3,757 width=95) (actual time=136.841..137.354 rows=845 loops=1)

  • Group Key: tb_init_master.project_id, tb_init_master.stage, tb_init_master.stretch_goal, (round(sum(((dim_currency.currency_fx_rate * (tb_init_val.init_val)::numeric))), 4)), d.date_count
7. 1.124 134.724 ↓ 1.0 3,814 1

Hash Right Join (cost=1,254.35..1,382.45 rows=3,757 width=71) (actual time=132.008..134.724 rows=3,814 loops=1)

  • Hash Cond: ((tb_bu_alloc.project_id)::text = (tb_init_master.project_id)::text)
8. 1.648 1.648 ↑ 1.0 3,748 1

Seq Scan on tb_bu_alloc (cost=0.00..76.48 rows=3,748 width=21) (actual time=0.039..1.648 rows=3,748 loops=1)

9. 0.174 131.952 ↑ 1.0 845 1

Hash (cost=1,243.76..1,243.76 rows=847 width=63) (actual time=131.952..131.952 rows=845 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 69kB
10. 0.201 131.778 ↑ 1.0 845 1

Hash Left Join (cost=1,128.28..1,243.76 rows=847 width=63) (actual time=128.939..131.778 rows=845 loops=1)

  • Hash Cond: ((tb_init_master.project_id)::text = (d.project_id)::text)
11. 0.539 126.957 ↑ 1.0 845 1

Merge Left Join (cost=785.86..899.11 rows=845 width=55) (actual time=124.301..126.957 rows=845 loops=1)

  • Merge Cond: ((tb_init_master.project_id)::text = (tb_init_val.project_id)::text)
12. 0.773 111.599 ↑ 1.0 845 1

Merge Left Join (cost=459.18..468.61 rows=845 width=437) (actual time=110.614..111.599 rows=845 loops=1)

  • Merge Cond: ((tb_init_master.project_id)::text = (tb_init_master_1.project_id)::text)
13. 2.929 4.541 ↑ 1.0 845 1

Sort (cost=330.58..332.69 rows=845 width=23) (actual time=4.503..4.541 rows=845 loops=1)

  • Sort Key: tb_init_master.project_id
  • Sort Method: quicksort Memory: 91kB
14. 0.477 1.612 ↑ 1.0 845 1

Hash Left Join (cost=229.88..289.50 rows=845 width=23) (actual time=0.766..1.612 rows=845 loops=1)

  • Hash Cond: (btrim(dim_category.category) = btrim(dim_category_1.category))
15. 0.144 0.479 ↑ 1.0 845 1

Hash Left Join (cost=1.68..54.60 rows=845 width=55) (actual time=0.101..0.479 rows=845 loops=1)

  • Hash Cond: (tb_init_master.category_id = dim_category.category_id)
16. 0.311 0.311 ↑ 1.0 845 1

Seq Scan on tb_init_master (cost=0.00..50.40 rows=845 width=43) (actual time=0.066..0.311 rows=845 loops=1)

  • Filter: (stage <> ALL ('{"Not Started","Cancelled Opportunity"}'::text[]))
  • Rows Removed by Filter: 148
17. 0.010 0.024 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.023..0.024 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 0.014 0.014 ↑ 1.0 30 1

Seq Scan on dim_category (cost=0.00..1.30 rows=30 width=36) (actual time=0.011..0.014 rows=30 loops=1)

19. 0.016 0.656 ↓ 6.0 30 1

Hash (cost=228.14..228.14 rows=5 width=32) (actual time=0.656..0.656 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.023 0.640 ↓ 6.0 30 1

Hash Join (cost=223.39..228.14 rows=5 width=32) (actual time=0.602..0.640 rows=30 loops=1)

  • Hash Cond: ((hier.element)::integer = dim_category_1.category_id)
21. 0.038 0.600 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.41 rows=5 width=392) (actual time=0.575..0.600 rows=30 loops=1)

  • Hash Cond: (tax.element_id = hier.element_id)
22.          

CTE hier

23. 0.063 0.366 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.018..0.366 rows=192 loops=1)

24. 0.033 0.033 ↑ 1.0 3 1

Seq Scan on tb_hierarchies tb_hierarchies_6 (cost=0.00..4.40 rows=3 width=74) (actual time=0.017..0.033 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
25. 0.183 0.270 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.051..0.090 rows=63 loops=3)

  • Hash Cond: (h_6.parent_id = s_6.element_id)
26. 0.039 0.039 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h_6 (cost=0.00..3.92 rows=192 width=38) (actual time=0.004..0.013 rows=192 loops=3)

27. 0.027 0.048 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.016..0.016 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
28. 0.021 0.021 ↓ 2.1 64 3

WorkTable Scan on hier s_6 (cost=0.00..0.60 rows=30 width=40) (actual time=0.001..0.007 rows=64 loops=3)

29. 0.026 0.026 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax (cost=0.00..3.92 rows=192 width=4) (actual time=0.014..0.026 rows=192 loops=1)

30. 0.009 0.536 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=36) (actual time=0.536..0.536 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
31. 0.059 0.527 ↓ 6.0 30 1

CTE Scan on hier (cost=45.77..93.84 rows=5 width=36) (actual time=0.509..0.527 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 18)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
32.          

SubPlan (for CTE Scan)

33. 0.034 0.468 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=0.466..0.468 rows=16 loops=1)

  • Group Key: hier_1_7.parent_id
34. 0.434 0.434 ↑ 10.0 192 1

CTE Scan on hier hier_1_7 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.000..0.434 rows=192 loops=1)

35. 0.008 0.017 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.017..0.017 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 0.009 0.009 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_1 (cost=0.00..1.30 rows=30 width=36) (actual time=0.007..0.009 rows=30 loops=1)

37. 0.117 106.285 ↓ 4.7 942 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=106.104..106.285 rows=942 loops=1)

38. 2.201 106.168 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=106.092..106.168 rows=942 loops=1)

  • Sort Key: tb_init_master_1.project_id
  • Sort Method: quicksort Memory: 98kB
39. 2.604 103.967 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=103.797..103.967 rows=942 loops=1)

  • Group Key: tb_init_master_1.project_id, tb_attr.attr_category, tb_attr.active
40. 42.586 101.363 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=98.482..101.363 rows=5,757 loops=1)

  • Group Key: tb_init_master_1.project_id, tb_initiative_master_attr_val.attr_id, tb_attr.attr_category, tb_attr.active
41. 11.550 58.777 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=44.440..58.777 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1.attr_id = tb_attr.attr_id)
42. 0.036 0.079 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.064..0.079 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1.attr_id
  • Sort Method: quicksort Memory: 28kB
43. 0.043 0.043 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1 (cost=0.00..1.70 rows=70 width=4) (actual time=0.035..0.043 rows=70 loops=1)

44. 5.428 47.148 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=44.372..47.148 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val.attr_id
  • Sort Method: quicksort Memory: 644kB
45. 4.117 41.720 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.080..41.720 rows=5,757 loops=1)

46. 1.313 3.061 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.056..3.061 rows=5,757 loops=1)

47. 0.038 0.038 ↓ 38.0 38 1

Seq Scan on tb_attr (cost=0.00..1.48 rows=1 width=38) (actual time=0.018..0.038 rows=38 loops=1)

  • Filter: (active = 1)
48. 1.710 1.710 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val (cost=0.28..39.48 rows=320 width=13) (actual time=0.003..0.045 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr.attr_id)
49. 34.542 34.542 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_1 (cost=0.28..0.32 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val.project_id)::text)
  • Heap Fetches: 5,757
50. 1.040 14.819 ↑ 1.0 765 1

GroupAggregate (cost=326.68..402.73 rows=765 width=37) (actual time=13.682..14.819 rows=765 loops=1)

  • Group Key: tb_init_val.project_id
51. 11.667 13.779 ↑ 1.0 3,690 1

Sort (cost=326.68..335.90 rows=3,690 width=305) (actual time=13.645..13.779 rows=3,690 loops=1)

  • Sort Key: tb_init_val.project_id, tb_init_val.init_value_line
  • Sort Method: quicksort Memory: 385kB
52. 1.838 2.112 ↑ 1.0 3,690 1

Hash Left Join (cost=1.16..108.06 rows=3,690 width=305) (actual time=0.087..2.112 rows=3,690 loops=1)

  • Hash Cond: ((tb_init_val.currency_id)::text = (dim_currency.currency_key)::text)
53. 0.259 0.259 ↑ 1.0 3,690 1

Seq Scan on tb_init_val (cost=0.00..72.90 rows=3,690 width=29) (actual time=0.025..0.259 rows=3,690 loops=1)

54. 0.004 0.015 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=36) (actual time=0.015..0.015 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
55. 0.011 0.011 ↑ 1.0 7 1

Seq Scan on dim_currency (cost=0.00..1.07 rows=7 width=36) (actual time=0.010..0.011 rows=7 loops=1)

56. 0.117 4.620 ↑ 1.0 847 1

Hash (cost=331.83..331.83 rows=847 width=13) (actual time=4.620..4.620 rows=847 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
57. 0.061 4.503 ↑ 1.0 847 1

Subquery Scan on d (cost=314.89..331.83 rows=847 width=13) (actual time=4.335..4.503 rows=847 loops=1)

58. 1.763 4.442 ↑ 1.0 847 1

HashAggregate (cost=314.89..323.36 rows=847 width=13) (actual time=4.333..4.442 rows=847 loops=1)

  • Group Key: tb_init_milestone.project_id
59.          

Initplan (for HashAggregate)

60. 0.011 0.011 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

61. 1.797 2.668 ↑ 1.0 8,470 1

Hash Join (cost=60.32..251.35 rows=8,470 width=9) (actual time=0.337..2.668 rows=8,470 loops=1)

  • Hash Cond: ((tb_init_milestone.project_id)::text = (tb_init_master_2.project_id)::text)
62. 0.627 0.627 ↑ 1.0 8,470 1

Seq Scan on tb_init_milestone (cost=0.00..168.70 rows=8,470 width=9) (actual time=0.077..0.627 rows=8,470 loops=1)

63. 0.106 0.244 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.244..0.244 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
64. 0.138 0.138 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_2 (cost=0.00..47.92 rows=992 width=5) (actual time=0.016..0.138 rows=993 loops=1)

65. 2.057 563.447 ↓ 1.1 3,918 1

Hash (cost=6,991.47..6,991.47 rows=3,690 width=402) (actual time=563.447..563.447 rows=3,918 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,388kB
66. 1.438 561.390 ↓ 1.1 3,918 1

Merge Left Join (cost=6,860.93..6,991.47 rows=3,690 width=402) (actual time=556.770..561.390 rows=3,918 loops=1)

  • Merge Cond: ((tb_init_master_3.project_id)::text = (tb_init_val_1.project_id)::text)
67. 0.789 539.786 ↓ 1.0 993 1

Merge Left Join (cost=6,534.25..6,560.84 rows=992 width=370) (actual time=537.209..539.786 rows=993 loops=1)

  • Merge Cond: ((tb_init_master_3.project_id)::text = (c."project id")::text)
68. 0.397 74.942 ↓ 1.0 993 1

Merge Left Join (cost=725.74..748.68 rows=992 width=338) (actual time=73.249..74.942 rows=993 loops=1)

  • Merge Cond: ((tb_init_master_3.project_id)::text = (tb_raid.project_id)::text)
69. 0.962 73.098 ↓ 1.0 993 1

Merge Left Join (cost=478.49..488.95 rows=992 width=462) (actual time=71.810..73.098 rows=993 loops=1)

  • Merge Cond: ((tb_init_master_3.project_id)::text = (tb_init_master_4.project_id)::text)
70. 2.837 6.641 ↓ 1.0 993 1

Sort (cost=349.88..352.36 rows=992 width=330) (actual time=6.568..6.641 rows=993 loops=1)

  • Sort Key: tb_init_master_3.project_id
  • Sort Method: quicksort Memory: 423kB
71. 0.203 3.804 ↓ 1.0 993 1

Hash Left Join (cost=232.16..300.51 rows=992 width=330) (actual time=1.551..3.804 rows=993 loops=1)

  • Hash Cond: (tb_init_master_3.value_type_id = dim_value_type.value_type_id)
72. 0.196 3.539 ↓ 1.0 993 1

Hash Left Join (cost=231.04..294.59 rows=992 width=302) (actual time=1.476..3.539 rows=993 loops=1)

  • Hash Cond: (tb_init_master_3.project_type_id = dim_project_type.project_type_id)
73. 0.657 3.323 ↓ 1.0 993 1

Hash Left Join (cost=229.93..288.67 rows=992 width=274) (actual time=1.445..3.323 rows=993 loops=1)

  • Hash Cond: (btrim(dim_category_2.category) = btrim(dim_category_3.category))
74. 0.389 1.571 ↓ 1.0 993 1

Hash Left Join (cost=1.68..52.57 rows=992 width=242) (actual time=0.337..1.571 rows=993 loops=1)

  • Hash Cond: (tb_init_master_3.category_id = dim_category_2.category_id)
75. 1.142 1.142 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_3 (cost=0.00..47.92 rows=992 width=222) (actual time=0.202..1.142 rows=993 loops=1)

76. 0.016 0.040 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.040..0.040 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
77. 0.024 0.024 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_2 (cost=0.00..1.30 rows=30 width=36) (actual time=0.019..0.024 rows=30 loops=1)

78. 0.026 1.095 ↓ 6.0 30 1

Hash (cost=228.19..228.19 rows=5 width=64) (actual time=1.095..1.095 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
79. 0.033 1.069 ↓ 6.0 30 1

Hash Join (cost=223.39..228.19 rows=5 width=64) (actual time=1.013..1.069 rows=30 loops=1)

  • Hash Cond: ((hier_1.element)::integer = dim_category_3.category_id)
80. 0.084 1.008 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.46 rows=5 width=392) (actual time=0.967..1.008 rows=30 loops=1)

  • Hash Cond: (tax_1.element_id = hier_1.element_id)
81.          

CTE hier

82. 0.119 0.588 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.027..0.588 rows=192 loops=1)

83. 0.064 0.064 ↑ 1.0 3 1

Seq Scan on tb_hierarchies tb_hierarchies_1 (cost=0.00..4.40 rows=3 width=74) (actual time=0.023..0.064 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
84. 0.279 0.405 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.050..0.135 rows=63 loops=3)

  • Hash Cond: (h_1.parent_id = s_1.element_id)
85. 0.069 0.069 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h_1 (cost=0.00..3.92 rows=192 width=38) (actual time=0.009..0.023 rows=192 loops=3)

86. 0.033 0.057 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.019..0.019 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
87. 0.024 0.024 ↓ 2.1 64 3

WorkTable Scan on hier s_1 (cost=0.00..0.60 rows=30 width=40) (actual time=0.001..0.008 rows=64 loops=3)

88. 0.022 0.022 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax_1 (cost=0.00..3.92 rows=192 width=7) (actual time=0.013..0.022 rows=192 loops=1)

89. 0.013 0.902 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=72) (actual time=0.902..0.902 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
90. 0.089 0.889 ↓ 6.0 30 1

CTE Scan on hier hier_1 (cost=45.77..93.84 rows=5 width=72) (actual time=0.871..0.889 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 5)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
91.          

SubPlan (for CTE Scan)

92. 0.068 0.800 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=0.797..0.800 rows=16 loops=1)

  • Group Key: hier_1_2.parent_id
93. 0.732 0.732 ↑ 10.0 192 1

CTE Scan on hier hier_1_2 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.000..0.732 rows=192 loops=1)

94. 0.012 0.028 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.028..0.028 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
95. 0.016 0.016 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_3 (cost=0.00..1.30 rows=30 width=36) (actual time=0.011..0.016 rows=30 loops=1)

96. 0.007 0.020 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=36) (actual time=0.020..0.020 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
97. 0.013 0.013 ↑ 1.0 5 1

Seq Scan on dim_project_type (cost=0.00..1.05 rows=5 width=36) (actual time=0.013..0.013 rows=5 loops=1)

98. 0.009 0.062 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=36) (actual time=0.062..0.062 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
99. 0.053 0.053 ↑ 1.0 5 1

Seq Scan on dim_value_type (cost=0.00..1.05 rows=5 width=36) (actual time=0.053..0.053 rows=5 loops=1)

100. 0.146 65.495 ↓ 4.7 942 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=65.236..65.495 rows=942 loops=1)

101. 2.201 65.349 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=65.224..65.349 rows=942 loops=1)

  • Sort Key: tb_init_master_4.project_id
  • Sort Method: quicksort Memory: 98kB
102. 1.491 63.148 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=62.986..63.148 rows=942 loops=1)

  • Group Key: tb_init_master_4.project_id, tb_attr_1.attr_category, tb_attr_1.active
103. 25.176 61.657 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=59.962..61.657 rows=5,757 loops=1)

  • Group Key: tb_init_master_4.project_id, tb_initiative_master_attr_val_1.attr_id, tb_attr_1.attr_category, tb_attr_1.active
104. 8.469 36.481 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=25.814..36.481 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_1.attr_id = tb_attr_1.attr_id)
105. 0.019 0.082 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.073..0.082 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_1.attr_id
  • Sort Method: quicksort Memory: 28kB
106. 0.063 0.063 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_1 (cost=0.00..1.70 rows=70 width=4) (actual time=0.058..0.063 rows=70 loops=1)

107. 3.684 27.930 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=25.738..27.930 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_1.attr_id
  • Sort Method: quicksort Memory: 644kB
108. 3.270 24.246 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.124..24.246 rows=5,757 loops=1)

109. 0.659 3.705 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.104..3.705 rows=5,757 loops=1)

110. 0.044 0.044 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_1 (cost=0.00..1.48 rows=1 width=38) (actual time=0.030..0.044 rows=38 loops=1)

  • Filter: (active = 1)
111. 3.002 3.002 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_1 (cost=0.28..39.48 rows=320 width=13) (actual time=0.006..0.079 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_1.attr_id)
112. 17.271 17.271 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_4 (cost=0.28..0.32 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_1.project_id)::text)
  • Heap Fetches: 5,757
113. 0.018 1.447 ↓ 3.5 7 1

GroupAggregate (cost=247.25..247.29 rows=2 width=13) (actual time=1.436..1.447 rows=7 loops=1)

  • Group Key: tb_raid.project_id
114. 0.016 1.429 ↓ 4.0 8 1

Sort (cost=247.25..247.26 rows=2 width=9) (actual time=1.428..1.429 rows=8 loops=1)

  • Sort Key: tb_raid.project_id
  • Sort Method: quicksort Memory: 25kB
115. 0.016 1.413 ↓ 4.0 8 1

Nested Loop Left Join (cost=223.39..247.24 rows=2 width=9) (actual time=0.823..1.413 rows=8 loops=1)

  • Join Filter: (dim_category_4.category_id = tb_raid.category_id)
  • Rows Removed by Join Filter: 240
116. 0.749 0.749 ↓ 4.0 8 1

Seq Scan on tb_raid (cost=0.00..18.94 rows=2 width=17) (actual time=0.176..0.749 rows=8 loops=1)

  • Filter: (((project_id)::text <> ''::text) AND ((project_id)::text <> ''::text) AND (CASE WHEN (actual_resolution_dt IS NOT NULL) THEN 'Closed'::text WHEN ((revised_resolution_dt IS NOT NULL) AND (revised_resolution_dt >= CURRENT_DATE)) THEN 'On Track'::text WHEN ((revised_resolution_dt IS NOT NULL) AND (revised_resolution_dt < CURRENT_DATE)) THEN 'Delayed'::text WHEN ((tgt_resolution_dt IS NOT NULL) AND (tgt_resolution_dt >= CURRENT_DATE)) THEN 'On Track'::text WHEN ((tgt_resolution_dt IS NOT NULL) AND (tgt_resolution_dt < CURRENT_DATE)) THEN 'Delayed'::text ELSE '-'::text END <> 'Closed'::text))
  • Rows Removed by Filter: 257
117. 0.015 0.648 ↓ 6.0 30 8

Materialize (cost=223.39..228.17 rows=5 width=4) (actual time=0.075..0.081 rows=30 loops=8)

118. 0.025 0.633 ↓ 6.0 30 1

Hash Join (cost=223.39..228.14 rows=5 width=4) (actual time=0.597..0.633 rows=30 loops=1)

  • Hash Cond: ((hier_2.element)::integer = dim_category_4.category_id)
119. 0.029 0.582 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.41 rows=5 width=392) (actual time=0.559..0.582 rows=30 loops=1)

  • Hash Cond: (tax_2.element_id = hier_2.element_id)
120.          

CTE hier

121. 0.064 0.314 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.014..0.314 rows=192 loops=1)

122. 0.028 0.028 ↑ 1.0 3 1

Seq Scan on tb_hierarchies tb_hierarchies_2 (cost=0.00..4.40 rows=3 width=74) (actual time=0.012..0.028 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
123. 0.135 0.222 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.035..0.074 rows=63 loops=3)

  • Hash Cond: (h_2.parent_id = s_2.element_id)
124. 0.036 0.036 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h_2 (cost=0.00..3.92 rows=192 width=38) (actual time=0.004..0.012 rows=192 loops=3)

125. 0.030 0.051 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.017..0.017 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
126. 0.021 0.021 ↓ 2.1 64 3

WorkTable Scan on hier s_2 (cost=0.00..0.60 rows=30 width=40) (actual time=0.001..0.007 rows=64 loops=3)

127. 0.016 0.016 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax_2 (cost=0.00..3.92 rows=192 width=4) (actual time=0.008..0.016 rows=192 loops=1)

128. 0.009 0.537 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=36) (actual time=0.537..0.537 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
129. 0.107 0.528 ↓ 6.0 30 1

CTE Scan on hier hier_2 (cost=45.77..93.84 rows=5 width=36) (actual time=0.510..0.528 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 7)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
130.          

SubPlan (for CTE Scan)

131. 0.032 0.421 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=0.419..0.421 rows=16 loops=1)

  • Group Key: hier_1_3.parent_id
132. 0.389 0.389 ↑ 10.0 192 1

CTE Scan on hier hier_1_3 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.000..0.389 rows=192 loops=1)

133. 0.010 0.026 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=4) (actual time=0.026..0.026 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
134. 0.016 0.016 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_4 (cost=0.00..1.30 rows=30 width=4) (actual time=0.014..0.016 rows=30 loops=1)

135. 3.287 464.055 ↓ 10.9 847 1

Sort (cost=5,808.51..5,808.70 rows=78 width=150) (actual time=463.955..464.055 rows=847 loops=1)

  • Sort Key: c."project id
  • Sort Method: quicksort Memory: 89kB
136. 0.103 460.768 ↓ 10.9 847 1

Subquery Scan on c (cost=5,804.50..5,806.06 rows=78 width=150) (actual time=460.385..460.768 rows=847 loops=1)

137. 1.481 460.665 ↓ 10.9 847 1

HashAggregate (cost=5,804.50..5,805.28 rows=78 width=162) (actual time=460.384..460.665 rows=847 loops=1)

  • Group Key: tb_init_milestone_1.project_id, (CASE WHEN ((CASE WHEN (tb_init_master_11.manual_status = 'Completed Project'::text) THEN 3 WHEN (tb_init_master_11.manual_status = 'On Track'::text) THEN 2 WHEN (tb_init_master_11.manual_status = 'Delayed'::text) THEN 1 WHEN (tb_init_master_11.manual_status = 'At Risk'::text) THEN 0 WHEN (tb_init_master_11.manual_status = 'Cancelled Project'::text) THEN '-1'::integer WHEN (tb_init_master_11.manual_status = 'Cancelled Opportunity'::text) THEN '-2'::integer ELSE NULL::integer END = '-1'::integer) OR (CASE WHEN (tb_init_master_11.manual_status = 'Completed Project'::text) THEN 3 WHEN (tb_init_master_11.manual_status = 'On Track'::text) THEN 2 WHEN (tb_init_master_11.manual_status = 'Delayed'::text) THEN 1 WHEN (tb_init_master_11.manual_status = 'At Risk'::text) THEN 0 WHEN (tb_init_master_11.manual_status = 'Cancelled Project'::text) THEN '-1'::integer WHEN (tb_init_master_11.manual_status = 'Cancelled Opportunity'::text) THEN '-2'::integer ELSE NULL::integer END = '-2'::integer)) THEN '-1'::integer ELSE 0 END), (CASE WHEN ((CASE WHEN (tb_init_master_11.manual_status = 'Completed Project'::text) THEN 3 WHEN (tb_init_master_11.manual_status = 'On Track'::text) THEN 2 WHEN (tb_init_master_11.manual_status = 'Delayed'::text) THEN 1 WHEN (tb_init_master_11.manual_status = 'At Risk'::text) THEN 0 WHEN (tb_init_master_11.manual_status = 'Cancelled Project'::text) THEN '-1'::integer WHEN (tb_init_master_11.manual_status = 'Cancelled Opportunity'::text) THEN '-2'::integer ELSE NULL::integer END = '-1'::integer) OR (CASE WHEN (tb_init_master_11.manual_status = 'Completed Project'::text) THEN 3 WHEN (tb_init_master_11.manual_status = 'On Track'::text) THEN 2 WHEN (tb_init_master_11.manual_status = 'Delayed'::text) THEN 1 WHEN (tb_init_master_11.manual_status = 'At Risk'::text) THEN 0 WHEN (tb_init_master_11.manual_status = 'Cancelled Project'::text) THEN '-1'::integer WHEN (tb_init_master_11.manual_status = 'Cancelled Opportunity'::text) THEN '-2'::integer ELSE NULL::integer END = '-2'::integer)) THEN 'Cancelled'::text ELSE 'At Risk - Current VM Delayed > 10 Days'::text END), last_vm.last_completed_vm, current_vm.current_milestone
138. 0.188 459.184 ↓ 28.9 2,255 1

Append (cost=3,585.51..5,803.52 rows=78 width=162) (actual time=407.109..459.184 rows=2,255 loops=1)

139. 0.000 421.371 ↓ 19.3 695 1

Hash Join (cost=3,585.51..4,102.01 rows=36 width=49) (actual time=407.108..421.371 rows=695 loops=1)

  • Hash Cond: ((tb_init_master_5.project_id)::text = (tb_init_master_11.project_id)::text)
140.          

Initplan (for Hash Join)

141. 1.536 9.032 ↑ 1.0 1 1

Aggregate (cost=412.50..412.51 rows=1 width=8) (actual time=9.032..9.032 rows=1 loops=1)

142. 7.496 7.496 ↑ 1.0 16,920 1

Seq Scan on tb_clone_init_milestone tb_clone_init_milestone_2 (cost=0.00..370.20 rows=16,920 width=8) (actual time=0.009..7.496 rows=16,920 loops=1)

143. 0.102 76.773 ↑ 1.0 1 1

Aggregate (cost=639.55..639.56 rows=1 width=8) (actual time=76.773..76.773 rows=1 loops=1)

144. 1.266 76.671 ↓ 1.0 1,990 1

Merge Left Join (cost=589.70..614.78 rows=1,982 width=443) (actual time=74.541..76.671 rows=1,990 loops=1)

  • Merge Cond: ((tb_clone_init_master_1.project_id)::text = (tb_init_master_23.project_id)::text)
145. 0.646 10.790 ↓ 1.0 1,990 1

Merge Left Join (cost=461.10..472.72 rows=1,982 width=13) (actual time=10.069..10.790 rows=1,990 loops=1)

  • Merge Cond: ((tb_clone_init_master_1.project_id)::text = (tb_ctachieve_1.project_id)::text)
146. 4.535 9.953 ↑ 1.0 1,982 1

Sort (cost=457.87..462.82 rows=1,982 width=13) (actual time=9.879..9.953 rows=1,982 loops=1)

  • Sort Key: tb_clone_init_master_1.project_id
  • Sort Method: quicksort Memory: 141kB
147. 1.151 5.418 ↑ 1.0 1,982 1

Hash Left Join (cost=229.88..349.33 rows=1,982 width=13) (actual time=1.745..5.418 rows=1,982 loops=1)

  • Hash Cond: (btrim(dim_category_11.category) = btrim(dim_category_12.category))
148. 0.589 2.686 ↑ 1.0 1,982 1

Hash Left Join (cost=1.68..105.43 rows=1,982 width=45) (actual time=0.142..2.686 rows=1,982 loops=1)

  • Hash Cond: (tb_clone_init_master_1.category_id = dim_category_11.category_id)
149. 2.011 2.011 ↑ 1.0 1,982 1

Seq Scan on tb_clone_init_master tb_clone_init_master_1 (cost=0.00..97.82 rows=1,982 width=33) (actual time=0.033..2.011 rows=1,982 loops=1)

150. 0.052 0.086 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.085..0.086 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
151. 0.034 0.034 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_11 (cost=0.00..1.30 rows=30 width=36) (actual time=0.028..0.034 rows=30 loops=1)

152. 0.033 1.581 ↓ 6.0 30 1

Hash (cost=228.14..228.14 rows=5 width=32) (actual time=1.581..1.581 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
153. 0.060 1.548 ↓ 6.0 30 1

Hash Join (cost=223.39..228.14 rows=5 width=32) (actual time=1.402..1.548 rows=30 loops=1)

  • Hash Cond: ((hier_6.element)::integer = dim_category_12.category_id)
154. 0.074 1.445 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.41 rows=5 width=392) (actual time=1.332..1.445 rows=30 loops=1)

  • Hash Cond: (tax_6.element_id = hier_6.element_id)
155.          

CTE hier

156. 0.161 0.784 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.027..0.784 rows=192 loops=1)

157. 0.068 0.068 ↑ 1.0 3 1

Seq Scan on tb_hierarchies tb_hierarchies_3 (cost=0.00..4.40 rows=3 width=74) (actual time=0.024..0.068 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
158. 0.348 0.555 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.078..0.185 rows=63 loops=3)

  • Hash Cond: (h_3.parent_id = s_3.element_id)
159. 0.090 0.090 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h_3 (cost=0.00..3.92 rows=192 width=38) (actual time=0.008..0.030 rows=192 loops=3)

160. 0.063 0.117 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.039..0.039 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
161. 0.054 0.054 ↓ 2.1 64 3

WorkTable Scan on hier s_3 (cost=0.00..0.60 rows=30 width=40) (actual time=0.003..0.018 rows=64 loops=3)

162. 0.089 0.089 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax_6 (cost=0.00..3.92 rows=192 width=4) (actual time=0.017..0.089 rows=192 loops=1)

163. 0.022 1.282 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=36) (actual time=1.282..1.282 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
164. 0.194 1.260 ↓ 6.0 30 1

CTE Scan on hier hier_6 (cost=45.77..93.84 rows=5 width=36) (actual time=1.213..1.260 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 10)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
165.          

SubPlan (for CTE Scan)

166. 0.086 1.066 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=1.062..1.066 rows=16 loops=1)

  • Group Key: hier_1_4.parent_id
167. 0.980 0.980 ↑ 10.0 192 1

CTE Scan on hier hier_1_4 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.001..0.980 rows=192 loops=1)

168. 0.015 0.043 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.043..0.043 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
169. 0.028 0.028 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_12 (cost=0.00..1.30 rows=30 width=36) (actual time=0.023..0.028 rows=30 loops=1)

170. 0.164 0.191 ↓ 2.0 113 1

Sort (cost=3.23..3.37 rows=57 width=9) (actual time=0.186..0.191 rows=113 loops=1)

  • Sort Key: tb_ctachieve_1.project_id
  • Sort Method: quicksort Memory: 27kB
171. 0.027 0.027 ↑ 1.0 57 1

Seq Scan on tb_ctachieve tb_ctachieve_1 (cost=0.00..1.57 rows=57 width=9) (actual time=0.022..0.027 rows=57 loops=1)

172. 0.135 64.615 ↓ 9.4 1,885 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=64.458..64.615 rows=1,885 loops=1)

173. 2.152 64.480 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=64.444..64.480 rows=942 loops=1)

  • Sort Key: tb_init_master_23.project_id
  • Sort Method: quicksort Memory: 98kB
174. 1.491 62.328 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=62.163..62.328 rows=942 loops=1)

  • Group Key: tb_init_master_23.project_id, tb_attr_7.attr_category, tb_attr_7.active
175. 26.926 60.837 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=59.194..60.837 rows=5,757 loops=1)

  • Group Key: tb_init_master_23.project_id, tb_initiative_master_attr_val_7.attr_id, tb_attr_7.attr_category, tb_attr_7.active
176. 8.713 33.911 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=23.012..33.911 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_7.attr_id = tb_attr_7.attr_id)
177. 0.026 0.051 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.042..0.051 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_7.attr_id
  • Sort Method: quicksort Memory: 28kB
178. 0.025 0.025 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_7 (cost=0.00..1.70 rows=70 width=4) (actual time=0.018..0.025 rows=70 loops=1)

179. 3.663 25.147 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=22.965..25.147 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_7.attr_id
  • Sort Method: quicksort Memory: 644kB
180. 2.453 21.484 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.047..21.484 rows=5,757 loops=1)

181. 0.627 1.760 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.028..1.760 rows=5,757 loops=1)

182. 0.031 0.031 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_7 (cost=0.00..1.48 rows=1 width=38) (actual time=0.012..0.031 rows=38 loops=1)

  • Filter: (active = 1)
183. 1.102 1.102 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_7 (cost=0.28..39.48 rows=320 width=13) (actual time=0.003..0.029 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_7.attr_id)
184. 17.271 17.271 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_23 (cost=0.28..0.32 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_7.project_id)::text)
  • Heap Fetches: 5,757
185. 0.353 33.937 ↓ 19.2 691 1

Hash Left Join (cost=794.45..1,307.93 rows=36 width=23) (actual time=20.281..33.937 rows=691 loops=1)

  • Hash Cond: ((tb_init_milestone_1.project_id)::text = (last_vm.project_id)::text)
186. 0.466 30.771 ↓ 19.2 691 1

Nested Loop Left Join (cost=505.20..1,018.59 rows=36 width=19) (actual time=17.455..30.771 rows=691 loops=1)

187. 0.071 22.704 ↓ 19.2 691 1

Nested Loop (cost=504.64..993.88 rows=36 width=23) (actual time=17.421..22.704 rows=691 loops=1)

188. 1.164 19.869 ↓ 16.5 691 1

Hash Right Join (cost=504.37..980.74 rows=42 width=18) (actual time=17.396..19.869 rows=691 loops=1)

  • Hash Cond: (((tb_clone_init_milestone.project_id)::text = (tb_init_milestone_1.project_id)::text) AND (tb_clone_init_milestone.value_milestone_id = tb_init_milestone_1.value_milestone_id))
189. 11.268 11.268 ↑ 1.0 8,460 1

Seq Scan on tb_clone_init_milestone (cost=0.00..412.50 rows=8,460 width=9) (actual time=9.939..11.268 rows=8,460 loops=1)

  • Filter: (batch_tm = $12)
  • Rows Removed by Filter: 8,460
190. 0.115 7.437 ↓ 16.5 691 1

Hash (cost=503.74..503.74 rows=42 width=18) (actual time=7.437..7.437 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
191. 0.952 7.322 ↓ 16.5 691 1

Hash Join (cost=290.57..503.74 rows=42 width=18) (actual time=5.906..7.322 rows=691 loops=1)

  • Hash Cond: (((tb_init_milestone_1.project_id)::text = (current_vm.project_id)::text) AND (tb_init_milestone_1.value_milestone_id = current_vm.current_milestone))
192. 0.501 0.501 ↑ 1.0 8,470 1

Seq Scan on tb_init_milestone tb_init_milestone_1 (cost=0.00..168.70 rows=8,470 width=9) (actual time=0.022..0.501 rows=8,470 loops=1)

193. 0.101 5.869 ↑ 1.2 691 1

Hash (cost=277.88..277.88 rows=846 width=9) (actual time=5.869..5.869 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
194. 0.038 5.768 ↑ 1.2 691 1

Subquery Scan on current_vm (cost=260.96..277.88 rows=846 width=9) (actual time=5.657..5.768 rows=691 loops=1)

195. 0.844 5.730 ↑ 1.2 691 1

HashAggregate (cost=260.96..269.42 rows=846 width=9) (actual time=5.656..5.730 rows=691 loops=1)

  • Group Key: tb_init_milestone_5.project_id
196. 0.739 4.886 ↑ 1.0 4,183 1

Hash Join (cost=60.32..240.04 rows=4,183 width=9) (actual time=0.249..4.886 rows=4,183 loops=1)

  • Hash Cond: ((tb_init_milestone_5.project_id)::text = (tb_init_master_9.project_id)::text)
197. 3.920 3.920 ↑ 1.0 4,183 1

Seq Scan on tb_init_milestone tb_init_milestone_5 (cost=0.00..168.70 rows=4,183 width=9) (actual time=0.013..3.920 rows=4,183 loops=1)

  • Filter: (actual_dt IS NULL)
  • Rows Removed by Filter: 4,287
198. 0.113 0.227 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.227..0.227 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
199. 0.114 0.114 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_9 (cost=0.00..47.92 rows=992 width=5) (actual time=0.010..0.114 rows=993 loops=1)

200. 2.764 2.764 ↑ 1.0 1 691

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_5 (cost=0.28..0.31 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=691)

  • Index Cond: (project_id = (tb_init_milestone_1.project_id)::text)
  • Heap Fetches: 691
201. 0.691 7.601 ↑ 1.0 1 691

Nested Loop (cost=0.56..0.68 rows=1 width=9) (actual time=0.011..0.011 rows=1 loops=691)

202. 4.837 4.837 ↑ 1.0 1 691

Index Only Scan using uq_tb_init_milestone_pk_project_id_value_milestone_id on tb_init_milestone tb_init_milestone_2 (cost=0.29..0.36 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=691)

  • Index Cond: ((project_id = (tb_init_milestone_1.project_id)::text) AND (value_milestone_id = (tb_init_milestone_1.value_milestone_id - 1)))
  • Heap Fetches: 691
203. 2.073 2.073 ↑ 1.0 1 691

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_6 (cost=0.28..0.31 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=691)

  • Index Cond: (project_id = (tb_init_milestone_2.project_id)::text)
  • Heap Fetches: 691
204. 0.115 2.813 ↓ 1.0 847 1

Hash (cost=278.67..278.67 rows=846 width=9) (actual time=2.813..2.813 rows=847 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
205. 0.051 2.698 ↓ 1.0 847 1

Subquery Scan on last_vm (cost=261.75..278.67 rows=846 width=9) (actual time=2.542..2.698 rows=847 loops=1)

206. 0.862 2.647 ↓ 1.0 847 1

HashAggregate (cost=261.75..270.21 rows=846 width=9) (actual time=2.542..2.647 rows=847 loops=1)

  • Group Key: tb_init_milestone_6.project_id
207. 0.714 1.785 ↑ 1.0 4,287 1

Hash Join (cost=60.32..240.32 rows=4,287 width=9) (actual time=0.270..1.785 rows=4,287 loops=1)

  • Hash Cond: ((tb_init_milestone_6.project_id)::text = (tb_init_master_10.project_id)::text)
208. 0.833 0.833 ↑ 1.0 4,287 1

Seq Scan on tb_init_milestone tb_init_milestone_6 (cost=0.00..168.70 rows=4,287 width=9) (actual time=0.021..0.833 rows=4,287 loops=1)

  • Filter: (actual_dt IS NOT NULL)
  • Rows Removed by Filter: 4,183
209. 0.116 0.238 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.238..0.238 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
210. 0.122 0.122 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_10 (cost=0.00..47.92 rows=992 width=5) (actual time=0.010..0.122 rows=993 loops=1)

211. 0.157 386.809 ↓ 1.0 997 1

Hash (cost=1,726.59..1,726.59 rows=992 width=16) (actual time=386.809..386.809 rows=997 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
212. 0.080 386.652 ↓ 1.0 997 1

Nested Loop (cost=1,604.71..1,726.59 rows=992 width=16) (actual time=385.720..386.652 rows=997 loops=1)

213. 0.044 0.044 ↑ 1.0 1 1

Seq Scan on tb_scaling_factor (cost=0.00..1.05 rows=1 width=0) (actual time=0.042..0.044 rows=1 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3
214. 0.266 386.528 ↓ 1.0 997 1

Hash Right Join (cost=1,604.71..1,715.62 rows=992 width=16) (actual time=385.660..386.528 rows=997 loops=1)

  • Hash Cond: ((tb_init_milestone_3.project_id)::text = (tb_init_master_11.project_id)::text)
215. 0.178 2.017 ↓ 1.2 847 1

Hash Join (cost=263.51..364.48 rows=723 width=5) (actual time=1.366..2.017 rows=847 loops=1)

  • Hash Cond: ((tb_init_master_7.project_id)::text = (tb_init_master_8.project_id)::text)
216. 0.178 1.582 ↓ 1.2 847 1

Hash Join (cost=203.19..302.25 rows=723 width=15) (actual time=1.088..1.582 rows=847 loops=1)

  • Hash Cond: ((tb_init_master_7.project_id)::text = (tb_init_milestone_4.project_id)::text)
217. 0.199 1.027 ↑ 1.0 847 1

Hash Join (cost=79.17..175.99 rows=847 width=10) (actual time=0.693..1.027 rows=847 loops=1)

  • Hash Cond: ((tb_init_milestone_3.project_id)::text = (tb_init_master_7.project_id)::text)
218. 0.164 0.568 ↑ 1.0 847 1

Bitmap Heap Scan on tb_init_milestone tb_init_milestone_3 (cost=18.85..113.44 rows=847 width=5) (actual time=0.417..0.568 rows=847 loops=1)

  • Recheck Cond: (value_milestone_id = 1)
  • Heap Blocks: exact=84
219. 0.404 0.404 ↑ 1.0 847 1

Bitmap Index Scan on fk_value_milestone_key_idx_tb_init_milestone (cost=0.00..18.64 rows=847 width=0) (actual time=0.404..0.404 rows=847 loops=1)

  • Index Cond: (value_milestone_id = 1)
220. 0.132 0.260 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.260..0.260 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
221. 0.128 0.128 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_7 (cost=0.00..47.92 rows=992 width=5) (actual time=0.018..0.128 rows=993 loops=1)

222. 0.101 0.377 ↑ 1.0 847 1

Hash (cost=113.44..113.44 rows=847 width=5) (actual time=0.377..0.377 rows=847 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
223. 0.164 0.276 ↑ 1.0 847 1

Bitmap Heap Scan on tb_init_milestone tb_init_milestone_4 (cost=18.85..113.44 rows=847 width=5) (actual time=0.121..0.276 rows=847 loops=1)

  • Recheck Cond: (value_milestone_id = 10)
  • Heap Blocks: exact=84
224. 0.112 0.112 ↑ 1.0 847 1

Bitmap Index Scan on fk_value_milestone_key_idx_tb_init_milestone (cost=0.00..18.64 rows=847 width=0) (actual time=0.112..0.112 rows=847 loops=1)

  • Index Cond: (value_milestone_id = 10)
225. 0.116 0.257 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.257..0.257 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
226. 0.141 0.141 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_8 (cost=0.00..47.92 rows=992 width=5) (actual time=0.034..0.141 rows=993 loops=1)

227. 0.184 384.245 ↓ 1.0 997 1

Hash (cost=1,328.80..1,328.80 rows=992 width=16) (actual time=384.245..384.245 rows=997 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
228. 0.661 384.061 ↓ 1.0 997 1

Merge Left Join (cost=1,248.42..1,328.80 rows=992 width=16) (actual time=378.859..384.061 rows=997 loops=1)

  • Merge Cond: ((tb_init_master_11.project_id)::text = (tb_init_master_15.project_id)::text)
229. 0.715 305.744 ↓ 1.0 997 1

Merge Left Join (cost=1,119.82..1,192.22 rows=992 width=16) (actual time=301.392..305.744 rows=997 loops=1)

  • Merge Cond: ((tb_init_master_11.project_id)::text = (tb_clone_init_master.project_id)::text)
230. 0.833 91.494 ↓ 1.0 993 1

Merge Left Join (cost=466.60..477.06 rows=992 width=438) (actual time=90.402..91.494 rows=993 loops=1)

  • Merge Cond: ((tb_init_master_11.project_id)::text = (tb_init_master_12.project_id)::text)
231. 2.824 5.709 ↓ 1.0 993 1

Sort (cost=338.00..340.48 rows=992 width=16) (actual time=5.651..5.709 rows=993 loops=1)

  • Sort Key: tb_init_master_11.project_id
  • Sort Method: quicksort Memory: 80kB
232. 1.117 2.885 ↓ 1.0 993 1

Hash Left Join (cost=229.88..288.62 rows=992 width=16) (actual time=1.070..2.885 rows=993 loops=1)

  • Hash Cond: (btrim(dim_category_5.category) = btrim(dim_category_6.category))
233. 0.498 0.765 ↓ 1.0 993 1

Hash Left Join (cost=1.68..52.57 rows=992 width=48) (actual time=0.049..0.765 rows=993 loops=1)

  • Hash Cond: (tb_init_master_11.category_id = dim_category_5.category_id)
234. 0.243 0.243 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_11 (cost=0.00..47.92 rows=992 width=36) (actual time=0.015..0.243 rows=993 loops=1)

235. 0.011 0.024 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.024..0.024 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
236. 0.013 0.013 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_5 (cost=0.00..1.30 rows=30 width=36) (actual time=0.011..0.013 rows=30 loops=1)

237. 0.033 1.003 ↓ 6.0 30 1

Hash (cost=228.14..228.14 rows=5 width=32) (actual time=1.003..1.003 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
238. 0.042 0.970 ↓ 6.0 30 1

Hash Join (cost=223.39..228.14 rows=5 width=32) (actual time=0.882..0.970 rows=30 loops=1)

  • Hash Cond: ((hier_3.element)::integer = dim_category_6.category_id)
239. 0.070 0.911 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.41 rows=5 width=392) (actual time=0.851..0.911 rows=30 loops=1)

  • Hash Cond: (tax_3.element_id = hier_3.element_id)
240.          

CTE hier

241. 0.080 0.488 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.016..0.488 rows=192 loops=1)

242. 0.030 0.030 ↑ 1.0 3 1

Seq Scan on tb_hierarchies tb_hierarchies_4 (cost=0.00..4.40 rows=3 width=74) (actual time=0.014..0.030 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
243. 0.192 0.378 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.075..0.126 rows=63 loops=3)

  • Hash Cond: (h_4.parent_id = s_4.element_id)
244. 0.102 0.102 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h_4 (cost=0.00..3.92 rows=192 width=38) (actual time=0.021..0.034 rows=192 loops=3)

245. 0.048 0.084 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.028..0.028 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
246. 0.036 0.036 ↓ 2.1 64 3

WorkTable Scan on hier s_4 (cost=0.00..0.60 rows=30 width=40) (actual time=0.002..0.012 rows=64 loops=3)

247. 0.034 0.034 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax_3 (cost=0.00..3.92 rows=192 width=4) (actual time=0.017..0.034 rows=192 loops=1)

248. 0.019 0.807 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=36) (actual time=0.807..0.807 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
249. 0.156 0.788 ↓ 6.0 30 1

CTE Scan on hier hier_3 (cost=45.77..93.84 rows=5 width=36) (actual time=0.748..0.788 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 13)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
250.          

SubPlan (for CTE Scan)

251. 0.043 0.632 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=0.628..0.632 rows=16 loops=1)

  • Group Key: hier_1_5.parent_id
252. 0.589 0.589 ↑ 10.0 192 1

CTE Scan on hier hier_1_5 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.000..0.589 rows=192 loops=1)

253. 0.008 0.017 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.017..0.017 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
254. 0.009 0.009 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_6 (cost=0.00..1.30 rows=30 width=36) (actual time=0.007..0.009 rows=30 loops=1)

255. 0.121 84.952 ↓ 4.7 942 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=84.745..84.952 rows=942 loops=1)

256. 2.297 84.831 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=84.730..84.831 rows=942 loops=1)

  • Sort Key: tb_init_master_12.project_id
  • Sort Method: quicksort Memory: 98kB
257. 1.525 82.534 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=82.368..82.534 rows=942 loops=1)

  • Group Key: tb_init_master_12.project_id, tb_attr_2.attr_category, tb_attr_2.active
258. 34.577 81.009 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=79.359..81.009 rows=5,757 loops=1)

  • Group Key: tb_init_master_12.project_id, tb_initiative_master_attr_val_2.attr_id, tb_attr_2.attr_category, tb_attr_2.active
259. 10.915 46.432 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=32.834..46.432 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_2.attr_id = tb_attr_2.attr_id)
260. 0.023 0.057 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.047..0.057 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_2.attr_id
  • Sort Method: quicksort Memory: 28kB
261. 0.034 0.034 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_2 (cost=0.00..1.70 rows=70 width=4) (actual time=0.029..0.034 rows=70 loops=1)

262. 4.744 35.460 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=32.784..35.460 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_2.attr_id
  • Sort Method: quicksort Memory: 644kB
263. 5.383 30.716 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.047..30.716 rows=5,757 loops=1)

264. 0.935 2.305 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.030..2.305 rows=5,757 loops=1)

265. 0.040 0.040 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_2 (cost=0.00..1.48 rows=1 width=38) (actual time=0.010..0.040 rows=38 loops=1)

  • Filter: (active = 1)
266. 1.330 1.330 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_2 (cost=0.28..39.48 rows=320 width=13) (actual time=0.003..0.035 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_2.attr_id)
267. 23.028 23.028 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_12 (cost=0.28..0.32 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_2.project_id)::text)
  • Heap Fetches: 5,757
268. 0.162 213.535 ↓ 1.0 995 1

Materialize (cost=653.22..690.37 rows=991 width=5) (actual time=210.987..213.535 rows=995 loops=1)

269. 0.665 213.373 ↓ 1.0 995 1

Merge Left Join (cost=653.22..687.89 rows=991 width=5) (actual time=210.983..213.373 rows=995 loops=1)

  • Merge Cond: ((tb_clone_init_master.project_id)::text = (tb_init_master_14.project_id)::text)
270. 0.865 144.212 ↓ 1.0 995 1

Merge Left Join (cost=524.62..538.41 rows=991 width=443) (actual time=142.723..144.212 rows=995 loops=1)

  • Merge Cond: ((tb_clone_init_master.project_id)::text = (tb_init_master_13.project_id)::text)
271. 0.338 81.880 ↓ 1.0 995 1

Merge Left Join (cost=396.02..401.83 rows=991 width=5) (actual time=81.478..81.880 rows=995 loops=1)

  • Merge Cond: ((tb_clone_init_master.project_id)::text = (tb_ctachieve.project_id)::text)
272. 2.257 81.443 ↑ 1.0 991 1

Sort (cost=392.78..395.26 rows=991 width=5) (actual time=81.381..81.443 rows=991 loops=1)

  • Sort Key: tb_clone_init_master.project_id
  • Sort Method: quicksort Memory: 71kB
273. 0.542 79.186 ↑ 1.0 991 1

Hash Left Join (cost=229.88..343.47 rows=991 width=5) (actual time=78.138..79.186 rows=991 loops=1)

  • Hash Cond: (btrim(dim_category_7.category) = btrim(dim_category_8.category))
274. 0.165 77.935 ↑ 1.0 991 1

Hash Left Join (cost=1.68..107.42 rows=991 width=37) (actual time=77.416..77.935 rows=991 loops=1)

  • Hash Cond: (tb_clone_init_master.category_id = dim_category_7.category_id)
275. 77.702 77.702 ↑ 1.0 991 1

Seq Scan on tb_clone_init_master (cost=0.00..102.78 rows=991 width=25) (actual time=77.331..77.702 rows=991 loops=1)

  • Filter: (batch_tm = $18)
  • Rows Removed by Filter: 991
276. 0.015 0.068 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.068..0.068 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
277. 0.053 0.053 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_7 (cost=0.00..1.30 rows=30 width=36) (actual time=0.048..0.053 rows=30 loops=1)

278. 0.015 0.709 ↓ 6.0 30 1

Hash (cost=228.14..228.14 rows=5 width=32) (actual time=0.709..0.709 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
279. 0.026 0.694 ↓ 6.0 30 1

Hash Join (cost=223.39..228.14 rows=5 width=32) (actual time=0.657..0.694 rows=30 loops=1)

  • Hash Cond: ((hier_4.element)::integer = dim_category_8.category_id)
280. 0.046 0.643 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.41 rows=5 width=392) (actual time=0.620..0.643 rows=30 loops=1)

  • Hash Cond: (tax_4.element_id = hier_4.element_id)
281.          

CTE hier

282. 0.067 0.344 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.032..0.344 rows=192 loops=1)

283. 0.043 0.043 ↑ 1.0 3 1

Seq Scan on tb_hierarchies tb_hierarchies_5 (cost=0.00..4.40 rows=3 width=74) (actual time=0.026..0.043 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
284. 0.135 0.234 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.040..0.078 rows=63 loops=3)

  • Hash Cond: (h_5.parent_id = s_5.element_id)
285. 0.042 0.042 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h_5 (cost=0.00..3.92 rows=192 width=38) (actual time=0.005..0.014 rows=192 loops=3)

286. 0.036 0.057 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.019..0.019 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
287. 0.021 0.021 ↓ 2.1 64 3

WorkTable Scan on hier s_5 (cost=0.00..0.60 rows=30 width=40) (actual time=0.001..0.007 rows=64 loops=3)

288. 0.016 0.016 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax_4 (cost=0.00..3.92 rows=192 width=4) (actual time=0.008..0.016 rows=192 loops=1)

289. 0.011 0.581 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=36) (actual time=0.581..0.581 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
290. 0.078 0.570 ↓ 6.0 30 1

CTE Scan on hier hier_4 (cost=45.77..93.84 rows=5 width=36) (actual time=0.552..0.570 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 15)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
291.          

SubPlan (for CTE Scan)

292. 0.033 0.492 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=0.490..0.492 rows=16 loops=1)

  • Group Key: hier_1_6.parent_id
293. 0.459 0.459 ↑ 10.0 192 1

CTE Scan on hier hier_1_6 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.000..0.459 rows=192 loops=1)

294. 0.007 0.025 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.025..0.025 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
295. 0.018 0.018 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_8 (cost=0.00..1.30 rows=30 width=36) (actual time=0.012..0.018 rows=30 loops=1)

296. 0.076 0.099 ↑ 1.0 57 1

Sort (cost=3.23..3.37 rows=57 width=9) (actual time=0.094..0.099 rows=57 loops=1)

  • Sort Key: tb_ctachieve.project_id
  • Sort Method: quicksort Memory: 27kB
297. 0.023 0.023 ↑ 1.0 57 1

Seq Scan on tb_ctachieve (cost=0.00..1.57 rows=57 width=9) (actual time=0.015..0.023 rows=57 loops=1)

298. 0.132 61.467 ↓ 4.7 943 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=61.239..61.467 rows=943 loops=1)

299. 4.208 61.335 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=61.235..61.335 rows=942 loops=1)

  • Sort Key: tb_init_master_13.project_id
  • Sort Method: quicksort Memory: 98kB
300. 1.604 57.127 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=56.962..57.127 rows=942 loops=1)

  • Group Key: tb_init_master_13.project_id, tb_attr_3.attr_category, tb_attr_3.active
301. 23.322 55.523 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=53.502..55.523 rows=5,757 loops=1)

  • Group Key: tb_init_master_13.project_id, tb_initiative_master_attr_val_3.attr_id, tb_attr_3.attr_category, tb_attr_3.active
302. 7.472 32.201 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=22.803..32.201 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_3.attr_id = tb_attr_3.attr_id)
303. 0.023 0.039 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.030..0.039 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_3.attr_id
  • Sort Method: quicksort Memory: 28kB
304. 0.016 0.016 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_3 (cost=0.00..1.70 rows=70 width=4) (actual time=0.013..0.016 rows=70 loops=1)

305. 3.500 24.690 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=22.770..24.690 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_3.attr_id
  • Sort Method: quicksort Memory: 644kB
306. 2.363 21.190 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.059..21.190 rows=5,757 loops=1)

307. 0.623 1.556 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.026..1.556 rows=5,757 loops=1)

308. 0.021 0.021 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_3 (cost=0.00..1.48 rows=1 width=38) (actual time=0.009..0.021 rows=38 loops=1)

  • Filter: (active = 1)
309. 0.912 0.912 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_3 (cost=0.28..39.48 rows=320 width=13) (actual time=0.002..0.024 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_3.attr_id)
310. 17.271 17.271 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_13 (cost=0.28..0.32 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_3.project_id)::text)
  • Heap Fetches: 5,757
311. 0.133 68.496 ↓ 4.7 943 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=68.256..68.496 rows=943 loops=1)

312. 2.211 68.363 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=68.252..68.363 rows=942 loops=1)

  • Sort Key: tb_init_master_14.project_id
  • Sort Method: quicksort Memory: 98kB
313. 1.510 66.152 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=65.986..66.152 rows=942 loops=1)

  • Group Key: tb_init_master_14.project_id, tb_attr_4.attr_category, tb_attr_4.active
314. 28.541 64.642 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=63.049..64.642 rows=5,757 loops=1)

  • Group Key: tb_init_master_14.project_id, tb_initiative_master_attr_val_4.attr_id, tb_attr_4.attr_category, tb_attr_4.active
315. 8.798 36.101 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=25.202..36.101 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_4.attr_id = tb_attr_4.attr_id)
316. 0.029 0.064 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.054..0.064 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_4.attr_id
  • Sort Method: quicksort Memory: 28kB
317. 0.035 0.035 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_4 (cost=0.00..1.70 rows=70 width=4) (actual time=0.030..0.035 rows=70 loops=1)

318. 3.739 27.239 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=25.144..27.239 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_4.attr_id
  • Sort Method: quicksort Memory: 644kB
319. 4.503 23.500 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.075..23.500 rows=5,757 loops=1)

320. 0.700 1.726 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.046..1.726 rows=5,757 loops=1)

321. 0.038 0.038 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_4 (cost=0.00..1.48 rows=1 width=38) (actual time=0.015..0.038 rows=38 loops=1)

  • Filter: (active = 1)
322. 0.988 0.988 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_4 (cost=0.28..39.48 rows=320 width=13) (actual time=0.003..0.026 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_4.attr_id)
323. 17.271 17.271 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_14 (cost=0.28..0.32 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_4.project_id)::text)
  • Heap Fetches: 5,757
324. 0.109 77.656 ↓ 4.7 943 1

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=77.462..77.656 rows=943 loops=1)

325. 2.305 77.547 ↓ 4.7 942 1

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=77.458..77.547 rows=942 loops=1)

  • Sort Key: tb_init_master_15.project_id
  • Sort Method: quicksort Memory: 98kB
326. 1.952 75.242 ↓ 4.7 942 1

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=75.038..75.242 rows=942 loops=1)

  • Group Key: tb_init_master_15.project_id, tb_attr_5.attr_category, tb_attr_5.active
327. 33.660 73.290 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=70.863..73.290 rows=5,757 loops=1)

  • Group Key: tb_init_master_15.project_id, tb_initiative_master_attr_val_5.attr_id, tb_attr_5.attr_category, tb_attr_5.active
328. 9.924 39.630 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=27.247..39.630 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_5.attr_id = tb_attr_5.attr_id)
329. 0.025 0.060 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.048..0.060 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_5.attr_id
  • Sort Method: quicksort Memory: 28kB
330. 0.035 0.035 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_5 (cost=0.00..1.70 rows=70 width=4) (actual time=0.030..0.035 rows=70 loops=1)

331. 4.311 29.646 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=27.195..29.646 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_5.attr_id
  • Sort Method: quicksort Memory: 644kB
332. 0.342 25.335 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.070..25.335 rows=5,757 loops=1)

333. 0.795 1.965 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.029..1.965 rows=5,757 loops=1)

334. 0.030 0.030 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_5 (cost=0.00..1.48 rows=1 width=38) (actual time=0.012..0.030 rows=38 loops=1)

  • Filter: (active = 1)
335. 1.140 1.140 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_5 (cost=0.28..39.48 rows=320 width=13) (actual time=0.003..0.030 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_5.attr_id)
336. 23.028 23.028 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_15 (cost=0.28..0.32 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_5.project_id)::text)
  • Heap Fetches: 5,757
337. 0.000 37.625 ↓ 37.1 1,560 1

Hash Left Join (cost=1,182.73..1,700.34 rows=42 width=49) (actual time=13.842..37.625 rows=1,560 loops=1)

  • Hash Cond: ((tb_init_milestone_7.project_id)::text = (last_vm_1.project_id)::text)
338.          

Initplan (for Hash Left Join)

339. 3.021 4.780 ↑ 1.0 1 1

Aggregate (cost=412.50..412.51 rows=1 width=8) (actual time=4.779..4.780 rows=1 loops=1)

340. 1.759 1.759 ↑ 1.0 16,920 1

Seq Scan on tb_clone_init_milestone tb_clone_init_milestone_3 (cost=0.00..370.20 rows=16,920 width=8) (actual time=0.011..1.759 rows=16,920 loops=1)

341. 0.000 34.135 ↓ 37.1 1,560 1

Nested Loop (cost=480.97..998.47 rows=42 width=5) (actual time=11.017..34.135 rows=1,560 loops=1)

342. 0.000 28.050 ↓ 37.1 1,560 1

Nested Loop Left Join (cost=480.69..985.34 rows=42 width=5) (actual time=10.997..28.050 rows=1,560 loops=1)

343. 1.665 14.191 ↓ 37.1 1,560 1

Hash Right Join (cost=480.13..956.50 rows=42 width=9) (actual time=10.959..14.191 rows=1,560 loops=1)

  • Hash Cond: (((tb_clone_init_milestone_1.project_id)::text = (tb_init_milestone_7.project_id)::text) AND (tb_clone_init_milestone_1.value_milestone_id = tb_init_milestone_7.value_milestone_id))
344. 7.339 7.339 ↑ 1.0 8,460 1

Seq Scan on tb_clone_init_milestone tb_clone_init_milestone_1 (cost=0.00..412.50 rows=8,460 width=9) (actual time=5.685..7.339 rows=8,460 loops=1)

  • Filter: (batch_tm = $25)
  • Rows Removed by Filter: 8,460
345. 0.252 5.187 ↓ 37.1 1,560 1

Hash (cost=479.50..479.50 rows=42 width=9) (actual time=5.187..5.187 rows=1,560 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 84kB
346. 1.499 4.935 ↓ 37.1 1,560 1

Hash Left Join (cost=288.45..479.50 rows=42 width=9) (actual time=2.879..4.935 rows=1,560 loops=1)

  • Hash Cond: ((tb_init_milestone_7.project_id)::text = (tb_init_milestone_9.project_id)::text)
  • Filter: ((min(tb_init_milestone_9.value_milestone_id)) IS NULL)
  • Rows Removed by Filter: 6,910
347. 0.587 0.587 ↑ 1.0 8,470 1

Seq Scan on tb_init_milestone tb_init_milestone_7 (cost=0.00..168.70 rows=8,470 width=9) (actual time=0.021..0.587 rows=8,470 loops=1)

348. 0.095 2.849 ↑ 1.2 691 1

Hash (cost=277.88..277.88 rows=846 width=9) (actual time=2.849..2.849 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
349. 0.864 2.754 ↑ 1.2 691 1

HashAggregate (cost=260.96..269.42 rows=846 width=9) (actual time=2.672..2.754 rows=691 loops=1)

  • Group Key: tb_init_milestone_9.project_id
350. 0.742 1.890 ↑ 1.0 4,183 1

Hash Join (cost=60.32..240.04 rows=4,183 width=9) (actual time=0.263..1.890 rows=4,183 loops=1)

  • Hash Cond: ((tb_init_milestone_9.project_id)::text = (tb_init_master_18.project_id)::text)
351. 0.902 0.902 ↑ 1.0 4,183 1

Seq Scan on tb_init_milestone tb_init_milestone_9 (cost=0.00..168.70 rows=4,183 width=9) (actual time=0.009..0.902 rows=4,183 loops=1)

  • Filter: (actual_dt IS NULL)
  • Rows Removed by Filter: 4,287
352. 0.116 0.246 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.246..0.246 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
353. 0.130 0.130 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_18 (cost=0.00..47.92 rows=992 width=5) (actual time=0.011..0.130 rows=993 loops=1)

354. 0.624 14.040 ↑ 1.0 1 1,560

Nested Loop (cost=0.56..0.68 rows=1 width=9) (actual time=0.008..0.009 rows=1 loops=1,560)

355. 7.800 7.800 ↑ 1.0 1 1,560

Index Only Scan using uq_tb_init_milestone_pk_project_id_value_milestone_id on tb_init_milestone tb_init_milestone_8 (cost=0.29..0.36 rows=1 width=9) (actual time=0.004..0.005 rows=1 loops=1,560)

  • Index Cond: ((project_id = (tb_init_milestone_7.project_id)::text) AND (value_milestone_id = (tb_init_milestone_7.value_milestone_id - 1)))
  • Heap Fetches: 1,404
356. 5.616 5.616 ↑ 1.0 1 1,404

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_17 (cost=0.28..0.31 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=1,404)

  • Index Cond: (project_id = (tb_init_milestone_8.project_id)::text)
  • Heap Fetches: 1,404
357. 6.240 6.240 ↑ 1.0 1 1,560

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_16 (cost=0.28..0.31 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=1,560)

  • Index Cond: (project_id = (tb_init_milestone_7.project_id)::text)
  • Heap Fetches: 1,560
358. 0.117 2.809 ↓ 1.0 847 1

Hash (cost=278.67..278.67 rows=846 width=9) (actual time=2.809..2.809 rows=847 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
359. 0.052 2.692 ↓ 1.0 847 1

Subquery Scan on last_vm_1 (cost=261.75..278.67 rows=846 width=9) (actual time=2.549..2.692 rows=847 loops=1)

360. 0.833 2.640 ↓ 1.0 847 1

HashAggregate (cost=261.75..270.21 rows=846 width=9) (actual time=2.548..2.640 rows=847 loops=1)

  • Group Key: tb_init_milestone_10.project_id
361. 0.732 1.807 ↑ 1.0 4,287 1

Hash Join (cost=60.32..240.32 rows=4,287 width=9) (actual time=0.281..1.807 rows=4,287 loops=1)

  • Hash Cond: ((tb_init_milestone_10.project_id)::text = (tb_init_master_19.project_id)::text)
362. 0.855 0.855 ↑ 1.0 4,287 1

Seq Scan on tb_init_milestone tb_init_milestone_10 (cost=0.00..168.70 rows=4,287 width=9) (actual time=0.019..0.855 rows=4,287 loops=1)

  • Filter: (actual_dt IS NOT NULL)
  • Rows Removed by Filter: 4,183
363. 0.109 0.220 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.220..0.220 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
364. 0.111 0.111 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_19 (cost=0.00..47.92 rows=992 width=5) (actual time=0.010..0.111 rows=993 loops=1)

365. 0.464 20.166 ↑ 1.0 3,690 1

Materialize (cost=326.68..382.03 rows=3,690 width=37) (actual time=19.554..20.166 rows=3,690 loops=1)

366. 15.881 19.702 ↑ 1.0 3,690 1

Sort (cost=326.68..335.90 rows=3,690 width=305) (actual time=19.542..19.702 rows=3,690 loops=1)

  • Sort Key: tb_init_val_1.project_id, tb_init_val_1.init_value_line
  • Sort Method: quicksort Memory: 385kB
367. 1.880 3.821 ↑ 1.0 3,690 1

Hash Left Join (cost=1.16..108.06 rows=3,690 width=305) (actual time=0.255..3.821 rows=3,690 loops=1)

  • Hash Cond: ((tb_init_val_1.currency_id)::text = (dim_currency_1.currency_key)::text)
368. 1.837 1.837 ↑ 1.0 3,690 1

Seq Scan on tb_init_val tb_init_val_1 (cost=0.00..72.90 rows=3,690 width=29) (actual time=0.125..1.837 rows=3,690 loops=1)

369. 0.011 0.104 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=36) (actual time=0.104..0.104 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
370. 0.093 0.093 ↑ 1.0 7 1

Seq Scan on dim_currency dim_currency_1 (cost=0.00..1.07 rows=7 width=36) (actual time=0.092..0.093 rows=7 loops=1)

371. 0.112 6.774 ↑ 1.0 847 1

Hash (cost=331.82..331.82 rows=847 width=13) (actual time=6.774..6.774 rows=847 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
372. 0.048 6.662 ↑ 1.0 847 1

Subquery Scan on e (cost=314.88..331.82 rows=847 width=13) (actual time=6.513..6.662 rows=847 loops=1)

373. 2.646 6.614 ↑ 1.0 847 1

HashAggregate (cost=314.88..323.35 rows=847 width=13) (actual time=6.512..6.614 rows=847 loops=1)

  • Group Key: tb_init_milestone_11.project_id
374. 2.857 3.968 ↑ 1.0 8,470 1

Hash Join (cost=60.32..251.35 rows=8,470 width=13) (actual time=0.291..3.968 rows=8,470 loops=1)

  • Hash Cond: ((tb_init_milestone_11.project_id)::text = (tb_init_master_20.project_id)::text)
375. 0.850 0.850 ↑ 1.0 8,470 1

Seq Scan on tb_init_milestone tb_init_milestone_11 (cost=0.00..168.70 rows=8,470 width=13) (actual time=0.019..0.850 rows=8,470 loops=1)

376. 0.127 0.261 ↓ 1.0 993 1

Hash (cost=47.92..47.92 rows=992 width=5) (actual time=0.261..0.261 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
377. 0.134 0.134 ↓ 1.0 993 1

Seq Scan on tb_init_master tb_init_master_20 (cost=0.00..47.92 rows=992 width=5) (actual time=0.011..0.134 rows=993 loops=1)

378.          

SubPlan (for GroupAggregate)

379. 1.764 1,375.479 ↑ 1.0 1 441

Aggregate (cost=373.73..373.74 rows=1 width=8) (actual time=3.119..3.119 rows=1 loops=441)

380. 11.099 1,373.715 ↑ 1.0 2 441

Nested Loop Left Join (cost=362.94..373.70 rows=2 width=459) (actual time=3.052..3.115 rows=2 loops=441)

  • Join Filter: (btrim(dim_category_10.category) = btrim(dim_category_9.category))
  • Rows Removed by Join Filter: 56
381. 3.124 1,360.926 ↑ 1.0 2 441

Nested Loop Left Join (cost=139.55..145.35 rows=2 width=37) (actual time=3.041..3.086 rows=2 loops=441)

  • Join Filter: (dim_category_9.category_id = tb_init_master_21.category_id)
  • Rows Removed by Join Filter: 26
382. 76.293 1,356.957 ↑ 1.0 2 441

Merge Left Join (cost=139.55..143.07 rows=2 width=9) (actual time=3.035..3.077 rows=2 loops=441)

  • Merge Cond: ((tb_init_master_21.project_id)::text = (tb_init_master_22.project_id)::text)
383. 9.261 15.435 ↑ 1.0 2 441

Sort (cost=10.95..10.95 rows=2 width=25) (actual time=0.035..0.035 rows=2 loops=441)

  • Sort Key: tb_init_master_21.project_id
  • Sort Method: quicksort Memory: 25kB
384. 1.764 6.174 ↑ 1.0 2 441

Bitmap Heap Scan on tb_init_master tb_init_master_21 (cost=4.29..10.94 rows=2 width=25) (actual time=0.014..0.014 rows=2 loops=441)

  • Recheck Cond: (group_id = tb_init_master_3.group_id)
  • Heap Blocks: exact=600
385. 4.410 4.410 ↑ 1.0 2 441

Bitmap Index Scan on vw_tb_init_master_idx_group_id (cost=0.00..4.29 rows=2 width=0) (actual time=0.010..0.010 rows=2 loops=441)

  • Index Cond: (group_id = tb_init_master_3.group_id)
386. 24.255 1,265.229 ↓ 2.6 513 441

Materialize (cost=128.60..131.60 rows=200 width=5) (actual time=2.791..2.869 rows=513 loops=441)

387. 1,054.872 1,240.974 ↓ 2.6 513 441

Sort (cost=128.60..129.10 rows=200 width=455) (actual time=2.789..2.814 rows=513 loops=441)

  • Sort Key: tb_init_master_22.project_id
  • Sort Method: quicksort Memory: 98kB
388. 98.234 186.102 ↓ 4.7 942 441

HashAggregate (cost=118.96..120.96 rows=200 width=455) (actual time=0.203..0.422 rows=942 loops=441)

  • Group Key: tb_init_master_22.project_id, tb_attr_6.attr_category, tb_attr_6.active
389. 36.211 87.868 ↓ 20.6 5,757 1

HashAggregate (cost=111.26..114.06 rows=280 width=459) (actual time=86.293..87.868 rows=5,757 loops=1)

  • Group Key: tb_init_master_22.project_id, tb_initiative_master_attr_val_6.attr_id, tb_attr_6.attr_category, tb_attr_6.active
390. 11.452 51.657 ↓ 214.2 59,982 1

Merge Right Join (cost=102.51..107.06 rows=280 width=47) (actual time=37.416..51.657 rows=59,982 loops=1)

  • Merge Cond: (tb_attr_lkp_1_6.attr_id = tb_attr_6.attr_id)
391. 0.029 0.119 ↑ 1.0 70 1

Sort (cost=3.85..4.02 rows=70 width=4) (actual time=0.109..0.119 rows=70 loops=1)

  • Sort Key: tb_attr_lkp_1_6.attr_id
  • Sort Method: quicksort Memory: 28kB
392. 0.090 0.090 ↑ 1.0 70 1

Seq Scan on tb_attr_lkp tb_attr_lkp_1_6 (cost=0.00..1.70 rows=70 width=4) (actual time=0.023..0.090 rows=70 loops=1)

393. 5.110 40.086 ↓ 394.6 59,982 1

Sort (cost=98.66..99.04 rows=152 width=51) (actual time=37.303..40.086 rows=59,982 loops=1)

  • Sort Key: tb_initiative_master_attr_val_6.attr_id
  • Sort Method: quicksort Memory: 644kB
394. 3.562 34.976 ↓ 37.9 5,757 1

Nested Loop (cost=0.56..93.15 rows=152 width=51) (actual time=0.072..34.976 rows=5,757 loops=1)

395. 1.142 2.629 ↓ 37.9 5,757 1

Nested Loop (cost=0.28..44.15 rows=152 width=51) (actual time=0.050..2.629 rows=5,757 loops=1)

396. 0.043 0.043 ↓ 38.0 38 1

Seq Scan on tb_attr tb_attr_6 (cost=0.00..1.48 rows=1 width=38) (actual time=0.024..0.043 rows=38 loops=1)

  • Filter: (active = 1)
397. 1.444 1.444 ↑ 2.1 152 38

Index Scan using fk_init_mstr_attr_id_idx_tb_initiative_master_attr_val on tb_initiative_master_attr_val tb_initiative_master_attr_val_6 (cost=0.28..39.48 rows=320 width=13) (actual time=0.003..0.038 rows=152 loops=38)

  • Index Cond: (attr_id = tb_attr_6.attr_id)
398. 28.785 28.785 ↑ 1.0 1 5,757

Index Only Scan using pk_tb_init_master on tb_init_master tb_init_master_22 (cost=0.28..0.32 rows=1 width=5) (actual time=0.005..0.005 rows=1 loops=5,757)

  • Index Cond: (project_id = (tb_initiative_master_attr_val_6.project_id)::text)
  • Heap Fetches: 5,757
399. 0.797 0.845 ↑ 2.1 14 845

Materialize (cost=0.00..1.45 rows=30 width=36) (actual time=0.000..0.001 rows=14 loops=845)

400. 0.048 0.048 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_9 (cost=0.00..1.30 rows=30 width=36) (actual time=0.041..0.048 rows=30 loops=1)

401. 1.009 1.690 ↓ 6.0 30 845

Materialize (cost=223.39..228.17 rows=5 width=32) (actual time=0.001..0.002 rows=30 loops=845)

402. 0.056 0.681 ↓ 6.0 30 1

Hash Join (cost=223.39..228.14 rows=5 width=32) (actual time=0.644..0.681 rows=30 loops=1)

  • Hash Cond: ((hier_5.element)::integer = dim_category_10.category_id)
403. 0.039 0.599 ↓ 6.0 30 1

Hash Right Join (cost=221.72..226.41 rows=5 width=392) (actual time=0.575..0.599 rows=30 loops=1)

  • Hash Cond: (tax_5.element_id = hier_5.element_id)
404.          

CTE hier

405. 0.063 0.370 ↑ 10.0 192 1

Recursive Union (cost=0.00..127.81 rows=1,923 width=74) (actual time=0.017..0.370 rows=192 loops=1)

406. 0.031 0.031 ↑ 1.0 3 1

Seq Scan on tb_hierarchies (cost=0.00..4.40 rows=3 width=74) (actual time=0.016..0.031 rows=3 loops=1)

  • Filter: (parent_id = 0)
  • Rows Removed by Filter: 189
407. 0.183 0.276 ↑ 3.0 63 3

Hash Join (cost=0.97..8.50 rows=192 width=74) (actual time=0.055..0.092 rows=63 loops=3)

  • Hash Cond: (h.parent_id = s.element_id)
408. 0.036 0.036 ↑ 1.0 192 3

Seq Scan on tb_hierarchies h (cost=0.00..3.92 rows=192 width=38) (actual time=0.004..0.012 rows=192 loops=3)

409. 0.030 0.057 ↓ 2.1 64 3

Hash (cost=0.60..0.60 rows=30 width=40) (actual time=0.019..0.019 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
410. 0.027 0.027 ↓ 2.1 64 3

WorkTable Scan on hier s (cost=0.00..0.60 rows=30 width=40) (actual time=0.003..0.009 rows=64 loops=3)

411. 0.019 0.019 ↑ 1.0 192 1

Seq Scan on tb_hierarchies tax_5 (cost=0.00..3.92 rows=192 width=4) (actual time=0.010..0.019 rows=192 loops=1)

412. 0.004 0.541 ↓ 6.0 30 1

Hash (cost=93.84..93.84 rows=5 width=36) (actual time=0.541..0.541 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
413. 0.060 0.537 ↓ 6.0 30 1

CTE Scan on hier hier_5 (cost=45.77..93.84 rows=5 width=36) (actual time=0.519..0.537 rows=30 loops=1)

  • Filter: ((NOT (hashed SubPlan 2)) AND (hierarchy_type = 'taxonomy'::text))
  • Rows Removed by Filter: 162
414.          

SubPlan (for CTE Scan)

415. 0.034 0.477 ↑ 12.5 16 1

HashAggregate (cost=43.27..45.27 rows=200 width=4) (actual time=0.475..0.477 rows=16 loops=1)

  • Group Key: hier_1_1.parent_id
416. 0.443 0.443 ↑ 10.0 192 1

CTE Scan on hier hier_1_1 (cost=0.00..38.46 rows=1,923 width=4) (actual time=0.000..0.443 rows=192 loops=1)

417. 0.015 0.026 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=36) (actual time=0.026..0.026 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
418. 0.011 0.011 ↑ 1.0 30 1

Seq Scan on dim_category dim_category_10 (cost=0.00..1.30 rows=30 width=36) (actual time=0.009..0.011 rows=30 loops=1)

Planning time : 82.358 ms