explain.depesz.com

A tool for finding a real cause for slow queries.

Result: GfB

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=80,171,687.68..80,171,734.13 rows=1 width=1,180) (actual time=.. rows= loops=)

2.          

Initplan (forNested Loop)

3. 0.000 0.000 ↓ 0.0

Aggregate (cost=40,084,042.32..40,084,042.33 rows=1 width=8) (actual time=.. rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=40,083,995.86..40,084,042.32 rows=1 width=8) (actual time=.. rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Join (cost=40,083,986.36..40,084,032.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=40,083,925.28..40,083,925.28 rows=1 width=532) (actual time=.. rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Join (cost=40,083,925.23..40,083,925.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=4,109,240.44..4,109,240.45 rows=4 width=1,044) (actual time=.. rows= loops=)

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

Subquery Scan eprs (cost=4,109,240.32..4,109,240.40 rows=4 width=1,044) (actual time=.. rows= loops=)

17. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,109,240.32..4,109,240.36 rows=4 width=554) (actual time=.. rows= loops=)

18. 0.000 0.000 ↓ 0.0

Append (cost=2,054,620.09..4,109,240.22 rows=4 width=554) (actual time=.. rows= loops=)

19. 0.000 0.000 ↓ 0.0

Result (cost=2,054,620.09..2,054,620.14 rows=2 width=290) (actual time=.. rows= loops=)

20. 0.000 0.000 ↓ 0.0

Unique (cost=2,054,620.09..2,054,620.14 rows=2 width=290) (actual time=.. rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=2,054,620.09..2,054,620.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=1,027,302.93..2,054,620.08 rows=2 width=290) (actual time=.. rows= loops=)

23. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=1,027,302.93..1,027,302.95 rows=1 width=538) (actual time=.. rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,027,302.93..1,027,302.94 rows=1 width=538) (actual time=.. rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.25..1,027,302.91 rows=1 width=538) (actual time=.. rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.25..1,027,302.57 rows=1 width=538) (actual time=.. rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=1,027,294.25..1,027,294.26 rows=1 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,019,825.99..1,027,294.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=988,147.31..988,147.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=988,147.28..988,147.29 rows=1 width=53) (actual time=.. rows= loops=)

34. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,096.85..988,143.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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,345 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=1,027,317.10..1,027,317.12 rows=1 width=42) (actual time=.. rows= loops=)

52. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,027,317.10..1,027,317.11 rows=1 width=42) (actual time=.. rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.75..1,027,317.08 rows=1 width=42) (actual time=.. rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.75..1,027,316.74 rows=1 width=42) (actual time=.. rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.75..1,027,308.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=1,027,294.75..1,027,302.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=1,027,294.75..1,027,294.76 rows=1 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,019,826.49..1,027,294.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

60. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

62. 0.000 0.000 ↓ 0.0

Sort (cost=988,147.81..988,147.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=988,147.78..988,147.79 rows=1 width=53) (actual time=.. rows= loops=)

64. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,097.35..988,143.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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,345 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=1,027,302.93..1,027,302.95 rows=1 width=1,066) (actual time=.. rows= loops=)

84. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,027,302.93..1,027,302.94 rows=1 width=1,066) (actual time=.. rows= loops=)

85. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.25..1,027,302.91 rows=1 width=1,066) (actual time=.. rows= loops=)

86. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.25..1,027,302.57 rows=1 width=538) (actual time=.. rows= loops=)

87. 0.000 0.000 ↓ 0.0

Sort (cost=1,027,294.25..1,027,294.26 rows=1 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,019,825.99..1,027,294.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

90. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

92. 0.000 0.000 ↓ 0.0

Sort (cost=988,147.31..988,147.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=988,147.28..988,147.29 rows=1 width=53) (actual time=.. rows= loops=)

94. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,096.85..988,143.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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

96. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,345 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=1,027,317.10..1,027,317.12 rows=1 width=570) (actual time=.. rows= loops=)

112. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,027,317.10..1,027,317.11 rows=1 width=570) (actual time=.. rows= loops=)

113. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.75..1,027,317.08 rows=1 width=570) (actual time=.. rows= loops=)

114. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.75..1,027,316.74 rows=1 width=42) (actual time=.. rows= loops=)

115. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,294.75..1,027,308.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=1,027,294.75..1,027,302.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=1,027,294.75..1,027,294.76 rows=1 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,019,826.49..1,027,294.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

120. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

122. 0.000 0.000 ↓ 0.0

Sort (cost=988,147.81..988,147.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=988,147.78..988,147.79 rows=1 width=53) (actual time=.. rows= loops=)

124. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,097.35..988,143.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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

126. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,345 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=35,974,684.80..35,974,684.80 rows=1 width=1,036) (actual time=.. rows= loops=)

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

Subquery Scan rateforacc2_ (cost=35,974,684.77..35,974,684.79 rows=1 width=1,036) (actual time=.. rows= loops=)

145. 0.000 0.000 ↓ 0.0

HashAggregate (cost=35,974,684.77..35,974,684.78 rows=1 width=49) (actual time=.. rows= loops=)

146. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=35,974,641.90..35,974,684.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=35,974,628.75..35,974,656.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=35,974,628.75..35,974,647.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=35,974,628.75..35,974,628.97 rows=22 width=532) (actual time=.. rows= loops=)

153. 0.000 0.000 ↓ 0.0

Merge Join (cost=35,974,594.30..35,974,628.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=17,983,931.96..17,983,942.86 rows=4,361 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=17,983,613.81..17,983,668.32 rows=4,361 width=20) (actual time=.. rows= loops=)

156. 0.000 0.000 ↓ 0.0

Sort (cost=17,983,613.81..17,983,624.71 rows=4,361 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=17,955,494.34..17,982,275.68 rows=4,361 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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

159. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

161. 0.000 0.000 ↓ 0.0

Materialize (cost=17,923,815.66..17,926,152.96 rows=186,984 width=548) (actual time=.. rows= loops=)

162. 0.000 0.000 ↓ 0.0

Sort (cost=17,923,815.66..17,924,283.12 rows=186,984 width=548) (actual time=.. rows= loops=)

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

Unique (cost=16,862,493.60..17,767,518.98 rows=186,984 width=53) (actual time=.. rows= loops=)

164. 0.000 0.000 ↓ 0.0

Sort (cost=16,862,493.60..16,975,621.77 rows=45,251,269 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=1,016,021.51..3,375,093.06 rows=45,251,269 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

167. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=32,959.68..35,296.98 rows=186,984 width=53) (actual time=.. rows= loops=)

176. 0.000 0.000 ↓ 0.0

Sort (cost=32,959.68..33,427.14 rows=186,984 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..3,797.84 rows=186,984 width=53) (actual time=.. rows= loops=)

178. 0.000 0.000 ↓ 0.0

Sort (cost=17,990,662.34..17,990,662.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=17,990,650.20..17,990,652.70 rows=200 width=14) (actual time=.. rows= loops=)

180. 0.000 0.000 ↓ 0.0

Merge Join (cost=17,983,613.81..17,990,617.49 rows=4,361 width=14) (actual time=.. rows= loops=)

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

Sort (cost=17,983,613.81..17,983,624.71 rows=4,361 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=17,955,494.34..17,982,275.68 rows=4,361 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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

184. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

186. 0.000 0.000 ↓ 0.0

Materialize (cost=17,923,815.66..17,926,152.96 rows=186,984 width=548) (actual time=.. rows= loops=)

187. 0.000 0.000 ↓ 0.0

Sort (cost=17,923,815.66..17,924,283.12 rows=186,984 width=548) (actual time=.. rows= loops=)

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

Unique (cost=16,862,493.60..17,767,518.98 rows=186,984 width=53) (actual time=.. rows= loops=)

189. 0.000 0.000 ↓ 0.0

Sort (cost=16,862,493.60..16,975,621.77 rows=45,251,269 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=1,016,021.51..3,375,093.06 rows=45,251,269 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

192. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=32,959.68..35,296.98 rows=186,984 width=53) (actual time=.. rows= loops=)

201. 0.000 0.000 ↓ 0.0

Sort (cost=32,959.68..33,427.14 rows=186,984 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..3,797.84 rows=186,984 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..6,427.20 rows=186,984 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=1,573) (actual time=.. rows= loops=)

210. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..9.48 rows=1 width=1,573) (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=1,553) (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=40,087,635.85..40,087,682.27 rows=1 width=1,700) (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=40,087,574.77..40,087,574.77 rows=1 width=1,700) (actual time=.. rows= loops=)

222. 0.000 0.000 ↓ 0.0

Merge Join (cost=40,087,574.72..40,087,574.77 rows=1 width=1,700) (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=4,112,889.92..4,112,889.93 rows=4 width=1,180) (actual time=.. rows= loops=)

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

Subquery Scan eprs (cost=4,112,889.80..4,112,889.88 rows=4 width=1,180) (actual time=.. rows= loops=)

225. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,112,889.80..4,112,889.84 rows=4 width=554) (actual time=.. rows= loops=)

226. 0.000 0.000 ↓ 0.0

Append (cost=2,056,444.83..4,112,889.70 rows=4 width=554) (actual time=.. rows= loops=)

227. 0.000 0.000 ↓ 0.0

Result (cost=2,056,444.83..2,056,444.88 rows=2 width=290) (actual time=.. rows= loops=)

228. 0.000 0.000 ↓ 0.0

Unique (cost=2,056,444.83..2,056,444.88 rows=2 width=290) (actual time=.. rows= loops=)

229. 0.000 0.000 ↓ 0.0

Sort (cost=2,056,444.83..2,056,444.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=1,027,543.77..2,056,444.82 rows=2 width=290) (actual time=.. rows= loops=)

231. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=1,027,543.77..1,027,543.79 rows=1 width=538) (actual time=.. rows= loops=)

232. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,027,543.77..1,027,543.78 rows=1 width=538) (actual time=.. rows= loops=)

233. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,535.09..1,027,543.74 rows=1 width=538) (actual time=.. rows= loops=)

234. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,535.09..1,027,543.40 rows=1 width=538) (actual time=.. rows= loops=)

235. 0.000 0.000 ↓ 0.0

Sort (cost=1,027,535.09..1,027,535.09 rows=1 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,020,065.64..1,027,535.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

238. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

240. 0.000 0.000 ↓ 0.0

Sort (cost=988,386.96..988,386.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=988,386.41..988,386.55 rows=14 width=53) (actual time=.. rows= loops=)

242. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,119.19..988,327.12 rows=3,388 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

244. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,028,901.01..1,028,901.03 rows=1 width=42) (actual time=.. rows= loops=)

258. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,028,901.01..1,028,901.02 rows=1 width=42) (actual time=.. rows= loops=)

259. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,028,892.00..1,028,900.99 rows=1 width=42) (actual time=.. rows= loops=)

260. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,028,892.00..1,028,900.65 rows=1 width=42) (actual time=.. rows= loops=)

261. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,028,892.00..1,028,892.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=1,028,877.18..1,028,877.19 rows=2 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,021,401.57..1,028,877.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

265. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

267. 0.000 0.000 ↓ 0.0

Sort (cost=989,722.90..989,723.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=989,718.77..989,719.57 rows=80 width=53) (actual time=.. rows= loops=)

269. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,341.80..989,379.97 rows=19,360 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

271. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,027,543.77..1,027,543.79 rows=1 width=1,066) (actual time=.. rows= loops=)

290. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,027,543.77..1,027,543.78 rows=1 width=1,066) (actual time=.. rows= loops=)

291. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,535.09..1,027,543.74 rows=1 width=1,066) (actual time=.. rows= loops=)

292. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,027,535.09..1,027,543.40 rows=1 width=538) (actual time=.. rows= loops=)

293. 0.000 0.000 ↓ 0.0

Sort (cost=1,027,535.09..1,027,535.09 rows=1 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,020,065.64..1,027,535.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

296. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

298. 0.000 0.000 ↓ 0.0

Sort (cost=988,386.96..988,386.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=988,386.41..988,386.55 rows=14 width=53) (actual time=.. rows= loops=)

300. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,119.19..988,327.12 rows=3,388 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

302. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=1,028,901.01..1,028,901.03 rows=1 width=570) (actual time=.. rows= loops=)

316. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,028,901.01..1,028,901.02 rows=1 width=570) (actual time=.. rows= loops=)

317. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,028,892.00..1,028,900.99 rows=1 width=570) (actual time=.. rows= loops=)

318. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,028,892.00..1,028,900.65 rows=1 width=42) (actual time=.. rows= loops=)

319. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,028,892.00..1,028,892.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=1,028,877.18..1,028,877.19 rows=2 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=1,021,401.57..1,028,877.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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

323. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

325. 0.000 0.000 ↓ 0.0

Sort (cost=989,722.90..989,723.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=989,718.77..989,719.57 rows=80 width=53) (actual time=.. rows= loops=)

327. 0.000 0.000 ↓ 0.0

Merge Join (cost=983,341.80..989,379.97 rows=19,360 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

329. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=35,974,684.80..35,974,684.80 rows=1 width=1,036) (actual time=.. rows= loops=)

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

Subquery Scan rateforacc5_ (cost=35,974,684.77..35,974,684.79 rows=1 width=1,036) (actual time=.. rows= loops=)

349. 0.000 0.000 ↓ 0.0

HashAggregate (cost=35,974,684.77..35,974,684.78 rows=1 width=49) (actual time=.. rows= loops=)

350. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=35,974,641.90..35,974,684.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=35,974,628.75..35,974,656.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=35,974,628.75..35,974,647.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=35,974,628.75..35,974,628.97 rows=22 width=532) (actual time=.. rows= loops=)

357. 0.000 0.000 ↓ 0.0

Merge Join (cost=35,974,594.30..35,974,628.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=17,983,931.96..17,983,942.86 rows=4,361 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=17,983,613.81..17,983,668.32 rows=4,361 width=20) (actual time=.. rows= loops=)

360. 0.000 0.000 ↓ 0.0

Sort (cost=17,983,613.81..17,983,624.71 rows=4,361 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=17,955,494.34..17,982,275.68 rows=4,361 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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

363. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

365. 0.000 0.000 ↓ 0.0

Materialize (cost=17,923,815.66..17,926,152.96 rows=186,984 width=548) (actual time=.. rows= loops=)

366. 0.000 0.000 ↓ 0.0

Sort (cost=17,923,815.66..17,924,283.12 rows=186,984 width=548) (actual time=.. rows= loops=)

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

Unique (cost=16,862,493.60..17,767,518.98 rows=186,984 width=53) (actual time=.. rows= loops=)

368. 0.000 0.000 ↓ 0.0

Sort (cost=16,862,493.60..16,975,621.77 rows=45,251,269 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=1,016,021.51..3,375,093.06 rows=45,251,269 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

371. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=32,959.68..35,296.98 rows=186,984 width=53) (actual time=.. rows= loops=)

380. 0.000 0.000 ↓ 0.0

Sort (cost=32,959.68..33,427.14 rows=186,984 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..3,797.84 rows=186,984 width=53) (actual time=.. rows= loops=)

382. 0.000 0.000 ↓ 0.0

Sort (cost=17,990,662.34..17,990,662.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=17,990,650.20..17,990,652.70 rows=200 width=14) (actual time=.. rows= loops=)

384. 0.000 0.000 ↓ 0.0

Merge Join (cost=17,983,613.81..17,990,617.49 rows=4,361 width=14) (actual time=.. rows= loops=)

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

Sort (cost=17,983,613.81..17,983,624.71 rows=4,361 width=548) (actual time=.. rows= loops=)

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

Merge Join (cost=17,955,494.34..17,982,275.68 rows=4,361 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=31,678.68..36,348.18 rows=186,576 width=41) (actual time=.. rows= loops=)

388. 0.000 0.000 ↓ 0.0

Sort (cost=31,678.68..32,146.14 rows=186,984 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..3,797.84 rows=186,984 width=41) (actual time=.. rows= loops=)

390. 0.000 0.000 ↓ 0.0

Materialize (cost=17,923,815.66..17,926,152.96 rows=186,984 width=548) (actual time=.. rows= loops=)

391. 0.000 0.000 ↓ 0.0

Sort (cost=17,923,815.66..17,924,283.12 rows=186,984 width=548) (actual time=.. rows= loops=)

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

Unique (cost=16,862,493.60..17,767,518.98 rows=186,984 width=53) (actual time=.. rows= loops=)

393. 0.000 0.000 ↓ 0.0

Sort (cost=16,862,493.60..16,975,621.77 rows=45,251,269 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=1,016,021.51..3,375,093.06 rows=45,251,269 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=983,061.83..986,296.56 rows=143,766 width=126) (actual time=.. rows= loops=)

396. 0.000 0.000 ↓ 0.0

Sort (cost=983,061.83..983,421.24 rows=143,766 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..952,069.85 rows=143,766 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=20,563 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=32,959.68..35,296.98 rows=186,984 width=53) (actual time=.. rows= loops=)

405. 0.000 0.000 ↓ 0.0

Sort (cost=32,959.68..33,427.14 rows=186,984 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..3,797.84 rows=186,984 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..6,427.20 rows=186,984 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=1,573) (actual time=.. rows= loops=)

414. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..9.48 rows=1 width=1,573) (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=1,553) (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)