explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T3r5

Settings
# exclusive inclusive rows x rows loops node
1. 9,984.901 9,984.901 ↑ 111,966,612,151,966,288.0 11,861 1

CTE Scan on bb_data (cost=1,090,651,189,076,688,764,928.00..1,117,211,908,811,378,262,016.00 rows=1,328,035,986,734,472,232,960 width=5,003) (actual time=8,979.211..9,984.901 rows=11,861 loops=1)

2.          

CTE constant

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

4.          

CTE loan

5. 18.912 369.932 ↑ 1.9 11,861 1

HashAggregate (cost=101,161.09..101,380.58 rows=21,949 width=78) (actual time=363.434..369.932 rows=11,861 loops=1)

  • Group Key: lo.id, lo.asset_id, lo.external_id, lo.origination_date, lo.loan_type, lo.term_in_months, lo.stated_interest_rate, lo.monthly_payment, lo.charge_off_date, lo.charge_off_principal, lo.charge_off_origination_fee, lo.lending_license_state
6.          

Initplan (for HashAggregate)

7. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

8. 15.098 351.018 ↑ 1.9 11,861 1

Nested Loop (cost=1,630.08..100,502.60 rows=21,949 width=78) (actual time=57.778..351.018 rows=11,861 loops=1)

9. 6.214 300.337 ↑ 5.6 11,861 1

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (actual time=57.739..300.337 rows=11,861 loops=1)

10. 0.002 0.003 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Group Key: constant_1.owner_entity_id
11. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

12. 246.129 294.120 ↑ 5.6 11,861 1

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,629.63..66,039.29 rows=66,962 width=8) (actual time=57.733..294.120 rows=11,861 loops=1)

  • Recheck Cond: (to_entity_id = constant_1.owner_entity_id)
  • Filter: ((ownership_end_date IS NULL) OR (to_entity_id <> 504))
  • Rows Removed by Filter: 527,077
  • Heap Blocks: exact=50,222
13. 47.991 47.991 ↓ 7.9 627,264 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,612.89 rows=79,528 width=0) (actual time=47.991..47.991 rows=627,264 loops=1)

  • Index Cond: (to_entity_id = constant_1.owner_entity_id)
14. 35.583 35.583 ↑ 1.0 1 11,861

Index Scan using unc_asset_loan_asset_id on loan lo (cost=0.42..0.49 rows=1 width=78) (actual time=0.003..0.003 rows=1 loops=11,861)

  • Index Cond: (asset_id = aot.asset_id)
  • Filter: ((origination_date <= $1) AND (originator_id = 1,003))
15.          

CTE aot

16. 37.480 182.606 ↑ 120.7 11,861 1

Nested Loop (cost=494.28..24,588,172.40 rows=1,431,496 width=200) (actual time=8.401..182.606 rows=11,861 loops=1)

17. 11.584 14.655 ↓ 59.3 11,861 1

HashAggregate (cost=493.85..495.85 rows=200 width=4) (actual time=8.327..14.655 rows=11,861 loops=1)

  • Group Key: loan.asset_id
18. 3.071 3.071 ↑ 1.9 11,861 1

CTE Scan on loan (cost=0.00..438.98 rows=21,949 width=4) (actual time=0.001..3.071 rows=11,861 loops=1)

19. 47.444 47.444 ↑ 9.0 1 11,861

Index Scan using nc_asset_asset_ownership_transfer_asset_id on asset_ownership_transfer aot_1 (cost=0.43..8.57 rows=9 width=200) (actual time=0.004..0.004 rows=1 loops=11,861)

  • Index Cond: (asset_id = loan.asset_id)
20.          

SubPlan (for Nested Loop)

21. 59.305 59.305 ↑ 1.0 1 11,861

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs (cost=0.57..8.59 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=11,861)

  • Index Cond: (id = aot_1.loan_balance_snapshot_id)
22. 23.722 23.722 ↑ 1.0 1 11,861

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs_1 (cost=0.57..8.59 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=11,861)

  • Index Cond: (id = aot_1.loan_balance_snapshot_id)
23.          

CTE loan_data_start

24. 15.972 189.061 ↑ 22.8 11,845 1

Nested Loop (cost=494.87..925,141.86 rows=269,484 width=92) (actual time=8.386..189.061 rows=11,845 loops=1)

25. 16.329 66.340 ↑ 22.7 11,861 1

Nested Loop (cost=494.28..2,130.35 rows=269,484 width=16) (actual time=8.353..66.340 rows=11,861 loops=1)

26. 11.233 14.428 ↓ 59.3 11,861 1

HashAggregate (cost=493.85..495.85 rows=200 width=4) (actual time=8.336..14.428 rows=11,861 loops=1)

  • Group Key: loan_1.asset_loan_id
27. 3.195 3.195 ↑ 1.9 11,861 1

CTE Scan on loan loan_1 (cost=0.00..438.98 rows=21,949 width=4) (actual time=0.001..3.195 rows=11,861 loops=1)

28. 35.583 35.583 ↑ 1.0 1 11,861

Index Scan using fki_loan_asset_registration_2_asset_loan on loan_asset_registration l (cost=0.42..8.16 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=11,861)

  • Index Cond: (asset_loan_id = loan_1.asset_loan_id)
29. 11.859 106.749 ↑ 1.0 1 11,861

Limit (cost=0.59..3.41 rows=1 width=61) (actual time=0.009..0.009 rows=1 loops=11,861)

30.          

Initplan (for Limit)

31. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

32. 94.888 94.888 ↑ 153.0 1 11,861

Index Scan using unc_lendify_loan_summary_loan_id_as_of_date_desc on loan_summary ls (cost=0.57..431.28 rows=153 width=61) (actual time=0.008..0.008 rows=1 loops=11,861)

  • Index Cond: ((loan_id = l.lendify_loan_id) AND (as_of_date < $9))
33.          

CTE loan_data_end

34. 13.258 200.974 ↑ 22.7 11,861 1

Nested Loop (cost=494.87..925,141.86 rows=269,484 width=143) (actual time=8.595..200.974 rows=11,861 loops=1)

35. 6.806 69.106 ↑ 22.7 11,861 1

Nested Loop (cost=494.28..2,130.35 rows=269,484 width=16) (actual time=8.546..69.106 rows=11,861 loops=1)

36. 11.658 14.856 ↓ 59.3 11,861 1

HashAggregate (cost=493.85..495.85 rows=200 width=4) (actual time=8.516..14.856 rows=11,861 loops=1)

  • Group Key: loan_2.asset_loan_id
37. 3.198 3.198 ↑ 1.9 11,861 1

CTE Scan on loan loan_2 (cost=0.00..438.98 rows=21,949 width=4) (actual time=0.001..3.198 rows=11,861 loops=1)

38. 47.444 47.444 ↑ 1.0 1 11,861

Index Scan using fki_loan_asset_registration_2_asset_loan on loan_asset_registration l_1 (cost=0.42..8.16 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=11,861)

  • Index Cond: (asset_loan_id = loan_2.asset_loan_id)
39. 11.859 118.610 ↑ 1.0 1 11,861

Limit (cost=0.59..3.41 rows=1 width=108) (actual time=0.010..0.010 rows=1 loops=11,861)

40.          

Initplan (for Limit)

41. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

42. 106.749 106.749 ↑ 153.0 1 11,861

Index Scan using unc_lendify_loan_summary_loan_id_as_of_date_desc on loan_summary ls_1 (cost=0.57..431.28 rows=153 width=108) (actual time=0.009..0.009 rows=1 loops=11,861)

  • Index Cond: ((loan_id = l_1.lendify_loan_id) AND (as_of_date <= $13))
43.          

CTE spv_cashflow

44. 25.660 304.304 ↓ 0.0 0 1

Hash Semi Join (cost=494.32..25,481.57 rows=162,240 width=233) (actual time=304.304..304.304 rows=0 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_4.spv_entity_id)
45. 74.070 278.639 ↑ 48.7 113,189 1

Nested Loop (cost=494.29..9,196.66 rows=5,516,175 width=233) (actual time=8.504..278.639 rows=113,189 loops=1)

46. 11.538 14.793 ↓ 59.3 11,861 1

HashAggregate (cost=493.85..495.85 rows=200 width=4) (actual time=8.485..14.793 rows=11,861 loops=1)

  • Group Key: loan_3.asset_loan_id
47. 3.255 3.255 ↑ 1.9 11,861 1

CTE Scan on loan loan_3 (cost=0.00..438.98 rows=21,949 width=4) (actual time=0.001..3.255 rows=11,861 loops=1)

48. 189.776 189.776 ↑ 3.6 10 11,861

Index Scan using nc_asset_loan_cashflow_loan_id on loan_cashflow cf (cost=0.43..43.14 rows=36 width=233) (actual time=0.005..0.016 rows=10 loops=11,861)

  • Index Cond: (loan_id = loan_3.asset_loan_id)
49. 0.001 0.005 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on constant constant_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

51.          

CTE pmts

52. 0.003 304.336 ↓ 0.0 0 1

HashAggregate (cost=465,106.43..465,115.43 rows=200 width=144) (actual time=304.336..304.336 rows=0 loops=1)

  • Group Key: cf_1.loan_id
53.          

Initplan (for HashAggregate)

54. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_5 (cost=0.00..0.02 rows=1 width=4) (never executed)

55. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_6 (cost=0.00..0.02 rows=1 width=4) (never executed)

56. 0.002 304.333 ↓ 0.0 0 1

Nested Loop (cost=29,486.73..465,090.17 rows=811 width=144) (actual time=304.333..304.333 rows=0 loops=1)

57. 0.002 304.331 ↓ 0.0 0 1

Merge Join (cost=29,486.30..365,338.57 rows=162,240 width=152) (actual time=304.331..304.331 rows=0 loops=1)

  • Merge Cond: (ler.asset_loan_cashflow_id = cf_1.id)
58. 0.018 0.018 ↑ 7,966,347.0 1 1

Index Scan using fki_loan_event_registration_2_asset_loan_cashflow on loan_event_registration ler (cost=0.43..313,097.64 rows=7,966,347 width=16) (actual time=0.018..0.018 rows=1 loops=1)

59. 0.001 304.311 ↓ 0.0 0 1

Materialize (cost=29,485.86..30,297.06 rows=162,240 width=148) (actual time=304.311..304.311 rows=0 loops=1)

60. 0.004 304.310 ↓ 0.0 0 1

Sort (cost=29,485.86..29,891.46 rows=162,240 width=148) (actual time=304.310..304.310 rows=0 loops=1)

  • Sort Key: cf_1.id
  • Sort Method: quicksort Memory: 25kB
61. 304.306 304.306 ↓ 0.0 0 1

CTE Scan on spv_cashflow cf_1 (cost=0.00..3,244.80 rows=162,240 width=148) (actual time=304.306..304.306 rows=0 loops=1)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_event_pkey on loan_event le (cost=0.43..0.60 rows=1 width=8) (never executed)

  • Index Cond: (id = ler.lendify_loan_event_id)
  • Filter: (((posting_date)::date >= $19) AND ((posting_date)::date <= $20))
63.          

CTE whcf

64. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=759,966.35..764,636.00 rows=10,377 width=158) (never executed)

  • Group Key: cf_2.loan_id, cf_2.recipient_entity_id
65.          

Initplan (for GroupAggregate)

66. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_7 (cost=0.00..0.02 rows=1 width=4) (never executed)

67. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_8 (cost=0.00..0.02 rows=1 width=4) (never executed)

68. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_9 (cost=0.00..0.02 rows=1 width=4) (never executed)

69. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_10 (cost=0.00..0.02 rows=1 width=4) (never executed)

70. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_11 (cost=0.00..0.02 rows=1 width=4) (never executed)

71. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_12 (cost=0.00..0.02 rows=1 width=4) (never executed)

72. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_13 (cost=0.00..0.02 rows=1 width=4) (never executed)

73. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_14 (cost=0.00..0.02 rows=1 width=4) (never executed)

74. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_15 (cost=0.00..0.02 rows=1 width=4) (never executed)

75. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_16 (cost=0.00..0.02 rows=1 width=4) (never executed)

76. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_17 (cost=0.00..0.02 rows=1 width=4) (never executed)

77. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_18 (cost=0.00..0.02 rows=1 width=4) (never executed)

78. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_19 (cost=0.00..0.02 rows=1 width=4) (never executed)

79. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_20 (cost=0.00..0.02 rows=1 width=4) (never executed)

80. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_21 (cost=0.00..0.02 rows=1 width=4) (never executed)

81. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_22 (cost=0.00..0.02 rows=1 width=4) (never executed)

82. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_23 (cost=0.00..0.02 rows=1 width=4) (never executed)

83. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_24 (cost=0.00..0.02 rows=1 width=4) (never executed)

84. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_25 (cost=0.00..0.02 rows=1 width=4) (never executed)

85. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_26 (cost=0.00..0.02 rows=1 width=4) (never executed)

86. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_27 (cost=0.00..0.02 rows=1 width=4) (never executed)

87. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_28 (cost=0.00..0.02 rows=1 width=4) (never executed)

88. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_29 (cost=0.00..0.02 rows=1 width=4) (never executed)

89. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_30 (cost=0.00..0.02 rows=1 width=4) (never executed)

90. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_31 (cost=0.00..0.02 rows=1 width=4) (never executed)

91. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_32 (cost=0.00..0.02 rows=1 width=4) (never executed)

92. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_33 (cost=0.00..0.02 rows=1 width=4) (never executed)

93. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_34 (cost=0.00..0.02 rows=1 width=4) (never executed)

94. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_35 (cost=0.00..0.02 rows=1 width=4) (never executed)

95. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_36 (cost=0.00..0.02 rows=1 width=4) (never executed)

96. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_37 (cost=0.00..0.02 rows=1 width=4) (never executed)

97. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_38 (cost=0.00..0.02 rows=1 width=4) (never executed)

98. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_39 (cost=0.00..0.02 rows=1 width=4) (never executed)

99. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_40 (cost=0.00..0.02 rows=1 width=4) (never executed)

100. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_41 (cost=0.00..0.02 rows=1 width=4) (never executed)

101. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_42 (cost=0.00..0.02 rows=1 width=4) (never executed)

102. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_43 (cost=0.00..0.02 rows=1 width=4) (never executed)

103. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_44 (cost=0.00..0.02 rows=1 width=4) (never executed)

104. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_45 (cost=0.00..0.02 rows=1 width=4) (never executed)

105. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_46 (cost=0.00..0.02 rows=1 width=4) (never executed)

106. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_47 (cost=0.00..0.02 rows=1 width=4) (never executed)

107. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_48 (cost=0.00..0.02 rows=1 width=4) (never executed)

108. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_49 (cost=0.00..0.02 rows=1 width=4) (never executed)

109. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_50 (cost=0.00..0.02 rows=1 width=4) (never executed)

110. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_51 (cost=0.00..0.02 rows=1 width=4) (never executed)

111. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_52 (cost=0.00..0.02 rows=1 width=4) (never executed)

112. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_53 (cost=0.00..0.02 rows=1 width=4) (never executed)

113. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_54 (cost=0.00..0.02 rows=1 width=4) (never executed)

114. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_55 (cost=0.00..0.02 rows=1 width=4) (never executed)

115. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_56 (cost=0.00..0.02 rows=1 width=4) (never executed)

116. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_57 (cost=0.00..0.02 rows=1 width=4) (never executed)

117. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_58 (cost=0.00..0.02 rows=1 width=4) (never executed)

118. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_59 (cost=0.00..0.02 rows=1 width=4) (never executed)

119. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_60 (cost=0.00..0.02 rows=1 width=4) (never executed)

120. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_61 (cost=0.00..0.02 rows=1 width=4) (never executed)

121. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_62 (cost=0.00..0.02 rows=1 width=4) (never executed)

122. 0.000 0.000 ↓ 0.0 0

Sort (cost=759,965.23..759,991.18 rows=10,377 width=158) (never executed)

  • Sort Key: cf_2.loan_id, cf_2.recipient_entity_id
123. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=754,967.00..759,273.03 rows=10,377 width=158) (never executed)

  • Merge Cond: (lcri.loan_cashflow_id = cf_2.id)
124. 0.000 0.000 ↓ 0.0 0

Sort (cost=724,924.63..726,417.37 rows=597,093 width=13) (never executed)

  • Sort Key: lcri.loan_cashflow_id
125. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,555.19..657,434.74 rows=597,093 width=13) (never executed)

  • Hash Cond: (lcri.loan_cashflow_report_id = lcr.id)
126. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_cashflow_report_item lcri (cost=0.00..641,165.85 rows=1,798,071 width=13) (never executed)

  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
127. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,265.14..3,265.14 rows=23,204 width=8) (never executed)

128. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_cashflow_report lcr (cost=0.00..3,265.14 rows=23,204 width=8) (never executed)

  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= $78))
129. 0.000 0.000 ↓ 0.0 0

Materialize (cost=30,042.36..30,853.56 rows=162,240 width=153) (never executed)

130. 0.000 0.000 ↓ 0.0 0

Sort (cost=30,042.36..30,447.96 rows=162,240 width=153) (never executed)

  • Sort Key: cf_2.id
131. 0.000 0.000 ↓ 0.0 0

CTE Scan on spv_cashflow cf_2 (cost=0.00..3,244.80 rows=162,240 width=153) (never executed)

132.          

CTE whcf_adj

133. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=5,460.96..5,465.91 rows=55 width=153) (never executed)

  • Group Key: cf_3.loan_id, cf_3.recipient_entity_id
134.          

Initplan (for HashAggregate)

135. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_63 (cost=0.00..0.02 rows=1 width=4) (never executed)

136. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_64 (cost=0.00..0.02 rows=1 width=4) (never executed)

137. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_65 (cost=0.00..0.02 rows=1 width=4) (never executed)

138. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_66 (cost=0.00..0.02 rows=1 width=4) (never executed)

139. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_67 (cost=0.00..0.02 rows=1 width=4) (never executed)

140. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_68 (cost=0.00..0.02 rows=1 width=4) (never executed)

141. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_69 (cost=0.00..0.02 rows=1 width=4) (never executed)

142. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_70 (cost=0.00..0.02 rows=1 width=4) (never executed)

143. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_71 (cost=0.00..0.02 rows=1 width=4) (never executed)

144. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_72 (cost=0.00..0.02 rows=1 width=4) (never executed)

145. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_73 (cost=0.00..0.02 rows=1 width=4) (never executed)

146. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_74 (cost=0.00..0.02 rows=1 width=4) (never executed)

147. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_75 (cost=0.00..0.02 rows=1 width=4) (never executed)

148. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_76 (cost=0.00..0.02 rows=1 width=4) (never executed)

149. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_77 (cost=0.00..0.02 rows=1 width=4) (never executed)

150. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_78 (cost=0.00..0.02 rows=1 width=4) (never executed)

151. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_79 (cost=0.00..0.02 rows=1 width=4) (never executed)

152. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_80 (cost=0.00..0.02 rows=1 width=4) (never executed)

153. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_81 (cost=0.00..0.02 rows=1 width=4) (never executed)

154. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_82 (cost=0.00..0.02 rows=1 width=4) (never executed)

155. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_83 (cost=0.00..0.02 rows=1 width=4) (never executed)

156. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_84 (cost=0.00..0.02 rows=1 width=4) (never executed)

157. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_85 (cost=0.00..0.02 rows=1 width=4) (never executed)

158. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_86 (cost=0.00..0.02 rows=1 width=4) (never executed)

159. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_87 (cost=0.00..0.02 rows=1 width=4) (never executed)

160. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_88 (cost=0.00..0.02 rows=1 width=4) (never executed)

161. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_89 (cost=0.00..0.02 rows=1 width=4) (never executed)

162. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_90 (cost=0.00..0.02 rows=1 width=4) (never executed)

163. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_91 (cost=0.00..0.02 rows=1 width=4) (never executed)

164. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_92 (cost=0.00..0.02 rows=1 width=4) (never executed)

165. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_93 (cost=0.00..0.02 rows=1 width=4) (never executed)

166. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_94 (cost=0.00..0.02 rows=1 width=4) (never executed)

167. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_95 (cost=0.00..0.02 rows=1 width=4) (never executed)

168. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_96 (cost=0.00..0.02 rows=1 width=4) (never executed)

169. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_97 (cost=0.00..0.02 rows=1 width=4) (never executed)

170. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_98 (cost=0.00..0.02 rows=1 width=4) (never executed)

171. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_99 (cost=0.00..0.02 rows=1 width=4) (never executed)

172. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_100 (cost=0.00..0.02 rows=1 width=4) (never executed)

173. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_101 (cost=0.00..0.02 rows=1 width=4) (never executed)

174. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_102 (cost=0.00..0.02 rows=1 width=4) (never executed)

175. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_103 (cost=0.00..0.02 rows=1 width=4) (never executed)

176. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_104 (cost=0.00..0.02 rows=1 width=4) (never executed)

177. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_105 (cost=0.00..0.02 rows=1 width=4) (never executed)

178. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_106 (cost=0.00..0.02 rows=1 width=4) (never executed)

179. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_107 (cost=0.00..0.02 rows=1 width=4) (never executed)

180. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_108 (cost=0.00..0.02 rows=1 width=4) (never executed)

181. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_109 (cost=0.00..0.02 rows=1 width=4) (never executed)

182. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_110 (cost=0.00..0.02 rows=1 width=4) (never executed)

183. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_111 (cost=0.00..0.02 rows=1 width=4) (never executed)

184. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=0.44..5,376.96 rows=270 width=153) (never executed)

185. 0.000 0.000 ↓ 0.0 0

CTE Scan on spv_cashflow cf_3 (cost=0.00..4,056.00 rows=541 width=157) (never executed)

  • Filter: ((cashflow_date <= $128) AND ((cashflow_type)::text = ANY ('{CREDIT,CREDIT_REVERSAL}'::text[])))
186. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri_1 (cost=0.44..5.14 rows=2 width=4) (never executed)

  • Index Cond: (loan_cashflow_id = cf_3.id)
  • Heap Fetches: 0
187.          

CTE bb_data

188. 231.916 9,919.414 ↑ 111,966,612,151,966,288.0 11,861 1

Merge Right Join (cost=35,930,570,453.53..1,090,651,189,076,660,977,664.00 rows=1,328,035,986,734,472,232,960 width=3,572) (actual time=8,979.199..9,919.414 rows=11,861 loops=1)

  • Merge Cond: (ln_1.asset_loan_id = ln.asset_loan_id)
  • Filter: ((hashed SubPlan 211) OR (COALESCE(aot_2.ownership_end_date, '1900-01-01'::date) >= (date_trunc('month'::text, ($221)::timestamp with time zone))::date) OR ((((COALESCE(whcf.prin_received_cumulative, 0.00)) + COALESCE((COALESCE(whcf_adj.prin_received_cumulative, 0.00)), 0.00)) + COALESCE(round(aot_sale.principal_balance, 2), 0.00)) <> round(aot_2.principal_balance, 2)) OR ((((COALESCE(whcf.orig_fee_received_cumulative, 0.00)) + COALESCE((COALESCE(whcf_adj.orig_fee_received_cumulative, 0.00)), 0.00)) + COALESCE(round(aot_sale.origination_fee_balance, 2), 0.00)) <> round(aot_2.origination_fee_balance, 2)) OR (COALESCE((GREATEST(whcf.max_cash_transfer_date, whcf_adj.max_adj_transfer_date)), '1900-01-01'::date) >= $222))
189.          

Initplan (for Merge Right Join)

190. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_112 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

191. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_113 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

192. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_114 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

193. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_115 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

194. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_116 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

195. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_117 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

196. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_119 (cost=0.00..0.02 rows=1 width=4) (never executed)

197. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_120 (cost=0.00..0.02 rows=1 width=4) (never executed)

198. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_121 (cost=0.00..0.02 rows=1 width=4) (never executed)

199. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_122 (cost=0.00..0.02 rows=1 width=4) (never executed)

200. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_123 (cost=0.00..0.02 rows=1 width=4) (never executed)

201. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_124 (cost=0.00..0.02 rows=1 width=4) (never executed)

202. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_125 (cost=0.00..0.02 rows=1 width=4) (never executed)

203. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_126 (cost=0.00..0.02 rows=1 width=4) (never executed)

204. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_127 (cost=0.00..0.02 rows=1 width=4) (never executed)

205. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_128 (cost=0.00..0.02 rows=1 width=4) (never executed)

206. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_129 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

207. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_130 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

208. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_131 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

209. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_132 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

210. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_133 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

211. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_135 (cost=0.00..0.02 rows=1 width=4) (never executed)

212. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_136 (cost=0.00..0.02 rows=1 width=4) (never executed)

213. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_137 (cost=0.00..0.02 rows=1 width=4) (never executed)

214. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_138 (cost=0.00..0.02 rows=1 width=4) (never executed)

215. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_139 (cost=0.00..0.02 rows=1 width=4) (never executed)

216. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_140 (cost=0.00..0.02 rows=1 width=4) (never executed)

217. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_142 (cost=0.00..0.02 rows=1 width=4) (never executed)

218. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_143 (cost=0.00..0.02 rows=1 width=4) (never executed)

219. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_144 (cost=0.00..0.02 rows=1 width=4) (never executed)

220. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_145 (cost=0.00..0.02 rows=1 width=4) (never executed)

221. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_146 (cost=0.00..0.02 rows=1 width=4) (never executed)

222. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_147 (cost=0.00..0.02 rows=1 width=4) (never executed)

223. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_149 (cost=0.00..0.02 rows=1 width=4) (never executed)

224. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_150 (cost=0.00..0.02 rows=1 width=4) (never executed)

225. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_151 (cost=0.00..0.02 rows=1 width=4) (never executed)

226. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_152 (cost=0.00..0.02 rows=1 width=4) (never executed)

227. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_153 (cost=0.00..0.02 rows=1 width=4) (never executed)

228. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_154 (cost=0.00..0.02 rows=1 width=4) (never executed)

229. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_155 (cost=0.00..0.02 rows=1 width=4) (never executed)

230. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_156 (cost=0.00..0.02 rows=1 width=4) (never executed)

231. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_157 (cost=0.00..0.02 rows=1 width=4) (never executed)

232. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_158 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

233. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_159 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

234. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_160 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

235. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_161 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

236. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_162 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

237. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_164 (cost=0.00..0.02 rows=1 width=4) (never executed)

238. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_165 (cost=0.00..0.02 rows=1 width=4) (never executed)

239. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_166 (cost=0.00..0.02 rows=1 width=4) (never executed)

240. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_167 (cost=0.00..0.02 rows=1 width=4) (never executed)

241. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_169 (cost=0.00..0.02 rows=1 width=4) (never executed)

242. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_170 (cost=0.00..0.02 rows=1 width=4) (never executed)

243. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_171 (cost=0.00..0.02 rows=1 width=4) (never executed)

244. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_172 (cost=0.00..0.02 rows=1 width=4) (never executed)

245. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_174 (cost=0.00..0.02 rows=1 width=4) (never executed)

246. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_175 (cost=0.00..0.02 rows=1 width=4) (never executed)

247. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_176 (cost=0.00..0.02 rows=1 width=4) (never executed)

248. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_177 (cost=0.00..0.02 rows=1 width=4) (never executed)

249. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_178 (cost=0.00..0.02 rows=1 width=4) (never executed)

250. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_179 (cost=0.00..0.02 rows=1 width=4) (never executed)

251. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_181 (cost=0.00..0.02 rows=1 width=4) (never executed)

252. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_182 (cost=0.00..0.02 rows=1 width=4) (never executed)

253. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_183 (cost=0.00..0.02 rows=1 width=4) (never executed)

254. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_184 (cost=0.00..0.02 rows=1 width=4) (never executed)

255. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_185 (cost=0.00..0.02 rows=1 width=4) (never executed)

256. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_186 (cost=0.00..0.02 rows=1 width=4) (never executed)

257. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_187 (cost=0.00..0.02 rows=1 width=4) (never executed)

258. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_188 (cost=0.00..0.02 rows=1 width=4) (never executed)

259. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_189 (cost=0.00..0.02 rows=1 width=4) (never executed)

260. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_190 (cost=0.00..0.02 rows=1 width=4) (never executed)

261. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_191 (cost=0.00..0.02 rows=1 width=4) (never executed)

262. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_192 (cost=0.00..0.02 rows=1 width=4) (never executed)

263. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_193 (cost=0.00..0.02 rows=1 width=4) (never executed)

264. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_194 (cost=0.00..0.02 rows=1 width=4) (never executed)

265. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_195 (cost=0.00..0.02 rows=1 width=4) (never executed)

266. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_196 (cost=0.00..0.02 rows=1 width=4) (never executed)

267. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_197 (cost=0.00..0.02 rows=1 width=4) (never executed)

268. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_198 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

269. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_199 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

270. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_200 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

271. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_202 (cost=0.00..0.02 rows=1 width=4) (never executed)

272. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_203 (cost=0.00..0.02 rows=1 width=4) (never executed)

273. 0.000 600.880 ↓ 0.0 0 1

Merge Left Join (cost=1,702,538.43..1,822,452.82 rows=7,025,812 width=2,356) (actual time=600.880..600.880 rows=0 loops=1)

  • Merge Cond: ((ln_1.asset_loan_id = whcf_adj.loan_id) AND (aot_3.to_entity_id = whcf_adj.recipient_entity_id))
274. 0.001 600.880 ↓ 0.0 0 1

Merge Left Join (cost=1,702,535.74..1,787,152.02 rows=7,025,812 width=1,388) (actual time=600.880..600.880 rows=0 loops=1)

  • Merge Cond: ((ln_1.asset_loan_id = whcf.loan_id) AND (aot_3.to_entity_id = whcf.recipient_entity_id))
275. 0.009 600.879 ↓ 0.0 0 1

Sort (cost=1,696,351.00..1,713,915.53 rows=7,025,812 width=100) (actual time=600.879..600.879 rows=0 loops=1)

  • Sort Key: ln_1.asset_loan_id, aot_3.to_entity_id
  • Sort Method: quicksort Memory: 25kB
276. 0.003 600.870 ↓ 0.0 0 1

Merge Join (cost=124,739.93..128,914.46 rows=7,025,812 width=100) (actual time=600.870..600.870 rows=0 loops=1)

  • Merge Cond: ((constant_206.spv_entity_id = aot_3.to_entity_id) AND (ln_1.asset_id = aot_3.asset_id))
277. 4.340 599.111 ↑ 392,668.0 1 1

Sort (cost=92,073.04..93,054.71 rows=392,668 width=60) (actual time=599.111..599.111 rows=1 loops=1)

  • Sort Key: constant_206.spv_entity_id, ln_1.asset_id
  • Sort Method: quicksort Memory: 940kB
278. 5.457 594.771 ↑ 33.1 11,861 1

Merge Left Join (cost=34,822.12..40,821.89 rows=392,668 width=60) (actual time=585.771..594.771 rows=11,861 loops=1)

  • Merge Cond: (ln_1.asset_id = aot_sale.asset_id)
279. 8.756 394.534 ↑ 1.9 11,861 1

Sort (cost=2,241.23..2,296.10 rows=21,949 width=16) (actual time=390.987..394.534 rows=11,861 loops=1)

  • Sort Key: ln_1.asset_id
  • Sort Method: quicksort Memory: 940kB
280. 5.794 385.778 ↑ 1.9 11,861 1

Nested Loop (cost=0.02..658.50 rows=21,949 width=16) (actual time=363.447..385.778 rows=11,861 loops=1)

281. 0.005 0.010 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Group Key: constant_206.spv_entity_id
282. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on constant constant_206 (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

283. 379.974 379.974 ↑ 1.9 11,861 1

CTE Scan on loan ln_1 (cost=0.00..438.98 rows=21,949 width=12) (actual time=363.436..379.974 rows=11,861 loops=1)

284. 0.013 194.780 ↓ 0.0 0 1

Sort (cost=32,580.89..32,589.84 rows=3,578 width=48) (actual time=194.780..194.780 rows=0 loops=1)

  • Sort Key: aot_sale.asset_id
  • Sort Method: quicksort Memory: 25kB
285. 0.002 194.767 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.00..32,369.70 rows=3,578 width=48) (actual time=194.767..194.767 rows=0 loops=1)

  • Join Filter: (aot_sale.from_entity_id = constant_205.spv_entity_id)
286. 194.765 194.765 ↓ 0.0 0 1

CTE Scan on aot aot_sale (cost=0.00..32,208.66 rows=7,157 width=52) (actual time=194.765..194.765 rows=0 loops=1)

  • Filter: (to_entity_id = 130)
  • Rows Removed by Filter: 11,861
287. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant constant_205 (cost=0.00..0.02 rows=1 width=4) (never executed)

288. 0.010 1.756 ↓ 0.0 0 1

Sort (cost=32,666.89..32,684.78 rows=7,157 width=52) (actual time=1.756..1.756 rows=0 loops=1)

  • Sort Key: aot_3.to_entity_id, aot_3.asset_id
  • Sort Method: quicksort Memory: 25kB
289. 1.746 1.746 ↓ 0.0 0 1

CTE Scan on aot aot_3 (cost=0.00..32,208.66 rows=7,157 width=52) (actual time=1.746..1.746 rows=0 loops=1)

  • Filter: (from_entity_id = 130)
  • Rows Removed by Filter: 11,861
290. 0.000 0.000 ↓ 0.0 0

Materialize (cost=6,184.74..6,236.63 rows=10,377 width=1,168) (never executed)

291. 0.000 0.000 ↓ 0.0 0

Sort (cost=6,184.74..6,210.69 rows=10,377 width=1,168) (never executed)

  • Sort Key: whcf.loan_id, whcf.recipient_entity_id
292. 0.000 0.000 ↓ 0.0 0

CTE Scan on whcf (cost=0.00..207.54 rows=10,377 width=1,168) (never executed)

293. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.69..2.83 rows=55 width=1,036) (never executed)

  • Sort Key: whcf_adj.loan_id, whcf_adj.recipient_entity_id
294. 0.000 0.000 ↓ 0.0 0

CTE Scan on whcf_adj (cost=0.00..1.10 rows=55 width=1,036) (never executed)

295. 19.818 9,086.593 ↑ 3,187,310,852,956.4 11,861 1

Materialize (cost=35,928,867,913.23..1,323,691,687,528,853.50 rows=37,804,694,026,915,752 width=1,220) (actual time=8,377.936..9,086.593 rows=11,861 loops=1)

296. 8.308 9,066.775 ↑ 3,187,310,852,956.4 11,861 1

Merge Left Join (cost=35,928,867,913.23..1,229,179,952,461,564.25 rows=37,804,694,026,915,752 width=1,220) (actual time=8,377.928..9,066.775 rows=11,861 loops=1)

  • Merge Cond: (ln.asset_loan_id = pmts.loan_id)
297. 16.657 8,754.121 ↑ 3,187,310,852,956.4 11,861 1

Merge Left Join (cost=35,928,867,901.59..567,597,806,990,527.12 rows=37,804,694,026,915,752 width=996) (actual time=8,073.578..8,754.121 rows=11,861 loops=1)

  • Merge Cond: (ln.asset_loan_id = ln_end.asset_loan_id)
298. 14.768 8,712.749 ↑ 2,365,491,719.7 11,861 1

Merge Left Join (cost=35,928,807,807.17..457,253,782,804.36 rows=28,057,097,287,346 width=764) (actual time=8,060.055..8,712.749 rows=11,861 loops=1)

  • Merge Cond: (ln.asset_loan_id = ln_start.asset_loan_id)
299. 165.290 8,476.616 ↑ 1,755,571.2 11,861 1

Merge Left Join (cost=35,928,756,000.75..36,345,213,939.62 rows=20,822,829,769 width=608) (actual time=7,849.510..8,476.616 rows=11,861 loops=1)

  • Merge Cond: (ln.asset_loan_id = lsr.loan_id)
300. 14.795 7,136.102 ↑ 1,755,571.2 11,861 1

Sort (cost=35,928,673,869.83..35,980,730,944.25 rows=20,822,829,769 width=576) (actual time=7,130.654..7,136.102 rows=11,861 loops=1)

  • Sort Key: ln.asset_loan_id
  • Sort Method: quicksort Memory: 3,486kB
301. 8.747 7,121.307 ↑ 1,755,571.2 11,861 1

Merge Right Join (cost=20,217,700.36..332,604,978.54 rows=20,822,829,769 width=576) (actual time=7,103.482..7,121.307 rows=11,861 loops=1)

  • Merge Cond: (aot_2.asset_id = ln.asset_id)
302. 0.002 7.612 ↓ 0.0 0 1

Sort (cost=67,065.97..67,662.42 rows=238,582 width=56) (actual time=7.612..7.612 rows=0 loops=1)

  • Sort Key: aot_2.asset_id
  • Sort Method: quicksort Memory: 25kB
303. 2.670 7.610 ↓ 0.0 0 1

Hash Join (cost=0.04..37,600.67 rows=238,582 width=56) (actual time=7.610..7.610 rows=0 loops=1)

  • Hash Cond: (aot_2.to_entity_id = constant_204.spv_entity_id)
304. 4.925 4.925 ↑ 40.2 11,861 1

CTE Scan on aot aot_2 (cost=0.00..35,787.40 rows=477,165 width=60) (actual time=0.009..4.925 rows=11,861 loops=1)

  • Filter: (ownership_start_date <= ($219 + 1))
305. 0.009 0.015 ↑ 1.0 1 1

Hash (cost=0.03..0.03 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
306. 0.004 0.006 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Group Key: constant_204.spv_entity_id
307. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_204 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

308. 5.121 7,104.948 ↑ 1,471.7 11,861 1

Materialize (cost=20,150,634.40..20,237,911.85 rows=17,455,491 width=528) (actual time=7,095.866..7,104.948 rows=11,861 loops=1)

309. 15.386 7,099.827 ↑ 1,471.7 11,861 1

Sort (cost=20,150,634.40..20,194,273.12 rows=17,455,491 width=528) (actual time=7,095.864..7,099.827 rows=11,861 loops=1)

  • Sort Key: ln.asset_id
  • Sort Method: quicksort Memory: 3,492kB
310. 76.610 7,084.441 ↑ 1,471.7 11,861 1

Hash Left Join (cost=687,957.91..5,700,874.66 rows=17,455,491 width=528) (actual time=6,925.912..7,084.441 rows=11,861 loops=1)

  • Hash Cond: (nt.loan_id = borrower.loan_id)
311. 61.369 6,497.414 ↑ 1,471.7 11,861 1

Hash Left Join (cost=655,272.57..3,274,008.32 rows=17,455,491 width=474) (actual time=6,414.223..6,497.414 rows=11,861 loops=1)

  • Hash Cond: (nt.loan_id = lp.loan_id)
312. 12.749 4,953.168 ↑ 1,471.7 11,861 1

Merge Left Join (cost=577,888.37..840,228.12 rows=17,455,491 width=470) (actual time=4,926.530..4,953.168 rows=11,861 loops=1)

  • Merge Cond: (ln.asset_id = aot_curr_owner.asset_id)
313. 15.491 4,907.812 ↑ 1.9 11,861 1

Sort (cost=500,078.47..500,133.34 rows=21,949 width=433) (actual time=4,902.957..4,907.812 rows=11,861 loops=1)

  • Sort Key: ln.asset_id
  • Sort Method: quicksort Memory: 3,483kB
314. 61.373 4,892.321 ↑ 1.9 11,861 1

Hash Left Join (cost=105,843.50..494,141.74 rows=21,949 width=433) (actual time=1,394.435..4,892.321 rows=11,861 loops=1)

  • Hash Cond: (ln.asset_loan_id = loan_4.id)
315. 7.478 4,338.459 ↑ 1.9 11,861 1

Hash Left Join (cost=65,943.25..449,398.35 rows=21,949 width=432) (actual time=831.347..4,338.459 rows=11,861 loops=1)

  • Hash Cond: (nt.tenant_id = lt.id)
316. 311.267 4,330.801 ↑ 1.9 11,861 1

Hash Right Join (cost=65,933.46..449,086.76 rows=21,949 width=435) (actual time=831.155..4,330.801 rows=11,861 loops=1)

  • Hash Cond: (de.loan_number = nt.loan_number)
317. 358.402 3,208.474 ↓ 12.8 538,838 1

Hash Join (cost=84.10..381,061.54 rows=42,059 width=51) (actual time=1.525..3,208.474 rows=538,838 loops=1)

  • Hash Cond: ((de.tenant_id = loc.tenant_id) AND (de.location_id = loc.location_id))
318. 2,848.638 2,848.638 ↑ 1.0 538,987 1

Seq Scan on loan_event de (cost=0.00..376,393.08 rows=555,170 width=23) (actual time=0.077..2,848.638 rows=538,987 loops=1)

  • Filter: (transaction_type = 'DISBURSEMENT'::text)
  • Rows Removed by Filter: 8,984,889
319. 0.687 1.434 ↓ 1.0 1,765 1

Hash (cost=57.64..57.64 rows=1,764 width=40) (actual time=1.434..1.434 rows=1,765 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 144kB
320. 0.747 0.747 ↓ 1.0 1,765 1

Seq Scan on location loc (cost=0.00..57.64 rows=1,764 width=40) (actual time=0.007..0.747 rows=1,765 loops=1)

321. 9.319 811.060 ↑ 1.9 11,861 1

Hash (cost=64,396.00..64,396.00 rows=21,949 width=414) (actual time=811.060..811.060 rows=11,861 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 640kB
322. 68.473 801.741 ↑ 1.9 11,861 1

Hash Join (cost=56,292.22..64,396.00 rows=21,949 width=414) (actual time=730.002..801.741 rows=11,861 loops=1)

  • Hash Cond: ((ln.external_id)::text = nt.loan_number)
323. 4.744 4.744 ↑ 1.9 11,861 1

CTE Scan on loan ln (cost=0.00..438.98 rows=21,949 width=362) (actual time=0.002..4.744 rows=11,861 loops=1)

324. 263.006 728.524 ↓ 1.0 538,987 1

Hash (cost=44,298.32..44,298.32 rows=538,632 width=52) (actual time=728.524..728.524 rows=538,987 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,418kB
325. 465.518 465.518 ↓ 1.0 538,987 1

Seq Scan on loan_product nt (cost=0.00..44,298.32 rows=538,632 width=52) (actual time=0.009..465.518 rows=538,987 loops=1)

326. 0.075 0.180 ↓ 1.1 230 1

Hash (cost=7.13..7.13 rows=213 width=5) (actual time=0.180..0.180 rows=230 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
327. 0.105 0.105 ↓ 1.1 230 1

Seq Scan on tenant lt (cost=0.00..7.13 rows=213 width=5) (actual time=0.005..0.105 rows=230 loops=1)

328. 182.057 492.489 ↓ 1.0 538,938 1

Hash (cost=31,076.10..31,076.10 rows=537,852 width=5) (actual time=492.489..492.489 rows=538,938 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,457kB
329. 310.432 310.432 ↓ 1.0 538,938 1

Seq Scan on loan loan_4 (cost=0.00..31,076.10 rows=537,852 width=5) (actual time=0.014..310.432 rows=538,938 loops=1)

  • Filter: (originator_id = 1,003)
  • Rows Removed by Filter: 8,825
330. 4.990 32.607 ↑ 13.4 11,861 1

Materialize (cost=77,809.90..78,605.17 rows=159,055 width=41) (actual time=23.566..32.607 rows=11,861 loops=1)

331. 9.416 27.617 ↑ 13.4 11,861 1

Sort (cost=77,809.90..78,207.54 rows=159,055 width=41) (actual time=23.563..27.617 rows=11,861 loops=1)

  • Sort Key: aot_curr_owner.asset_id
  • Sort Method: quicksort Memory: 940kB
332. 6.440 18.201 ↑ 13.4 11,861 1

Hash Left Join (cost=6.71..59,175.21 rows=159,055 width=41) (actual time=1.312..18.201 rows=11,861 loops=1)

  • Hash Cond: (aot_curr_owner.to_entity_id = le_1.id)
333. 11.624 11.624 ↑ 13.4 11,861 1

CTE Scan on aot aot_curr_owner (cost=0.00..57,259.84 rows=159,055 width=12) (actual time=1.160..11.624 rows=11,861 loops=1)

  • Filter: ((($217 + 1) >= ownership_start_date) AND (($218 + 1) <= COALESCE((ownership_end_date - 1), (timezone('PST8PDT'::text, now()))::date)))
334. 0.058 0.137 ↑ 1.1 155 1

Hash (cost=4.65..4.65 rows=165 width=33) (actual time=0.137..0.137 rows=155 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
335. 0.079 0.079 ↑ 1.1 155 1

Seq Scan on legal_entity le_1 (cost=0.00..4.65 rows=165 width=33) (actual time=0.024..0.079 rows=155 loops=1)

336. 184.031 1,482.877 ↓ 1.0 538,987 1

Hash (cost=68,020.30..68,020.30 rows=538,632 width=12) (actual time=1,482.877..1,482.877 rows=538,987 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,473kB
337. 458.425 1,298.846 ↓ 1.0 538,987 1

Hash Right Join (cost=53,662.22..68,020.30 rows=538,632 width=12) (actual time=635.520..1,298.846 rows=538,987 loops=1)

  • Hash Cond: (loan_data_end.loan_id = lp.loan_id)
338. 216.318 216.318 ↑ 22.7 11,861 1

CTE Scan on loan_data_end (cost=0.00..5,389.68 rows=269,484 width=16) (actual time=8.601..216.318 rows=11,861 loops=1)

339. 192.371 624.103 ↓ 1.0 538,987 1

Hash (cost=44,298.32..44,298.32 rows=538,632 width=16) (actual time=624.103..624.103 rows=538,987 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,605kB
340. 431.732 431.732 ↓ 1.0 538,987 1

Seq Scan on loan_product lp (cost=0.00..44,298.32 rows=538,632 width=16) (actual time=0.007..431.732 rows=538,987 loops=1)

341. 257.479 510.417 ↑ 1.0 538,987 1

Hash (cost=19,616.15..19,616.15 rows=539,615 width=70) (actual time=510.417..510.417 rows=538,987 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,434kB
342. 252.938 252.938 ↑ 1.0 538,987 1

Seq Scan on borrower_application borrower (cost=0.00..19,616.15 rows=539,615 width=70) (actual time=0.015..252.938 rows=538,987 loops=1)

343. 222.822 1,175.224 ↓ 1.0 547,249 1

Materialize (cost=82,130.92..84,817.90 rows=537,397 width=36) (actual time=712.267..1,175.224 rows=547,249 loops=1)

344. 712.781 952.402 ↓ 1.0 547,249 1

Sort (cost=82,130.92..83,474.41 rows=537,397 width=36) (actual time=712.264..952.402 rows=547,249 loops=1)

  • Sort Key: lsr.loan_id
  • Sort Method: external merge Disk: 26,648kB
345. 239.621 239.621 ↓ 1.0 547,763 1

Seq Scan on loan_securitization_review lsr (cost=0.00..16,285.97 rows=537,397 width=36) (actual time=0.011..239.621 rows=547,763 loops=1)

346. 5.530 221.365 ↑ 22.8 11,845 1

Materialize (cost=51,806.42..53,153.84 rows=269,484 width=164) (actual time=210.539..221.365 rows=11,845 loops=1)

347. 13.560 215.835 ↑ 22.8 11,845 1

Sort (cost=51,806.42..52,480.13 rows=269,484 width=164) (actual time=210.534..215.835 rows=11,845 loops=1)

  • Sort Key: ln_start.asset_loan_id
  • Sort Method: quicksort Memory: 1,660kB
348. 202.275 202.275 ↑ 22.8 11,845 1

CTE Scan on loan_data_start ln_start (cost=0.00..5,389.68 rows=269,484 width=164) (actual time=8.391..202.275 rows=11,845 loops=1)

349. 5.070 24.715 ↑ 22.7 11,861 1

Materialize (cost=60,094.42..61,441.84 rows=269,484 width=240) (actual time=13.518..24.715 rows=11,861 loops=1)

350. 14.392 19.645 ↑ 22.7 11,861 1

Sort (cost=60,094.42..60,768.13 rows=269,484 width=240) (actual time=13.514..19.645 rows=11,861 loops=1)

  • Sort Key: ln_end.asset_loan_id
  • Sort Method: quicksort Memory: 2,052kB
351. 5.253 5.253 ↑ 22.7 11,861 1

CTE Scan on loan_data_end ln_end (cost=0.00..5,389.68 rows=269,484 width=240) (actual time=0.003..5.253 rows=11,861 loops=1)

352. 0.008 304.346 ↓ 0.0 0 1

Sort (cost=11.64..12.14 rows=200 width=228) (actual time=304.346..304.346 rows=0 loops=1)

  • Sort Key: pmts.loan_id
  • Sort Method: quicksort Memory: 25kB
353. 304.338 304.338 ↓ 0.0 0 1

CTE Scan on pmts (cost=0.00..4.00 rows=200 width=228) (actual time=304.338..304.338 rows=0 loops=1)

354.          

SubPlan (for Merge Right Join)

355. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_118 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

356. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_134 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

357. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_141 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

358. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_148 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

359. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_163 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

360. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_168 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

361. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_173 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

362. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_180 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

363. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on constant constant_201 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

Planning time : 120.805 ms
Execution time : 9,999.243 ms