explain.depesz.com

A tool for finding a real cause for slow queries.

Result: T9g

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop (cost=80171687.68..80171734.13 rows=1 width=1180) (actual time=.. rows= loops=)

2.          

Initplan (forNested Loop)

3. 0.000 0.000 ↓ 0.0

Aggregate (cost=40084042.32..40084042.33 rows=1 width=8) (actual time=.. rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=40083995.86..40084042.32 rows=1 width=8) (actual time=.. rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Join (cost=40083986.36..40084032.79 rows=1 width=528) (actual time=.. rows= loops=)

  • Hash Cond: (s.id = eprs.schedule_id)
6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=61.06..107.38 rows=7 width=126) (actual time=.. rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..60.92 rows=7 width=126) (actual time=.. rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_account_id_index on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
  • Filter: (pricing_program_id = 2)
9. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.33 rows=7 width=106) (actual time=.. rows= loops=)

  • Filter: (s.pricing_program_id = 2)
10.          

SubPlan (forNested Loop)

11. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $4) AND (pricing_program_id = $5))
13. 0.000 0.000 ↓ 0.0

Hash (cost=40083925.28..40083925.28 rows=1 width=532) (actual time=.. rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Join (cost=40083925.23..40083925.28 rows=1 width=532) (actual time=.. rows= loops=)

  • Merge Cond: ((eprs.schedule_id = rateforacc2_.schedule_id) AND ((eprs.rate_key)::text = (rateforacc2_.rate_key)::text))
15. 0.000 0.000 ↓ 0.0

Sort (cost=4109240.44..4109240.45 rows=4 width=1044) (actual time=.. rows= loops=)

  • Sort Key: eprs.schedule_id, eprs.rate_key
16. 0.000 0.000 ↓ 0.0

Subquery Scan eprs (cost=4109240.32..4109240.40 rows=4 width=1044) (actual time=.. rows= loops=)

17. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4109240.32..4109240.36 rows=4 width=554) (actual time=.. rows= loops=)

18. 0.000 0.000 ↓ 0.0

Append (cost=2054620.09..4109240.22 rows=4 width=554) (actual time=.. rows= loops=)

19. 0.000 0.000 ↓ 0.0

Result (cost=2054620.09..2054620.14 rows=2 width=290) (actual time=.. rows= loops=)

20. 0.000 0.000 ↓ 0.0

Unique (cost=2054620.09..2054620.14 rows=2 width=290) (actual time=.. rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=2054620.09..2054620.09 rows=2 width=290) (actual time=.. rows= loops=)

  • Sort Key: "*SELECT* 1".rid, "*SELECT* 1".schedule_id, "*SELECT* 1".rate_key, "*SELECT* 1".account_id, (NULL::text), "*SELECT* 1".effective_date, (NULL::bigint), (NULL::bigint), ('IBR'::text), "*SELECT* 1".group_order
22. 0.000 0.000 ↓ 0.0

Append (cost=1027302.93..2054620.08 rows=2 width=290) (actual time=.. rows= loops=)

23. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=1027302.93..1027302.95 rows=1 width=538) (actual time=.. rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1027302.93..1027302.94 rows=1 width=538) (actual time=.. rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.25..1027302.91 rows=1 width=538) (actual time=.. rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.25..1027302.57 rows=1 width=538) (actual time=.. rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=1027294.25..1027294.26 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
28. 0.000 0.000 ↓ 0.0

Merge Join (cost=1019825.99..1027294.24 rows=1 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
29. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
31. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=988147.31..988147.31 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
33. 0.000 0.000 ↓ 0.0

HashAggregate (cost=988147.28..988147.29 rows=1 width=53) (actual time=.. rows= loops=)

34. 0.000 0.000 ↓ 0.0

Merge Join (cost=983096.85..988143.04 rows=242 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 7)) IS NULL) OR (sr3.effective_date < ((SubPlan 7))))
35. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 7))
37. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
38. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

41.          

SubPlan (forHash Join)

42. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $2) AND (pricing_program_id = $3))
44. 0.000 0.000 ↓ 0.0

Sort (cost=35.03..35.03 rows=1 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
45. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=31.00..35.02 rows=1 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: (((account_id)::text = '30328775762641203278'::text) AND (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone))
46. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=31.00..31.00 rows=1 width=0) (actual time=.. rows= loops=)

47. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.39 rows=14 width=0) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
48. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_effective_date_index (cost=0.00..26.36 rows=1345 width=0) (actual time=.. rows= loops=)

  • Index Cond: (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone)
49. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
50. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=4) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NOT NULL) OR (p.consumption_percentage_from IS NOT NULL))
51. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=1027317.10..1027317.12 rows=1 width=42) (actual time=.. rows= loops=)

52. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1027317.10..1027317.11 rows=1 width=42) (actual time=.. rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027317.08 rows=1 width=42) (actual time=.. rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027316.74 rows=1 width=42) (actual time=.. rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027308.44 rows=1 width=36) (actual time=.. rows= loops=)

  • Join Filter: (s.pricing_program_id = ppe.pricing_program_id)
56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027302.15 rows=1 width=20) (actual time=.. rows= loops=)

  • Join Filter: (sr3.schedule_id = s.id)
57. 0.000 0.000 ↓ 0.0

Sort (cost=1027294.75..1027294.76 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
58. 0.000 0.000 ↓ 0.0

Merge Join (cost=1019826.49..1027294.74 rows=1 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
59. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

60. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
61. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

62. 0.000 0.000 ↓ 0.0

Sort (cost=988147.81..988147.81 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
63. 0.000 0.000 ↓ 0.0

HashAggregate (cost=988147.78..988147.79 rows=1 width=53) (actual time=.. rows= loops=)

64. 0.000 0.000 ↓ 0.0

Merge Join (cost=983097.35..988143.54 rows=242 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 6)) IS NULL) OR (sr3.effective_date < ((SubPlan 6))))
65. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 6))
67. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
68. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

71.          

SubPlan (forHash Join)

72. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $2) AND (pricing_program_id = $3))
74. 0.000 0.000 ↓ 0.0

Sort (cost=35.53..35.53 rows=1 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
75. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=31.50..35.52 rows=1 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: ((account_id IS NULL) AND (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone))
76. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=31.50..31.50 rows=1 width=0) (actual time=.. rows= loops=)

77. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.89 rows=81 width=0) (actual time=.. rows= loops=)

  • Index Cond: (account_id IS NULL)
78. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_effective_date_index (cost=0.00..26.36 rows=1345 width=0) (actual time=.. rows= loops=)

  • Index Cond: (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone)
79. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=8) (actual time=.. rows= loops=)

80. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_account_id_index on pricing_program_enrollment ppe (cost=0.00..6.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((ppe.account_id)::text = '30328775762641203278'::text)
81. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
82. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=4) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NOT NULL) OR (p.consumption_percentage_from IS NOT NULL))
83. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 3" (cost=1027302.93..1027302.95 rows=1 width=1066) (actual time=.. rows= loops=)

84. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1027302.93..1027302.94 rows=1 width=1066) (actual time=.. rows= loops=)

85. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.25..1027302.91 rows=1 width=1066) (actual time=.. rows= loops=)

86. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.25..1027302.57 rows=1 width=538) (actual time=.. rows= loops=)

87. 0.000 0.000 ↓ 0.0

Sort (cost=1027294.25..1027294.26 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
88. 0.000 0.000 ↓ 0.0

Merge Join (cost=1019825.99..1027294.24 rows=1 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
89. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

90. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
91. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

92. 0.000 0.000 ↓ 0.0

Sort (cost=988147.31..988147.31 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
93. 0.000 0.000 ↓ 0.0

HashAggregate (cost=988147.28..988147.29 rows=1 width=53) (actual time=.. rows= loops=)

94. 0.000 0.000 ↓ 0.0

Merge Join (cost=983096.85..988143.04 rows=242 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 5)) IS NULL) OR (sr3.effective_date < ((SubPlan 5))))
95. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

96. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 5))
97. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
98. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

99. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

100. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

101.          

SubPlan (forHash Join)

102. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

103. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $2) AND (pricing_program_id = $3))
104. 0.000 0.000 ↓ 0.0

Sort (cost=35.03..35.03 rows=1 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
105. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=31.00..35.02 rows=1 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: (((account_id)::text = '30328775762641203278'::text) AND (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone))
106. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=31.00..31.00 rows=1 width=0) (actual time=.. rows= loops=)

107. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.39 rows=14 width=0) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
108. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_effective_date_index (cost=0.00..26.36 rows=1345 width=0) (actual time=.. rows= loops=)

  • Index Cond: (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone)
109. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
110. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=532) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NULL) AND (p.consumption_percentage_from IS NULL))
111. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 4" (cost=1027317.10..1027317.12 rows=1 width=570) (actual time=.. rows= loops=)

112. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1027317.10..1027317.11 rows=1 width=570) (actual time=.. rows= loops=)

113. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027317.08 rows=1 width=570) (actual time=.. rows= loops=)

114. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027316.74 rows=1 width=42) (actual time=.. rows= loops=)

115. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027308.44 rows=1 width=36) (actual time=.. rows= loops=)

  • Join Filter: (s.pricing_program_id = ppe.pricing_program_id)
116. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027294.75..1027302.15 rows=1 width=20) (actual time=.. rows= loops=)

  • Join Filter: (sr3.schedule_id = s.id)
117. 0.000 0.000 ↓ 0.0

Sort (cost=1027294.75..1027294.76 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
118. 0.000 0.000 ↓ 0.0

Merge Join (cost=1019826.49..1027294.74 rows=1 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
119. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

120. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
121. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

122. 0.000 0.000 ↓ 0.0

Sort (cost=988147.81..988147.81 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
123. 0.000 0.000 ↓ 0.0

HashAggregate (cost=988147.78..988147.79 rows=1 width=53) (actual time=.. rows= loops=)

124. 0.000 0.000 ↓ 0.0

Merge Join (cost=983097.35..988143.54 rows=242 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 4)) IS NULL) OR (sr3.effective_date < ((SubPlan 4))))
125. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

126. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 4))
127. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
128. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

129. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

130. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

131.          

SubPlan (forHash Join)

132. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

133. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $2) AND (pricing_program_id = $3))
134. 0.000 0.000 ↓ 0.0

Sort (cost=35.53..35.53 rows=1 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
135. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=31.50..35.52 rows=1 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: ((account_id IS NULL) AND (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone))
136. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=31.50..31.50 rows=1 width=0) (actual time=.. rows= loops=)

137. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.89 rows=81 width=0) (actual time=.. rows= loops=)

  • Index Cond: (account_id IS NULL)
138. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_effective_date_index (cost=0.00..26.36 rows=1345 width=0) (actual time=.. rows= loops=)

  • Index Cond: (effective_date <= '2010-12-31 00:00:00'::timestamp without time zone)
139. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=8) (actual time=.. rows= loops=)

140. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_account_id_index on pricing_program_enrollment ppe (cost=0.00..6.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((ppe.account_id)::text = '30328775762641203278'::text)
141. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
142. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=532) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NULL) AND (p.consumption_percentage_from IS NULL))
143. 0.000 0.000 ↓ 0.0

Sort (cost=35974684.80..35974684.80 rows=1 width=1036) (actual time=.. rows= loops=)

  • Sort Key: rateforacc2_.schedule_id, rateforacc2_.rate_key
144. 0.000 0.000 ↓ 0.0

Subquery Scan rateforacc2_ (cost=35974684.77..35974684.79 rows=1 width=1036) (actual time=.. rows= loops=)

145. 0.000 0.000 ↓ 0.0

HashAggregate (cost=35974684.77..35974684.78 rows=1 width=49) (actual time=.. rows= loops=)

146. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=35974641.90..35974684.75 rows=1 width=49) (actual time=.. rows= loops=)

  • Join Filter: ((ppe.pricing_program_id = g.pricing_program_id) AND ((r.schedule_key)::text = (rk.rate_key)::text))
147. 0.000 0.000 ↓ 0.0

Nested Loop (cost=35974628.75..35974656.17 rows=1 width=48) (actual time=.. rows= loops=)

  • Join Filter: ((r2.schedule_key)::text = (r.schedule_key)::text)
148. 0.000 0.000 ↓ 0.0

Nested Loop (cost=35974628.75..35974647.87 rows=1 width=552) (actual time=.. rows= loops=)

  • Join Filter: (sr.schedule_id = s.id)
149. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..15.66 rows=7 width=28) (actual time=.. rows= loops=)

  • Join Filter: (s.pricing_program_id = ppe.pricing_program_id)
150. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_key on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
151. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=8) (actual time=.. rows= loops=)

152. 0.000 0.000 ↓ 0.0

Materialize (cost=35974628.75..35974628.97 rows=22 width=532) (actual time=.. rows= loops=)

153. 0.000 0.000 ↓ 0.0

Merge Join (cost=35974594.30..35974628.73 rows=22 width=532) (actual time=.. rows= loops=)

  • Merge Cond: ((sr.schedule_id = sr3.schedule_id) AND (sr.effective_date = (max(sr3.effective_date))))
154. 0.000 0.000 ↓ 0.0

Sort (cost=17983931.96..17983942.86 rows=4361 width=20) (actual time=.. rows= loops=)

  • Sort Key: sr.schedule_id, sr.effective_date
155. 0.000 0.000 ↓ 0.0

Subquery Scan sr (cost=17983613.81..17983668.32 rows=4361 width=20) (actual time=.. rows= loops=)

156. 0.000 0.000 ↓ 0.0

Sort (cost=17983613.81..17983624.71 rows=4361 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
157. 0.000 0.000 ↓ 0.0

Merge Join (cost=17955494.34..17982275.68 rows=4361 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
158. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

159. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
160. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

161. 0.000 0.000 ↓ 0.0

Materialize (cost=17923815.66..17926152.96 rows=186984 width=548) (actual time=.. rows= loops=)

162. 0.000 0.000 ↓ 0.0

Sort (cost=17923815.66..17924283.12 rows=186984 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
163. 0.000 0.000 ↓ 0.0

Unique (cost=16862493.60..17767518.98 rows=186984 width=53) (actual time=.. rows= loops=)

164. 0.000 0.000 ↓ 0.0

Sort (cost=16862493.60..16975621.77 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.id, sr3.version, sr3.account_id, sr3.schedule_id, sr3.effective_date, sr3.created_at, sr3.consumption_baseline
165. 0.000 0.000 ↓ 0.0

Merge Join (cost=1016021.51..3375093.06 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 1)) IS NULL) OR (sr3.effective_date < ((SubPlan 1))))
166. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

167. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 1))
168. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
169. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

170. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

171. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

172.          

SubPlan (forHash Join)

173. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

174. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $0) AND (pricing_program_id = $1))
175. 0.000 0.000 ↓ 0.0

Materialize (cost=32959.68..35296.98 rows=186984 width=53) (actual time=.. rows= loops=)

176. 0.000 0.000 ↓ 0.0

Sort (cost=32959.68..33427.14 rows=186984 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
177. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates sr3 (cost=0.00..3797.84 rows=186984 width=53) (actual time=.. rows= loops=)

178. 0.000 0.000 ↓ 0.0

Sort (cost=17990662.34..17990662.84 rows=200 width=528) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, (max(sr3.effective_date))
179. 0.000 0.000 ↓ 0.0

HashAggregate (cost=17990650.20..17990652.70 rows=200 width=14) (actual time=.. rows= loops=)

180. 0.000 0.000 ↓ 0.0

Merge Join (cost=17983613.81..17990617.49 rows=4361 width=14) (actual time=.. rows= loops=)

  • Merge Cond: (sr3.id = r2.schedule_rates_id)
181. 0.000 0.000 ↓ 0.0

Sort (cost=17983613.81..17983624.71 rows=4361 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
182. 0.000 0.000 ↓ 0.0

Merge Join (cost=17955494.34..17982275.68 rows=4361 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
183. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

184. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
185. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

186. 0.000 0.000 ↓ 0.0

Materialize (cost=17923815.66..17926152.96 rows=186984 width=548) (actual time=.. rows= loops=)

187. 0.000 0.000 ↓ 0.0

Sort (cost=17923815.66..17924283.12 rows=186984 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
188. 0.000 0.000 ↓ 0.0

Unique (cost=16862493.60..17767518.98 rows=186984 width=53) (actual time=.. rows= loops=)

189. 0.000 0.000 ↓ 0.0

Sort (cost=16862493.60..16975621.77 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.id, sr3.version, sr3.account_id, sr3.schedule_id, sr3.effective_date, sr3.created_at, sr3.consumption_baseline
190. 0.000 0.000 ↓ 0.0

Merge Join (cost=1016021.51..3375093.06 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 2)) IS NULL) OR (sr3.effective_date < ((SubPlan 2))))
191. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

192. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 2))
193. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
194. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

195. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

196. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

197.          

SubPlan (forHash Join)

198. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

199. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $2) AND (pricing_program_id = $3))
200. 0.000 0.000 ↓ 0.0

Materialize (cost=32959.68..35296.98 rows=186984 width=53) (actual time=.. rows= loops=)

201. 0.000 0.000 ↓ 0.0

Sort (cost=32959.68..33427.14 rows=186984 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
202. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates sr3 (cost=0.00..3797.84 rows=186984 width=53) (actual time=.. rows= loops=)

203. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r2 (cost=0.00..6427.20 rows=186984 width=6) (actual time=.. rows= loops=)

204. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=20) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr.id)
205. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.15..26.48 rows=140 width=521) (actual time=.. rows= loops=)

  • Hash Cond: (rk.rate_group_id = g.id)
206. 0.000 0.000 ↓ 0.0

Seq Scan on rate_group_ratekeys rk (cost=0.00..11.40 rows=140 width=520) (actual time=.. rows= loops=)

207. 0.000 0.000 ↓ 0.0

Hash (cost=11.40..11.40 rows=140 width=9) (actual time=.. rows= loops=)

208. 0.000 0.000 ↓ 0.0

Seq Scan on rate_group g (cost=0.00..11.40 rows=140 width=9) (actual time=.. rows= loops=)

209. 0.000 0.000 ↓ 0.0

HashAggregate (cost=9.50..9.51 rows=1 width=1573) (actual time=.. rows= loops=)

210. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..9.48 rows=1 width=1573) (actual time=.. rows= loops=)

211. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program pp (cost=0.00..1.20 rows=1 width=1553) (actual time=.. rows= loops=)

  • Filter: (active AND (id = 2))
212. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_account_id_index on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((ppe.account_id)::text = '30328775762641203278'::text)
  • Filter: (ppe.pricing_program_id = 2)
213. 0.000 0.000 ↓ 0.0

Hash Join (cost=40087635.85..40087682.27 rows=1 width=1700) (actual time=.. rows= loops=)

  • Hash Cond: (s.id = eprs.schedule_id)
214. 0.000 0.000 ↓ 0.0

HashAggregate (cost=61.06..107.38 rows=7 width=126) (actual time=.. rows= loops=)

215. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..60.92 rows=7 width=126) (actual time=.. rows= loops=)

216. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_account_id_index on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
  • Filter: (pricing_program_id = 2)
217. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.33 rows=7 width=106) (actual time=.. rows= loops=)

  • Filter: (s.pricing_program_id = 2)
218.          

SubPlan (forNested Loop)

219. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

220. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $9) AND (pricing_program_id = $10))
221. 0.000 0.000 ↓ 0.0

Hash (cost=40087574.77..40087574.77 rows=1 width=1700) (actual time=.. rows= loops=)

222. 0.000 0.000 ↓ 0.0

Merge Join (cost=40087574.72..40087574.77 rows=1 width=1700) (actual time=.. rows= loops=)

  • Merge Cond: ((eprs.schedule_id = rateforacc5_.schedule_id) AND ((eprs.rate_key)::text = (rateforacc5_.rate_key)::text))
223. 0.000 0.000 ↓ 0.0

Sort (cost=4112889.92..4112889.93 rows=4 width=1180) (actual time=.. rows= loops=)

  • Sort Key: eprs.schedule_id, eprs.rate_key
224. 0.000 0.000 ↓ 0.0

Subquery Scan eprs (cost=4112889.80..4112889.88 rows=4 width=1180) (actual time=.. rows= loops=)

225. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4112889.80..4112889.84 rows=4 width=554) (actual time=.. rows= loops=)

226. 0.000 0.000 ↓ 0.0

Append (cost=2056444.83..4112889.70 rows=4 width=554) (actual time=.. rows= loops=)

227. 0.000 0.000 ↓ 0.0

Result (cost=2056444.83..2056444.88 rows=2 width=290) (actual time=.. rows= loops=)

228. 0.000 0.000 ↓ 0.0

Unique (cost=2056444.83..2056444.88 rows=2 width=290) (actual time=.. rows= loops=)

229. 0.000 0.000 ↓ 0.0

Sort (cost=2056444.83..2056444.83 rows=2 width=290) (actual time=.. rows= loops=)

  • Sort Key: "*SELECT* 1".rid, "*SELECT* 1".schedule_id, "*SELECT* 1".rate_key, "*SELECT* 1".account_id, (NULL::text), "*SELECT* 1".effective_date, (NULL::bigint), (NULL::bigint), ('IBR'::text), "*SELECT* 1".group_order
230. 0.000 0.000 ↓ 0.0

Append (cost=1027543.77..2056444.82 rows=2 width=290) (actual time=.. rows= loops=)

231. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=1027543.77..1027543.79 rows=1 width=538) (actual time=.. rows= loops=)

232. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1027543.77..1027543.78 rows=1 width=538) (actual time=.. rows= loops=)

233. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027535.09..1027543.74 rows=1 width=538) (actual time=.. rows= loops=)

234. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027535.09..1027543.40 rows=1 width=538) (actual time=.. rows= loops=)

235. 0.000 0.000 ↓ 0.0

Sort (cost=1027535.09..1027535.09 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
236. 0.000 0.000 ↓ 0.0

Merge Join (cost=1020065.64..1027535.08 rows=1 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
237. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

238. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
239. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

240. 0.000 0.000 ↓ 0.0

Sort (cost=988386.96..988386.99 rows=14 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
241. 0.000 0.000 ↓ 0.0

HashAggregate (cost=988386.41..988386.55 rows=14 width=53) (actual time=.. rows= loops=)

242. 0.000 0.000 ↓ 0.0

Merge Join (cost=983119.19..988327.12 rows=3388 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 15)) IS NULL) OR (sr3.effective_date < ((SubPlan 15))))
243. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

244. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 15))
245. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
246. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

247. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

248. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

249.          

SubPlan (forHash Join)

250. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

251. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $0) AND (pricing_program_id = $1))
252. 0.000 0.000 ↓ 0.0

Sort (cost=57.36..57.40 rows=14 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
253. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=4.40..57.10 rows=14 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: ((account_id)::text = '30328775762641203278'::text)
  • Filter: ((effective_date < '2012-12-31 00:00:00'::timestamp without time zone) AND ((effective_date > '2010-12-31 00:00:00'::timestamp without time zone) OR (effective_date = $6)))
254. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.39 rows=14 width=0) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
255. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
256. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=4) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NOT NULL) OR (p.consumption_percentage_from IS NOT NULL))
257. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=1028901.01..1028901.03 rows=1 width=42) (actual time=.. rows= loops=)

258. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1028901.01..1028901.02 rows=1 width=42) (actual time=.. rows= loops=)

259. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1028892.00..1028900.99 rows=1 width=42) (actual time=.. rows= loops=)

260. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1028892.00..1028900.65 rows=1 width=42) (actual time=.. rows= loops=)

261. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1028892.00..1028892.34 rows=1 width=36) (actual time=.. rows= loops=)

  • Join Filter: (s.id = sr3.schedule_id)
262. 0.000 0.000 ↓ 0.0

Sort (cost=1028877.18..1028877.19 rows=2 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
263. 0.000 0.000 ↓ 0.0

Merge Join (cost=1021401.57..1028877.17 rows=2 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
264. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

265. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
266. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

267. 0.000 0.000 ↓ 0.0

Sort (cost=989722.90..989723.10 rows=80 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
268. 0.000 0.000 ↓ 0.0

HashAggregate (cost=989718.77..989719.57 rows=80 width=53) (actual time=.. rows= loops=)

269. 0.000 0.000 ↓ 0.0

Merge Join (cost=983341.80..989379.97 rows=19360 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 14)) IS NULL) OR (sr3.effective_date < ((SubPlan 14))))
270. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

271. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 14))
272. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
273. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

274. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

275. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

276.          

SubPlan (forHash Join)

277. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

278. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $0) AND (pricing_program_id = $1))
279. 0.000 0.000 ↓ 0.0

Sort (cost=279.97..280.17 rows=80 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
280. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=4.91..277.44 rows=80 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: (account_id IS NULL)
  • Filter: ((effective_date < '2012-12-31 00:00:00'::timestamp without time zone) AND ((effective_date > '2010-12-31 00:00:00'::timestamp without time zone) OR (effective_date = $6)))
281. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.89 rows=81 width=0) (actual time=.. rows= loops=)

  • Index Cond: (account_id IS NULL)
282. 0.000 0.000 ↓ 0.0

Materialize (cost=14.82..14.89 rows=7 width=24) (actual time=.. rows= loops=)

283. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.29..14.81 rows=7 width=24) (actual time=.. rows= loops=)

  • Hash Cond: (s.pricing_program_id = ppe.pricing_program_id)
284. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=8) (actual time=.. rows= loops=)

285. 0.000 0.000 ↓ 0.0

Hash (cost=8.27..8.27 rows=1 width=24) (actual time=.. rows= loops=)

286. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_key on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
287. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
288. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=4) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NOT NULL) OR (p.consumption_percentage_from IS NOT NULL))
289. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 3" (cost=1027543.77..1027543.79 rows=1 width=1066) (actual time=.. rows= loops=)

290. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1027543.77..1027543.78 rows=1 width=1066) (actual time=.. rows= loops=)

291. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027535.09..1027543.74 rows=1 width=1066) (actual time=.. rows= loops=)

292. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1027535.09..1027543.40 rows=1 width=538) (actual time=.. rows= loops=)

293. 0.000 0.000 ↓ 0.0

Sort (cost=1027535.09..1027535.09 rows=1 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
294. 0.000 0.000 ↓ 0.0

Merge Join (cost=1020065.64..1027535.08 rows=1 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
295. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

296. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
297. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

298. 0.000 0.000 ↓ 0.0

Sort (cost=988386.96..988386.99 rows=14 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
299. 0.000 0.000 ↓ 0.0

HashAggregate (cost=988386.41..988386.55 rows=14 width=53) (actual time=.. rows= loops=)

300. 0.000 0.000 ↓ 0.0

Merge Join (cost=983119.19..988327.12 rows=3388 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 13)) IS NULL) OR (sr3.effective_date < ((SubPlan 13))))
301. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

302. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 13))
303. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
304. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

305. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

306. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

307.          

SubPlan (forHash Join)

308. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

309. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $0) AND (pricing_program_id = $1))
310. 0.000 0.000 ↓ 0.0

Sort (cost=57.36..57.40 rows=14 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
311. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=4.40..57.10 rows=14 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: ((account_id)::text = '30328775762641203278'::text)
  • Filter: ((effective_date < '2012-12-31 00:00:00'::timestamp without time zone) AND ((effective_date > '2010-12-31 00:00:00'::timestamp without time zone) OR (effective_date = $6)))
312. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.39 rows=14 width=0) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
313. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
314. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=532) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NULL) AND (p.consumption_percentage_from IS NULL))
315. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 4" (cost=1028901.01..1028901.03 rows=1 width=570) (actual time=.. rows= loops=)

316. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1028901.01..1028901.02 rows=1 width=570) (actual time=.. rows= loops=)

317. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1028892.00..1028900.99 rows=1 width=570) (actual time=.. rows= loops=)

318. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1028892.00..1028900.65 rows=1 width=42) (actual time=.. rows= loops=)

319. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1028892.00..1028892.34 rows=1 width=36) (actual time=.. rows= loops=)

  • Join Filter: (s.id = sr3.schedule_id)
320. 0.000 0.000 ↓ 0.0

Sort (cost=1028877.18..1028877.19 rows=2 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
321. 0.000 0.000 ↓ 0.0

Merge Join (cost=1021401.57..1028877.17 rows=2 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
322. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

323. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
324. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

325. 0.000 0.000 ↓ 0.0

Sort (cost=989722.90..989723.10 rows=80 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
326. 0.000 0.000 ↓ 0.0

HashAggregate (cost=989718.77..989719.57 rows=80 width=53) (actual time=.. rows= loops=)

327. 0.000 0.000 ↓ 0.0

Merge Join (cost=983341.80..989379.97 rows=19360 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 12)) IS NULL) OR (sr3.effective_date < ((SubPlan 12))))
328. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

329. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 12))
330. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
331. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

332. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

333. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

334.          

SubPlan (forHash Join)

335. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

336. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $0) AND (pricing_program_id = $1))
337. 0.000 0.000 ↓ 0.0

Sort (cost=279.97..280.17 rows=80 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
338. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on schedule_rates sr3 (cost=4.91..277.44 rows=80 width=53) (actual time=.. rows= loops=)

  • Recheck Cond: (account_id IS NULL)
  • Filter: ((effective_date < '2012-12-31 00:00:00'::timestamp without time zone) AND ((effective_date > '2010-12-31 00:00:00'::timestamp without time zone) OR (effective_date = $6)))
339. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on schedule_rates_account_id_index (cost=0.00..4.89 rows=81 width=0) (actual time=.. rows= loops=)

  • Index Cond: (account_id IS NULL)
340. 0.000 0.000 ↓ 0.0

Materialize (cost=14.82..14.89 rows=7 width=24) (actual time=.. rows= loops=)

341. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.29..14.81 rows=7 width=24) (actual time=.. rows= loops=)

  • Hash Cond: (s.pricing_program_id = ppe.pricing_program_id)
342. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=8) (actual time=.. rows= loops=)

343. 0.000 0.000 ↓ 0.0

Hash (cost=8.27..8.27 rows=1 width=24) (actual time=.. rows= loops=)

344. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_key on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
345. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=10) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr3.id)
346. 0.000 0.000 ↓ 0.0

Index Scan using price_tier_rate_id_index on price_tier p (cost=0.00..0.33 rows=1 width=532) (actual time=.. rows= loops=)

  • Index Cond: (p.rate_id = r.id)
  • Filter: ((p.consumption_from IS NULL) AND (p.consumption_percentage_from IS NULL))
347. 0.000 0.000 ↓ 0.0

Sort (cost=35974684.80..35974684.80 rows=1 width=1036) (actual time=.. rows= loops=)

  • Sort Key: rateforacc5_.schedule_id, rateforacc5_.rate_key
348. 0.000 0.000 ↓ 0.0

Subquery Scan rateforacc5_ (cost=35974684.77..35974684.79 rows=1 width=1036) (actual time=.. rows= loops=)

349. 0.000 0.000 ↓ 0.0

HashAggregate (cost=35974684.77..35974684.78 rows=1 width=49) (actual time=.. rows= loops=)

350. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=35974641.90..35974684.75 rows=1 width=49) (actual time=.. rows= loops=)

  • Join Filter: ((ppe.pricing_program_id = g.pricing_program_id) AND ((r.schedule_key)::text = (rk.rate_key)::text))
351. 0.000 0.000 ↓ 0.0

Nested Loop (cost=35974628.75..35974656.17 rows=1 width=48) (actual time=.. rows= loops=)

  • Join Filter: ((r2.schedule_key)::text = (r.schedule_key)::text)
352. 0.000 0.000 ↓ 0.0

Nested Loop (cost=35974628.75..35974647.87 rows=1 width=552) (actual time=.. rows= loops=)

  • Join Filter: (sr.schedule_id = s.id)
353. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..15.66 rows=7 width=28) (actual time=.. rows= loops=)

  • Join Filter: (s.pricing_program_id = ppe.pricing_program_id)
354. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_key on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((account_id)::text = '30328775762641203278'::text)
355. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=8) (actual time=.. rows= loops=)

356. 0.000 0.000 ↓ 0.0

Materialize (cost=35974628.75..35974628.97 rows=22 width=532) (actual time=.. rows= loops=)

357. 0.000 0.000 ↓ 0.0

Merge Join (cost=35974594.30..35974628.73 rows=22 width=532) (actual time=.. rows= loops=)

  • Merge Cond: ((sr.schedule_id = sr3.schedule_id) AND (sr.effective_date = (max(sr3.effective_date))))
358. 0.000 0.000 ↓ 0.0

Sort (cost=17983931.96..17983942.86 rows=4361 width=20) (actual time=.. rows= loops=)

  • Sort Key: sr.schedule_id, sr.effective_date
359. 0.000 0.000 ↓ 0.0

Subquery Scan sr (cost=17983613.81..17983668.32 rows=4361 width=20) (actual time=.. rows= loops=)

360. 0.000 0.000 ↓ 0.0

Sort (cost=17983613.81..17983624.71 rows=4361 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
361. 0.000 0.000 ↓ 0.0

Merge Join (cost=17955494.34..17982275.68 rows=4361 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
362. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

363. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
364. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

365. 0.000 0.000 ↓ 0.0

Materialize (cost=17923815.66..17926152.96 rows=186984 width=548) (actual time=.. rows= loops=)

366. 0.000 0.000 ↓ 0.0

Sort (cost=17923815.66..17924283.12 rows=186984 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
367. 0.000 0.000 ↓ 0.0

Unique (cost=16862493.60..17767518.98 rows=186984 width=53) (actual time=.. rows= loops=)

368. 0.000 0.000 ↓ 0.0

Sort (cost=16862493.60..16975621.77 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.id, sr3.version, sr3.account_id, sr3.schedule_id, sr3.effective_date, sr3.created_at, sr3.consumption_baseline
369. 0.000 0.000 ↓ 0.0

Merge Join (cost=1016021.51..3375093.06 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 9)) IS NULL) OR (sr3.effective_date < ((SubPlan 9))))
370. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

371. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 9))
372. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
373. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

374. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

375. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

376.          

SubPlan (forHash Join)

377. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

378. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $7) AND (pricing_program_id = $8))
379. 0.000 0.000 ↓ 0.0

Materialize (cost=32959.68..35296.98 rows=186984 width=53) (actual time=.. rows= loops=)

380. 0.000 0.000 ↓ 0.0

Sort (cost=32959.68..33427.14 rows=186984 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
381. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates sr3 (cost=0.00..3797.84 rows=186984 width=53) (actual time=.. rows= loops=)

382. 0.000 0.000 ↓ 0.0

Sort (cost=17990662.34..17990662.84 rows=200 width=528) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, (max(sr3.effective_date))
383. 0.000 0.000 ↓ 0.0

HashAggregate (cost=17990650.20..17990652.70 rows=200 width=14) (actual time=.. rows= loops=)

384. 0.000 0.000 ↓ 0.0

Merge Join (cost=17983613.81..17990617.49 rows=4361 width=14) (actual time=.. rows= loops=)

  • Merge Cond: (sr3.id = r2.schedule_rates_id)
385. 0.000 0.000 ↓ 0.0

Sort (cost=17983613.81..17983624.71 rows=4361 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.id
386. 0.000 0.000 ↓ 0.0

Merge Join (cost=17955494.34..17982275.68 rows=4361 width=548) (actual time=.. rows= loops=)

  • Merge Cond: ((public.schedule_rates.schedule_id = sr3.schedule_id) AND (public.schedule_rates.effective_date = sr3.effective_date))
  • Join Filter: (sr3.created_at = (max(public.schedule_rates.created_at)))
387. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=31678.68..36348.18 rows=186576 width=41) (actual time=.. rows= loops=)

388. 0.000 0.000 ↓ 0.0

Sort (cost=31678.68..32146.14 rows=186984 width=41) (actual time=.. rows= loops=)

  • Sort Key: public.schedule_rates.schedule_id, public.schedule_rates.effective_date, public.schedule_rates.account_id
389. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates (cost=0.00..3797.84 rows=186984 width=41) (actual time=.. rows= loops=)

390. 0.000 0.000 ↓ 0.0

Materialize (cost=17923815.66..17926152.96 rows=186984 width=548) (actual time=.. rows= loops=)

391. 0.000 0.000 ↓ 0.0

Sort (cost=17923815.66..17924283.12 rows=186984 width=548) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id, sr3.effective_date
392. 0.000 0.000 ↓ 0.0

Unique (cost=16862493.60..17767518.98 rows=186984 width=53) (actual time=.. rows= loops=)

393. 0.000 0.000 ↓ 0.0

Sort (cost=16862493.60..16975621.77 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.id, sr3.version, sr3.account_id, sr3.schedule_id, sr3.effective_date, sr3.created_at, sr3.consumption_baseline
394. 0.000 0.000 ↓ 0.0

Merge Join (cost=1016021.51..3375093.06 rows=45251269 width=53) (actual time=.. rows= loops=)

  • Merge Cond: (s.id = sr3.schedule_id)
  • Join Filter: ((((SubPlan 10)) IS NULL) OR (sr3.effective_date < ((SubPlan 10))))
395. 0.000 0.000 ↓ 0.0

Unique (cost=983061.83..986296.56 rows=143766 width=126) (actual time=.. rows= loops=)

396. 0.000 0.000 ↓ 0.0

Sort (cost=983061.83..983421.24 rows=143766 width=126) (actual time=.. rows= loops=)

  • Sort Key: s.id, ppe.account_id, s.name, s.pricing_program_id, s.effective_date, s.switch_point_content, s.switch_point_resource, ((SubPlan 10))
397. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.39..952069.85 rows=143766 width=126) (actual time=.. rows= loops=)

  • Hash Cond: (ppe.pricing_program_id = s.pricing_program_id)
398. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program_enrollment ppe (cost=0.00..459.63 rows=20563 width=24) (actual time=.. rows= loops=)

399. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=106 width=106) (actual time=.. rows= loops=)

400. 0.000 0.000 ↓ 0.0

Seq Scan on schedule s (cost=0.00..6.06 rows=106 width=106) (actual time=.. rows= loops=)

401.          

SubPlan (forHash Join)

402. 0.000 0.000 ↓ 0.0

Aggregate (cost=6.60..6.61 rows=1 width=8) (actual time=.. rows= loops=)

403. 0.000 0.000 ↓ 0.0

Seq Scan on schedule (cost=0.00..6.59 rows=2 width=8) (actual time=.. rows= loops=)

  • Filter: ((effective_date > $0) AND (pricing_program_id = $1))
404. 0.000 0.000 ↓ 0.0

Materialize (cost=32959.68..35296.98 rows=186984 width=53) (actual time=.. rows= loops=)

405. 0.000 0.000 ↓ 0.0

Sort (cost=32959.68..33427.14 rows=186984 width=53) (actual time=.. rows= loops=)

  • Sort Key: sr3.schedule_id
406. 0.000 0.000 ↓ 0.0

Seq Scan on schedule_rates sr3 (cost=0.00..3797.84 rows=186984 width=53) (actual time=.. rows= loops=)

407. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r2 (cost=0.00..6427.20 rows=186984 width=6) (actual time=.. rows= loops=)

408. 0.000 0.000 ↓ 0.0

Index Scan using rate_schedule_rates_id_index on rate r (cost=0.00..8.29 rows=1 width=20) (actual time=.. rows= loops=)

  • Index Cond: (r.schedule_rates_id = sr.id)
409. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.15..26.48 rows=140 width=521) (actual time=.. rows= loops=)

  • Hash Cond: (rk.rate_group_id = g.id)
410. 0.000 0.000 ↓ 0.0

Seq Scan on rate_group_ratekeys rk (cost=0.00..11.40 rows=140 width=520) (actual time=.. rows= loops=)

411. 0.000 0.000 ↓ 0.0

Hash (cost=11.40..11.40 rows=140 width=9) (actual time=.. rows= loops=)

412. 0.000 0.000 ↓ 0.0

Seq Scan on rate_group g (cost=0.00..11.40 rows=140 width=9) (actual time=.. rows= loops=)

413. 0.000 0.000 ↓ 0.0

HashAggregate (cost=9.50..9.51 rows=1 width=1573) (actual time=.. rows= loops=)

414. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..9.48 rows=1 width=1573) (actual time=.. rows= loops=)

415. 0.000 0.000 ↓ 0.0

Seq Scan on pricing_program pp (cost=0.00..1.20 rows=1 width=1553) (actual time=.. rows= loops=)

  • Filter: (active AND (id = 2))
416. 0.000 0.000 ↓ 0.0

Index Scan using pricing_program_enrollment_account_id_index on pricing_program_enrollment ppe (cost=0.00..8.27 rows=1 width=24) (actual time=.. rows= loops=)

  • Index Cond: ((ppe.account_id)::text = '30328775762641203278'::text)
  • Filter: (ppe.pricing_program_id = 2)