explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K6ds

Settings
# exclusive inclusive rows x rows loops node
1. 70,825.390 70,825.390 ↑ 1.0 87,631 1

CTE Scan on bb_data (cost=12,863,441.29..12,865,236.73 rows=89,772 width=5,003) (actual time=68,700.661..70,825.390 rows=87,631 loops=1)

2.          

CTE constant

3. 0.002 0.002 ↑ 1.0 1 1

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

4.          

CTE aot_was_in_spv

5. 77.443 414.385 ↓ 1.3 87,631 1

HashAggregate (cost=100,499.81..101,158.27 rows=65,846 width=4) (actual time=377.014..414.385 rows=87,631 loops=1)

  • Group Key: lo.id
6. 81.383 336.942 ↓ 1.3 87,631 1

Nested Loop (cost=1,630.08..100,335.20 rows=65,846 width=4) (actual time=5.866..336.942 rows=87,631 loops=1)

7. 43.138 80.297 ↓ 1.3 87,631 1

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (actual time=5.854..80.297 rows=87,631 loops=1)

8. 0.005 0.009 ↑ 1.0 1 1

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

  • Group Key: constant.owner_entity_id
9. 0.004 0.004 ↑ 1.0 1 1

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

10. 31.756 37.150 ↓ 1.3 87,631 1

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,629.63..66,039.29 rows=66,962 width=8) (actual time=5.843..37.150 rows=87,631 loops=1)

  • Recheck Cond: (to_entity_id = constant.owner_entity_id)
  • Filter: ((ownership_end_date IS NULL) OR (to_entity_id <> 504))
  • Heap Blocks: exact=3341
11. 5.394 5.394 ↓ 1.1 87,631 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=5.394..5.394 rows=87,631 loops=1)

  • Index Cond: (to_entity_id = constant.owner_entity_id)
12. 175.262 175.262 ↑ 1.0 1 87,631

Index Scan using unc_asset_loan_asset_id on loan lo (cost=0.42..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=87,631)

  • Index Cond: (asset_id = aot.asset_id)
  • Filter: (originator_id = 1003)
13.          

CTE loan_data_start

14. 89.454 1,223.051 ↑ 3.2 84,728 1

Nested Loop (cost=1,482.55..926,129.55 rows=269,484 width=92) (actual time=53.727..1,223.051 rows=84,728 loops=1)

15. 83.930 432.549 ↑ 3.1 87,631 1

Nested Loop (cost=1,481.96..3,118.03 rows=269,484 width=16) (actual time=53.680..432.549 rows=87,631 loops=1)

16. 63.459 85.726 ↓ 438.2 87,631 1

HashAggregate (cost=1,481.54..1,483.54 rows=200 width=4) (actual time=53.652..85.726 rows=87,631 loops=1)

  • Group Key: aot_was_in_spv.asset_loan_id
17. 22.267 22.267 ↓ 1.3 87,631 1

CTE Scan on aot_was_in_spv (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=0.006..22.267 rows=87,631 loops=1)

18. 262.893 262.893 ↑ 1.0 1 87,631

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=87,631)

  • Index Cond: (asset_loan_id = aot_was_in_spv.asset_loan_id)
19. 87.629 701.048 ↑ 1.0 1 87,631

Limit (cost=0.59..3.41 rows=1 width=61) (actual time=0.008..0.008 rows=1 loops=87,631)

20.          

Initplan (for Limit)

21. 0.002 0.002 ↑ 1.0 1 1

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

22. 613.417 613.417 ↑ 153.0 1 87,631

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.007..0.007 rows=1 loops=87,631)

  • Index Cond: ((loan_id = l.lendify_loan_id) AND (as_of_date < $4))
23.          

CTE loan_data_end

24. 120.007 1,574.586 ↑ 3.1 87,631 1

Nested Loop (cost=1,482.55..926,129.55 rows=269,484 width=143) (actual time=54.199..1,574.586 rows=87,631 loops=1)

25. 140.282 578.269 ↑ 3.1 87,631 1

Nested Loop (cost=1,481.96..3,118.03 rows=269,484 width=16) (actual time=54.148..578.269 rows=87,631 loops=1)

26. 64.927 87.463 ↓ 438.2 87,631 1

HashAggregate (cost=1,481.54..1,483.54 rows=200 width=4) (actual time=54.119..87.463 rows=87,631 loops=1)

  • Group Key: aot_was_in_spv_1.asset_loan_id
27. 22.536 22.536 ↓ 1.3 87,631 1

CTE Scan on aot_was_in_spv aot_was_in_spv_1 (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=0.010..22.536 rows=87,631 loops=1)

28. 350.524 350.524 ↑ 1.0 1 87,631

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.004..0.004 rows=1 loops=87,631)

  • Index Cond: (asset_loan_id = aot_was_in_spv_1.asset_loan_id)
29. 87.629 876.310 ↑ 1.0 1 87,631

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

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.001..0.002 rows=1 loops=1)

32. 788.679 788.679 ↑ 153.0 1 87,631

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=87,631)

  • Index Cond: ((loan_id = l_1.lendify_loan_id) AND (as_of_date <= $8))
33.          

CTE spv_cashflow

34. 243.885 1,595.793 ↓ 2.0 318,907 1

Hash Semi Join (cost=1,482.00..26,469.25 rows=162,240 width=233) (actual time=54.567..1,595.793 rows=318,907 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_3.spv_entity_id)
35. 388.513 1,351.903 ↑ 9.9 555,214 1

Nested Loop (cost=1,481.97..10,184.34 rows=5,516,175 width=233) (actual time=54.545..1,351.903 rows=555,214 loops=1)

36. 64.629 87.080 ↓ 438.2 87,631 1

HashAggregate (cost=1,481.54..1,483.54 rows=200 width=4) (actual time=54.518..87.080 rows=87,631 loops=1)

  • Group Key: aot_was_in_spv_2.asset_loan_id
37. 22.451 22.451 ↓ 1.3 87,631 1

CTE Scan on aot_was_in_spv aot_was_in_spv_2 (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=0.005..22.451 rows=87,631 loops=1)

38. 876.310 876.310 ↑ 6.0 6 87,631

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.004..0.010 rows=6 loops=87,631)

  • Index Cond: (loan_id = aot_was_in_spv_2.asset_loan_id)
39. 0.003 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: 1024 Batches: 1 Memory Usage: 9kB
40. 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)

41.          

CTE pmts

42. 216.878 7,329.629 ↓ 276.4 55,285 1

HashAggregate (cost=465,106.43..465,115.43 rows=200 width=144) (actual time=7,229.300..7,329.629 rows=55,285 loops=1)

  • Group Key: cf_1.loan_id
43.          

Initplan (for HashAggregate)

44. 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)

45. 0.004 0.004 ↑ 1.0 1 1

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

46. 234.495 7,112.743 ↓ 70.8 57,458 1

Nested Loop (cost=29,486.73..465,090.17 rows=811 width=144) (actual time=6,707.192..7,112.743 rows=57,458 loops=1)

47. 2,476.005 6,240.434 ↓ 2.0 318,907 1

Merge Join (cost=29,486.30..365,338.57 rows=162,240 width=152) (actual time=4,802.127..6,240.434 rows=318,907 loops=1)

  • Merge Cond: (ler.asset_loan_cashflow_id = cf_1.id)
48. 2,960.923 2,960.923 ↑ 1.0 7,966,234 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.009..2,960.923 rows=7,966,234 loops=1)

49. 136.712 803.506 ↓ 2.0 318,907 1

Materialize (cost=29,485.86..30,297.06 rows=162,240 width=148) (actual time=468.318..803.506 rows=318,907 loops=1)

50. 505.453 666.794 ↓ 2.0 318,907 1

Sort (cost=29,485.86..29,891.46 rows=162,240 width=148) (actual time=468.314..666.794 rows=318,907 loops=1)

  • Sort Key: cf_1.id
  • Sort Method: external merge Disk: 16128kB
51. 161.341 161.341 ↓ 2.0 318,907 1

CTE Scan on spv_cashflow cf_1 (cost=0.00..3,244.80 rows=162,240 width=148) (actual time=0.007..161.341 rows=318,907 loops=1)

52. 637.814 637.814 ↓ 0.0 0 318,907

Index Scan using loan_event_pkey on loan_event le (cost=0.43..0.60 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=318,907)

  • Index Cond: (id = ler.lendify_loan_event_id)
  • Filter: (((posting_date)::date >= $14) AND ((posting_date)::date <= $15))
  • Rows Removed by Filter: 1
53.          

CTE whcf

54. 3,464.229 15,969.051 ↓ 7.0 72,634 1

GroupAggregate (cost=759,966.35..764,636.00 rows=10,377 width=158) (actual time=12,315.483..15,969.051 rows=72,634 loops=1)

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

Initplan (for GroupAggregate)

56. 0.001 0.001 ↑ 1.0 1 1

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

57. 0.002 0.002 ↑ 1.0 1 1

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

58. 0.002 0.002 ↑ 1.0 1 1

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

59. 0.001 0.001 ↑ 1.0 1 1

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

60. 0.000 0.000 ↑ 1.0 1 1

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

61. 0.001 0.001 ↑ 1.0 1 1

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

62. 0.000 0.000 ↑ 1.0 1 1

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

63. 0.001 0.001 ↑ 1.0 1 1

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

64. 0.000 0.000 ↑ 1.0 1 1

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

65. 0.000 0.000 ↑ 1.0 1 1

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

66. 0.001 0.001 ↑ 1.0 1 1

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

67. 0.001 0.001 ↑ 1.0 1 1

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

68. 0.000 0.000 ↑ 1.0 1 1

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

69. 0.001 0.001 ↑ 1.0 1 1

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

70. 0.001 0.001 ↑ 1.0 1 1

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

71. 0.000 0.000 ↑ 1.0 1 1

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

72. 0.002 0.002 ↑ 1.0 1 1

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

73. 0.001 0.001 ↑ 1.0 1 1

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

74. 0.000 0.000 ↑ 1.0 1 1

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

75. 0.000 0.000 ↑ 1.0 1 1

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

76. 0.002 0.002 ↑ 1.0 1 1

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

77. 0.000 0.000 ↑ 1.0 1 1

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

78. 0.002 0.002 ↑ 1.0 1 1

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

79. 0.000 0.000 ↑ 1.0 1 1

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

80. 0.001 0.001 ↑ 1.0 1 1

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

81. 0.002 0.002 ↑ 1.0 1 1

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

82. 0.000 0.000 ↑ 1.0 1 1

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

83. 0.000 0.000 ↑ 1.0 1 1

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

84. 0.001 0.001 ↑ 1.0 1 1

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

85. 0.000 0.000 ↑ 1.0 1 1

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

86. 0.001 0.001 ↑ 1.0 1 1

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

87. 0.001 0.001 ↑ 1.0 1 1

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

88. 0.001 0.001 ↑ 1.0 1 1

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

89. 0.000 0.000 ↑ 1.0 1 1

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

90. 0.001 0.001 ↑ 1.0 1 1

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

91. 0.002 0.002 ↑ 1.0 1 1

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

92. 0.001 0.001 ↑ 1.0 1 1

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

93. 0.001 0.001 ↑ 1.0 1 1

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

94. 0.002 0.002 ↑ 1.0 1 1

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

95. 0.001 0.001 ↑ 1.0 1 1

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

96. 0.000 0.000 ↑ 1.0 1 1

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

97. 0.001 0.001 ↑ 1.0 1 1

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

98. 0.000 0.000 ↑ 1.0 1 1

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

99. 0.002 0.002 ↑ 1.0 1 1

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

100. 0.001 0.001 ↑ 1.0 1 1

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

101. 0.001 0.001 ↑ 1.0 1 1

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

102. 0.001 0.001 ↑ 1.0 1 1

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

103. 0.001 0.001 ↑ 1.0 1 1

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

104. 0.002 0.002 ↑ 1.0 1 1

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

105. 0.000 0.000 ↑ 1.0 1 1

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

106. 0.000 0.000 ↑ 1.0 1 1

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

107. 0.001 0.001 ↑ 1.0 1 1

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

108. 0.002 0.002 ↑ 1.0 1 1

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

109. 0.003 0.003 ↑ 1.0 1 1

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

110. 0.002 0.002 ↑ 1.0 1 1

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

111. 0.002 0.002 ↑ 1.0 1 1

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

112. 476.271 12,504.769 ↓ 27.7 287,153 1

Sort (cost=759,965.23..759,991.18 rows=10,377 width=158) (actual time=12,315.217..12,504.769 rows=287,153 loops=1)

  • Sort Key: cf_2.loan_id, cf_2.recipient_entity_id
  • Sort Method: external merge Disk: 16648kB
113. 631.065 12,028.498 ↓ 27.7 287,153 1

Merge Join (cost=754,967.00..759,273.03 rows=10,377 width=158) (actual time=11,354.246..12,028.498 rows=287,153 loops=1)

  • Merge Cond: (lcri.loan_cashflow_id = cf_2.id)
114. 2,151.750 8,658.736 ↓ 3.0 1,803,039 1

Sort (cost=724,924.63..726,417.37 rows=597,093 width=13) (actual time=8,020.153..8,658.736 rows=1,803,039 loops=1)

  • Sort Key: lcri.loan_cashflow_id
  • Sort Method: external merge Disk: 45792kB
115. 986.825 6,506.986 ↓ 3.0 1,803,039 1

Hash Join (cost=3,555.19..657,434.74 rows=597,093 width=13) (actual time=67.523..6,506.986 rows=1,803,039 loops=1)

  • Hash Cond: (lcri.loan_cashflow_report_id = lcr.id)
116. 5,460.195 5,460.195 ↓ 1.0 1,818,102 1

Seq Scan on loan_cashflow_report_item lcri (cost=0.00..641,165.85 rows=1,798,071 width=13) (actual time=7.503..5,460.195 rows=1,818,102 loops=1)

  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 19727845
117. 23.073 59.966 ↓ 3.0 69,929 1

Hash (cost=3,265.14..3,265.14 rows=23,204 width=8) (actual time=59.966..59.966 rows=69,929 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3756kB
118. 36.893 36.893 ↓ 3.0 69,929 1

Seq Scan on loan_cashflow_report lcr (cost=0.00..3,265.14 rows=23,204 width=8) (actual time=0.015..36.893 rows=69,929 loops=1)

  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= $73))
  • Rows Removed by Filter: 360
119. 127.965 2,738.697 ↓ 1.9 304,790 1

Materialize (cost=30,042.36..30,853.56 rows=162,240 width=153) (actual time=2,447.046..2,738.697 rows=304,790 loops=1)

120. 529.401 2,610.732 ↓ 1.9 304,790 1

Sort (cost=30,042.36..30,447.96 rows=162,240 width=153) (actual time=2,447.042..2,610.732 rows=304,790 loops=1)

  • Sort Key: cf_2.id
  • Sort Method: external merge Disk: 17368kB
121. 2,081.331 2,081.331 ↓ 2.0 318,907 1

CTE Scan on spv_cashflow cf_2 (cost=0.00..3,244.80 rows=162,240 width=153) (actual time=54.573..2,081.331 rows=318,907 loops=1)

122.          

CTE whcf_adj

123. 27.030 130.477 ↓ 19.0 1,045 1

HashAggregate (cost=5,460.96..5,465.91 rows=55 width=153) (actual time=128.402..130.477 rows=1,045 loops=1)

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

Initplan (for HashAggregate)

125. 0.002 0.002 ↑ 1.0 1 1

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

126. 0.002 0.002 ↑ 1.0 1 1

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

127. 0.002 0.002 ↑ 1.0 1 1

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

128. 0.000 0.000 ↑ 1.0 1 1

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

129. 0.000 0.000 ↑ 1.0 1 1

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

130. 0.002 0.002 ↑ 1.0 1 1

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

131. 0.001 0.001 ↑ 1.0 1 1

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

132. 0.001 0.001 ↑ 1.0 1 1

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

133. 0.000 0.000 ↑ 1.0 1 1

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

134. 0.002 0.002 ↑ 1.0 1 1

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

135. 0.002 0.002 ↑ 1.0 1 1

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

136. 0.001 0.001 ↑ 1.0 1 1

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

137. 0.000 0.000 ↑ 1.0 1 1

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

138. 0.000 0.000 ↑ 1.0 1 1

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

139. 0.001 0.001 ↑ 1.0 1 1

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

140. 0.002 0.002 ↑ 1.0 1 1

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

141. 0.001 0.001 ↑ 1.0 1 1

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

142. 0.001 0.001 ↑ 1.0 1 1

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

143. 0.001 0.001 ↑ 1.0 1 1

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

144. 0.000 0.000 ↑ 1.0 1 1

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

145. 0.001 0.001 ↑ 1.0 1 1

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

146. 0.001 0.001 ↑ 1.0 1 1

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

147. 0.002 0.002 ↑ 1.0 1 1

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

148. 0.001 0.001 ↑ 1.0 1 1

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

149. 0.001 0.001 ↑ 1.0 1 1

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

150. 0.001 0.001 ↑ 1.0 1 1

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

151. 0.001 0.001 ↑ 1.0 1 1

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

152. 0.002 0.002 ↑ 1.0 1 1

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

153. 0.000 0.000 ↑ 1.0 1 1

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

154. 0.001 0.001 ↑ 1.0 1 1

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

155. 0.001 0.001 ↑ 1.0 1 1

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

156. 0.001 0.001 ↑ 1.0 1 1

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

157. 0.001 0.001 ↑ 1.0 1 1

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

158. 0.000 0.000 ↑ 1.0 1 1

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

159. 0.000 0.000 ↑ 1.0 1 1

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

160. 0.001 0.001 ↑ 1.0 1 1

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

161. 0.001 0.001 ↑ 1.0 1 1

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

162. 0.001 0.001 ↑ 1.0 1 1

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

163. 0.001 0.001 ↑ 1.0 1 1

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

164. 0.001 0.001 ↑ 1.0 1 1

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

165. 0.000 0.000 ↑ 1.0 1 1

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

166. 0.002 0.002 ↑ 1.0 1 1

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

167. 0.001 0.001 ↑ 1.0 1 1

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

168. 0.001 0.001 ↑ 1.0 1 1

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

169. 0.002 0.002 ↑ 1.0 1 1

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

170. 0.002 0.002 ↑ 1.0 1 1

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

171. 0.002 0.002 ↑ 1.0 1 1

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

172. 0.002 0.002 ↑ 1.0 1 1

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

173. 0.002 0.002 ↑ 1.0 1 1

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

174. 2.285 103.393 ↓ 6.9 1,855 1

Nested Loop Anti Join (cost=0.44..5,376.96 rows=270 width=153) (actual time=0.092..103.393 rows=1,855 loops=1)

175. 95.543 95.543 ↓ 3.4 1,855 1

CTE Scan on spv_cashflow cf_3 (cost=0.00..4,056.00 rows=541 width=157) (actual time=0.079..95.543 rows=1,855 loops=1)

  • Filter: ((cashflow_date <= $123) AND ((cashflow_type)::text = ANY ('{CREDIT,CREDIT_REVERSAL}'::text[])))
  • Rows Removed by Filter: 317052
176. 5.565 5.565 ↓ 0.0 0 1,855

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) (actual time=0.003..0.003 rows=0 loops=1,855)

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

CTE bb_data

178. 6,699.347 70,538.834 ↑ 1.0 87,631 1

Sort (cost=9,648,112.87..9,648,337.30 rows=89,772 width=3,236) (actual time=68,700.650..70,538.834 rows=87,631 loops=1)

  • Sort Key: (CASE WHEN (hashed SubPlan 204) THEN 0 ELSE 1 END), (CASE WHEN (hashed SubPlan 205) THEN aot_1.ownership_start_date ELSE ln.origination_date END), ln.origination_date, ln.external_id
  • Sort Method: external merge Disk: 72800kB
179.          

Initplan (for Sort)

180. 0.002 0.002 ↑ 1.0 1 1

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

181. 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)

182. 0.002 0.002 ↑ 1.0 1 1

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

183. 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)

184. 0.002 0.002 ↑ 1.0 1 1

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

185. 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)

186. 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.001..0.001 rows=1 loops=1)

187. 0.002 0.002 ↑ 1.0 1 1

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

188. 0.001 0.001 ↑ 1.0 1 1

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

189. 0.000 0.000 ↑ 1.0 1 1

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

190. 0.001 0.001 ↑ 1.0 1 1

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

191. 0.000 0.000 ↑ 1.0 1 1

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

192. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_124 (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_125 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

194. 0.002 0.002 ↑ 1.0 1 1

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

195. 0.000 0.000 ↑ 1.0 1 1

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

196. 0.002 0.002 ↑ 1.0 1 1

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

197. 0.000 0.000 ↑ 1.0 1 1

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

198. 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)

199. 0.002 0.002 ↑ 1.0 1 1

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

200. 0.002 0.002 ↑ 1.0 1 1

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

201. 0.001 0.001 ↑ 1.0 1 1

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

202. 0.000 0.000 ↑ 1.0 1 1

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

203. 0.000 0.000 ↓ 0.0 0

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

204. 0.000 0.000 ↓ 0.0 0

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

205. 0.000 0.000 ↓ 0.0 0

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

206. 0.002 0.002 ↑ 1.0 1 1

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

207. 0.000 0.000 ↑ 1.0 1 1

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

208. 0.002 0.002 ↑ 1.0 1 1

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

209. 0.000 0.000 ↓ 0.0 0

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

210. 0.000 0.000 ↓ 0.0 0

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

211. 0.000 0.000 ↓ 0.0 0

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

212. 0.001 0.001 ↑ 1.0 1 1

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

213. 0.001 0.001 ↑ 1.0 1 1

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

214. 0.001 0.001 ↑ 1.0 1 1

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

215. 0.000 0.000 ↑ 1.0 1 1

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

216. 0.000 0.000 ↓ 0.0 0

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

217. 0.000 0.000 ↓ 0.0 0

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

218. 0.002 0.002 ↑ 1.0 1 1

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

219. 0.001 0.001 ↑ 1.0 1 1

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

220. 0.001 0.001 ↑ 1.0 1 1

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

221. 0.001 0.001 ↑ 1.0 1 1

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

222. 0.001 0.001 ↑ 1.0 1 1

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

223. 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)

224. 0.001 0.001 ↑ 1.0 1 1

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

225. 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)

226. 0.001 0.001 ↑ 1.0 1 1

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

227. 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)

228. 0.000 0.000 ↓ 0.0 0

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

229. 0.000 0.000 ↓ 0.0 0

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

230. 0.001 0.001 ↑ 1.0 1 1

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

231. 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)

232. 0.000 0.000 ↓ 0.0 0

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

233. 0.000 0.000 ↓ 0.0 0

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

234. 0.001 0.001 ↑ 1.0 1 1

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

235. 0.000 0.000 ↓ 0.0 0

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

236. 0.000 0.000 ↓ 0.0 0

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

237. 0.000 0.000 ↑ 1.0 1 1

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

238. 0.000 0.000 ↓ 0.0 0

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

239. 0.000 0.000 ↓ 0.0 0

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

240. 0.001 0.001 ↑ 1.0 1 1

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

241. 0.000 0.000 ↓ 0.0 0

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

242. 0.000 0.000 ↓ 0.0 0

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

243. 0.000 0.000 ↑ 1.0 1 1

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

244. 0.000 0.000 ↓ 0.0 0

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

245. 0.000 0.000 ↓ 0.0 0

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

246. 0.001 0.001 ↑ 1.0 1 1

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

247. 0.000 0.000 ↓ 0.0 0

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

248. 0.000 0.000 ↓ 0.0 0

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

249. 0.000 0.000 ↓ 0.0 0

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

250. 0.000 0.000 ↓ 0.0 0

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

251. 0.000 0.000 ↓ 0.0 0

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

252. 0.000 0.000 ↓ 0.0 0

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

253. 0.000 0.000 ↓ 0.0 0

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

254. 0.000 0.000 ↓ 0.0 0

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

255. 0.000 0.000 ↓ 0.0 0

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

256. 0.000 0.000 ↓ 0.0 0

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

257. 0.000 0.000 ↓ 0.0 0

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

258. 0.002 0.002 ↑ 1.0 1 1

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

259. 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)

260. 0.002 0.002 ↑ 1.0 1 1

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

261. 0.004 0.004 ↑ 1.0 1 1

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

262. 0.000 0.000 ↓ 0.0 0

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

263. 0.000 0.000 ↓ 0.0 0

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

264. 2,669.380 63,839.428 ↑ 1.0 87,631 1

Hash Left Join (cost=2,005,681.42..9,390,342.66 rows=89,772 width=3,236) (actual time=51,400.448..63,839.428 rows=87,631 loops=1)

  • Hash Cond: (ln.id = pmts.loan_id)
265. 437.942 53,758.790 ↑ 1.0 87,631 1

Hash Right Join (cost=2,005,674.69..9,318,630.22 rows=89,772 width=3,012) (actual time=43,988.834..53,758.790 rows=87,631 loops=1)

  • Hash Cond: (ln_1.id = ln.id)
  • Filter: ((hashed SubPlan 210) OR (COALESCE(aot_1.ownership_end_date, '1900-01-01'::date) >= (date_trunc('month'::text, ($219)::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(lbs_sale.principal_balance, 2), 0.00)) <> round(lbs.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(lbs_sale.origination_fee_balance, 2), 0.00)) <> round(lbs.origination_fee_balance, 2)) OR (COALESCE((GREATEST(whcf.max_cash_transfer_date, whcf_adj.max_adj_transfer_date)), '1900-01-01'::date) >= $220))
266. 308.702 28,824.135 ↓ 3.4 87,631 1

Hash Semi Join (cost=737,969.29..8,028,072.66 rows=25,522 width=2,326) (actual time=14,913.293..28,824.135 rows=87,631 loops=1)

  • Hash Cond: (aot_2.to_entity_id = constant_208.spv_entity_id)
267. 3,155.787 28,515.427 ↑ 1.0 889,817 1

Hash Left Join (cost=737,969.26..8,025,376.88 rows=918,788 width=2,330) (actual time=14,913.263..28,515.427 rows=889,817 loops=1)

  • Hash Cond: ((aot_2.to_entity_id = whcf_adj.recipient_entity_id) AND (ln_1.id = whcf_adj.loan_id))
268. 605.383 25,226.906 ↑ 1.0 889,817 1

Hash Left Join (cost=737,967.33..8,018,484.02 rows=918,788 width=1,328) (actual time=14,780.458..25,226.906 rows=889,817 loops=1)

  • Hash Cond: (ln_1.asset_id = aot_sale.asset_id)
269. 1,124.962 24,621.487 ↑ 1.0 889,817 1

Nested Loop (cost=174,879.73..7,416,440.23 rows=918,788 width=1,318) (actual time=14,780.172..24,621.487 rows=889,817 loops=1)

270. 1,336.313 19,937.257 ↑ 1.0 889,817 1

Hash Right Join (cost=174,879.16..183,597.34 rows=919,094 width=1,312) (actual time=14,780.145..19,937.257 rows=889,817 loops=1)

  • Hash Cond: ((whcf.recipient_entity_id = aot_2.to_entity_id) AND (whcf.loan_id = ln_1.id))
271. 16,138.039 16,138.039 ↓ 7.0 72,634 1

CTE Scan on whcf (cost=0.00..207.54 rows=10,377 width=1,168) (actual time=12,315.491..16,138.039 rows=72,634 loops=1)

272. 355.702 2,462.905 ↑ 1.0 889,817 1

Hash (cost=155,706.75..155,706.75 rows=919,094 width=24) (actual time=2,462.905..2,462.905 rows=889,817 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3362kB
273. 803.858 2,107.203 ↑ 1.0 889,817 1

Hash Join (cost=59,562.39..155,706.75 rows=919,094 width=24) (actual time=654.581..2,107.203 rows=889,817 loops=1)

  • Hash Cond: (aot_2.asset_id = ln_1.asset_id)
274. 679.191 817.858 ↑ 1.0 905,912 1

Bitmap Heap Scan on asset_ownership_transfer aot_2 (cost=19,136.14..90,829.54 rows=934,672 width=16) (actual time=143.549..817.858 rows=905,912 loops=1)

  • Recheck Cond: (from_entity_id = 130)
  • Rows Removed by Index Recheck: 869001
  • Heap Blocks: exact=28637 lossy=26440
275. 138.667 138.667 ↓ 1.1 1,000,639 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_from_entity_id (cost=0.00..18,902.47 rows=934,672 width=0) (actual time=138.667..138.667 rows=1,000,639 loops=1)

  • Index Cond: (from_entity_id = 130)
276. 185.260 485.487 ↓ 1.0 538,938 1

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

  • Buckets: 131072 Batches: 16 Memory Usage: 2359kB
277. 300.227 300.227 ↓ 1.0 538,938 1

Seq Scan on loan ln_1 (cost=0.00..31,076.10 rows=537,852 width=12) (actual time=0.008..300.227 rows=538,938 loops=1)

  • Filter: (originator_id = 1003)
  • Rows Removed by Filter: 8825
278. 3,559.268 3,559.268 ↑ 1.0 1 889,817

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs_1 (cost=0.57..7.86 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=889,817)

  • Index Cond: (id = aot_2.loan_balance_snapshot_id)
279. 0.000 0.036 ↓ 0.0 0 1

Hash (cost=562,301.65..562,301.65 rows=62,876 width=18) (actual time=0.036..0.036 rows=0 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 512kB
280. 0.001 0.036 ↓ 0.0 0 1

Nested Loop (cost=3,876.24..562,301.65 rows=62,876 width=18) (actual time=0.036..0.036 rows=0 loops=1)

281. 0.005 0.035 ↓ 0.0 0 1

Nested Loop (cost=3,875.67..67,377.64 rows=62,897 width=12) (actual time=0.035..0.035 rows=0 loops=1)

282. 0.006 0.007 ↑ 1.0 1 1

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

  • Group Key: constant_207.spv_entity_id
283. 0.001 0.001 ↑ 1.0 1 1

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

284. 0.002 0.023 ↓ 0.0 0 1

Bitmap Heap Scan on asset_ownership_transfer aot_sale (cost=3,875.65..66,748.64 rows=62,897 width=16) (actual time=0.023..0.023 rows=0 loops=1)

  • Recheck Cond: (from_entity_id = constant_207.spv_entity_id)
  • Filter: (to_entity_id = 130)
285. 0.021 0.021 ↓ 0.0 0 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_from_entity_id (cost=0.00..3,859.92 rows=190,866 width=0) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (from_entity_id = constant_207.spv_entity_id)
286. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs_sale (cost=0.57..7.86 rows=1 width=14) (never executed)

  • Index Cond: (id = aot_sale.loan_balance_snapshot_id)
287. 0.731 132.734 ↓ 19.0 1,045 1

Hash (cost=1.10..1.10 rows=55 width=1,036) (actual time=132.734..132.734 rows=1,045 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 125kB
288. 132.003 132.003 ↓ 19.0 1,045 1

CTE Scan on whcf_adj (cost=0.00..1.10 rows=55 width=1,036) (actual time=128.407..132.003 rows=1,045 loops=1)

289. 0.002 0.006 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
290. 0.004 0.004 ↑ 1.0 1 1

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

291. 141.068 24,496.711 ↑ 1.0 87,631 1

Hash (cost=1,258,692.23..1,258,692.23 rows=89,772 width=690) (actual time=24,496.711..24,496.711 rows=87,631 loops=1)

  • Buckets: 8192 Batches: 32 Memory Usage: 1263kB
292. 166.329 24,355.643 ↑ 1.0 87,631 1

Hash Right Join (cost=1,229,131.70..1,258,692.23 rows=89,772 width=690) (actual time=24,146.399..24,355.643 rows=87,631 loops=1)

  • Hash Cond: (ln_end.asset_loan_id = ln.id)
293. 42.972 42.972 ↑ 3.1 87,631 1

CTE Scan on loan_data_end ln_end (cost=0.00..5,389.68 rows=269,484 width=240) (actual time=0.023..42.972 rows=87,631 loops=1)

294. 117.091 24,146.342 ↑ 1.0 87,631 1

Hash (cost=1,222,661.55..1,222,661.55 rows=89,772 width=458) (actual time=24,146.342..24,146.342 rows=87,631 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 2106kB
295. 247.152 24,029.251 ↑ 1.0 87,631 1

Hash Left Join (cost=1,155,702.73..1,222,661.55 rows=89,772 width=458) (actual time=21,821.103..24,029.251 rows=87,631 loops=1)

  • Hash Cond: (ln.id = lsr.loan_id)
296. 489.432 23,293.156 ↑ 1.0 87,631 1

Hash Join (cost=1,128,500.30..1,180,045.46 rows=89,772 width=426) (actual time=21,275.345..23,293.156 rows=87,631 loops=1)

  • Hash Cond: (ln.id = aot_was_in_spv_4.asset_loan_id)
297. 735.110 22,247.684 ↓ 3.0 538,938 1

Hash Left Join (cost=1,127,014.27..1,177,885.47 rows=179,544 width=426) (actual time=20,329.537..22,247.684 rows=538,938 loops=1)

  • Hash Cond: (ln.asset_id = aot_1.asset_id)
298. 1,094.361 21,090.831 ↓ 3.0 538,938 1

Hash Right Join (cost=851,619.30..896,119.44 rows=179,544 width=408) (actual time=19,710.017..21,090.831 rows=538,938 loops=1)

  • Hash Cond: (borrower.loan_id = nt.loan_id)
299. 286.912 286.912 ↑ 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.019..286.912 rows=538,987 loops=1)

300. 510.148 19,709.558 ↓ 3.0 538,938 1

Hash (cost=840,958.00..840,958.00 rows=179,544 width=354) (actual time=19,709.558..19,709.558 rows=538,938 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 64 (originally 32) Memory Usage: 3969kB
301. 620.525 19,199.410 ↓ 3.0 538,938 1

Hash Right Join (cost=816,129.16..840,958.00 rows=179,544 width=354) (actual time=17,307.733..19,199.410 rows=538,938 loops=1)

  • Hash Cond: (ln_start.asset_loan_id = ln.id)
302. 1,324.911 1,324.911 ↑ 3.2 84,728 1

CTE Scan on loan_data_start ln_start (cost=0.00..5,389.68 rows=269,484 width=164) (actual time=53.732..1,324.911 rows=84,728 loops=1)

303. 505.353 17,253.974 ↓ 3.0 538,938 1

Hash (cost=808,974.86..808,974.86 rows=179,544 width=198) (actual time=17,253.974..17,253.974 rows=538,938 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 64 (originally 16) Memory Usage: 3841kB
304. 855.860 16,748.621 ↓ 3.0 538,938 1

Hash Right Join (cost=780,629.47..808,974.86 rows=179,544 width=198) (actual time=13,521.599..16,748.621 rows=538,938 loops=1)

  • Hash Cond: (lp.loan_id = nt.loan_id)
305. 712.928 3,052.987 ↓ 1.0 538,987 1

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

  • Hash Cond: (loan_data_end.loan_id = lp.loan_id)
306. 1,714.722 1,714.722 ↑ 3.1 87,631 1

CTE Scan on loan_data_end (cost=0.00..5,389.68 rows=269,484 width=16) (actual time=54.206..1,714.722 rows=87,631 loops=1)

307. 188.347 625.337 ↓ 1.0 538,987 1

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

  • Buckets: 131072 Batches: 16 Memory Usage: 2605kB
308. 436.990 436.990 ↓ 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.011..436.990 rows=538,987 loops=1)

309. 485.274 12,839.774 ↓ 3.0 538,938 1

Hash (cost=719,812.95..719,812.95 rows=179,544 width=194) (actual time=12,839.774..12,839.774 rows=538,938 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 64 (originally 16) Memory Usage: 3841kB
310. 430.622 12,354.500 ↓ 3.0 538,938 1

Hash Right Join (cost=712,781.96..719,812.95 rows=179,544 width=194) (actual time=11,901.214..12,354.500 rows=538,938 loops=1)

  • Hash Cond: (aot_was_in_spv_3.asset_loan_id = ln.id)
311. 22.722 22.722 ↓ 1.3 87,631 1

CTE Scan on aot_was_in_spv aot_was_in_spv_3 (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=0.024..22.722 rows=87,631 loops=1)

312. 486.833 11,901.156 ↓ 3.0 538,938 1

Hash (cost=705,802.66..705,802.66 rows=179,544 width=190) (actual time=11,901.156..11,901.156 rows=538,938 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 32 (originally 16) Memory Usage: 4170kB
313. 939.228 11,414.323 ↓ 3.0 538,938 1

Hash Left Join (cost=301,075.03..705,802.66 rows=179,544 width=190) (actual time=5,215.418..11,414.323 rows=538,938 loops=1)

  • Hash Cond: (ln.id = loan.id)
314. 323.928 9,922.595 ↓ 3.0 538,938 1

Hash Left Join (cost=261,174.78..651,892.60 rows=179,544 width=189) (actual time=4,655.179..9,922.595 rows=538,938 loops=1)

  • Hash Cond: (aot_curr_owner.to_entity_id = le_1.id)
315. 311.486 9,598.549 ↓ 3.0 538,938 1

Hash Left Join (cost=261,168.07..649,417.16 rows=179,544 width=156) (actual time=4,655.053..9,598.549 rows=538,938 loops=1)

  • Hash Cond: (nt.tenant_id = lt.id)
316. 808.026 9,286.887 ↓ 3.0 538,938 1

Hash Right Join (cost=261,158.28..646,938.64 rows=179,544 width=159) (actual time=4,654.868..9,286.887 rows=538,938 loops=1)

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

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

  • Hash Cond: ((de.tenant_id = loc.tenant_id) AND (de.location_id = loc.location_id))
318. 3,475.930 3,475.930 ↑ 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.060..3,475.930 rows=538,987 loops=1)

  • Filter: (transaction_type = 'DISBURSEMENT'::text)
  • Rows Removed by Filter: 8984889
319. 0.660 1.498 ↓ 1.0 1,765 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 144kB
320. 0.838 0.838 ↓ 1.0 1,765 1

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

321. 390.329 4,647.597 ↓ 3.0 538,938 1

Hash (cost=255,146.88..255,146.88 rows=179,544 width=138) (actual time=4,647.597..4,647.597 rows=538,938 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 32 (originally 16) Memory Usage: 3841kB
322. 746.193 4,257.268 ↓ 3.0 538,938 1

Hash Join (cost=194,018.25..255,146.88 rows=179,544 width=138) (actual time=2,948.034..4,257.268 rows=538,938 loops=1)

  • Hash Cond: (nt.loan_number = (ln.external_id)::text)
323. 567.450 567.450 ↓ 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.023..567.450 rows=538,987 loops=1)

324. 322.201 2,943.625 ↓ 3.0 547,763 1

Hash (cost=189,246.21..189,246.21 rows=182,323 width=86) (actual time=2,943.625..2,943.625 rows=547,763 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 32 (originally 8) Memory Usage: 3841kB
325. 631.944 2,621.424 ↓ 3.0 547,763 1

Hash Right Join (cost=35,670.14..189,246.21 rows=182,323 width=86) (actual time=712.523..2,621.424 rows=547,763 loops=1)

  • Hash Cond: (aot_curr_owner.asset_id = ln.asset_id)
326. 1,282.653 1,282.653 ↓ 1.7 547,763 1

Seq Scan on asset_ownership_transfer aot_curr_owner (cost=0.00..145,899.79 rows=318,110 width=12) (actual time=0.034..1,282.653 rows=547,763 loops=1)

  • Filter: ((($214 + 1) >= ownership_start_date) AND (($215 + 1) <= COALESCE((ownership_end_date - 1), (timezone('PST8PDT'::text, now()))::date)))
  • Rows Removed by Filter: 2265752
327. 303.976 706.827 ↓ 3.0 547,763 1

Hash (cost=31,076.10..31,076.10 rows=182,323 width=78) (actual time=706.827..706.827 rows=547,763 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 8) Memory Usage: 3585kB
328. 402.851 402.851 ↓ 3.0 547,763 1

Seq Scan on loan ln (cost=0.00..31,076.10 rows=182,323 width=78) (actual time=2.239..402.851 rows=547,763 loops=1)

  • Filter: (origination_date <= $217)
329. 0.070 0.176 ↓ 1.1 230 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
330. 0.106 0.106 ↓ 1.1 230 1

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

331. 0.047 0.118 ↑ 1.1 155 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
332. 0.071 0.071 ↑ 1.1 155 1

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

333. 205.550 552.500 ↓ 1.0 538,938 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3457kB
334. 346.950 346.950 ↓ 1.0 538,938 1

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

  • Filter: (originator_id = 1003)
  • Rows Removed by Filter: 8825
335. 41.645 421.743 ↓ 3.3 87,631 1

Hash (cost=275,063.61..275,063.61 rows=26,509 width=26) (actual time=421.743..421.743 rows=87,631 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3469kB
336. 111.588 380.098 ↓ 3.3 87,631 1

Nested Loop Left Join (cost=1,620.11..275,063.61 rows=26,509 width=26) (actual time=5.938..380.098 rows=87,631 loops=1)

337. 41.150 93.248 ↓ 3.3 87,631 1

Nested Loop (cost=1,619.54..66,493.12 rows=26,509 width=20) (actual time=5.923..93.248 rows=87,631 loops=1)

338. 0.007 0.009 ↑ 1.0 1 1

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

  • Group Key: constant_206.spv_entity_id
339. 0.002 0.002 ↑ 1.0 1 1

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

340. 46.627 52.089 ↓ 3.3 87,631 1

Bitmap Heap Scan on asset_ownership_transfer aot_1 (cost=1,619.52..66,227.99 rows=26,509 width=24) (actual time=5.908..52.089 rows=87,631 loops=1)

  • Recheck Cond: (to_entity_id = constant_206.spv_entity_id)
  • Filter: (ownership_start_date <= ($216 + 1))
  • Heap Blocks: exact=3341
341. 5.462 5.462 ↓ 1.1 87,631 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=5.462..5.462 rows=87,631 loops=1)

  • Index Cond: (to_entity_id = constant_206.spv_entity_id)
342. 175.262 175.262 ↑ 1.0 1 87,631

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs (cost=0.57..7.86 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=87,631)

  • Index Cond: (aot_1.loan_balance_snapshot_id = id)
343. 27.351 556.040 ↓ 438.2 87,631 1

Hash (cost=1,483.54..1,483.54 rows=200 width=4) (actual time=556.040..556.040 rows=87,631 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3073kB
344. 62.191 528.689 ↓ 438.2 87,631 1

HashAggregate (cost=1,481.54..1,483.54 rows=200 width=4) (actual time=506.241..528.689 rows=87,631 loops=1)

  • Group Key: aot_was_in_spv_4.asset_loan_id
345. 466.498 466.498 ↓ 1.3 87,631 1

CTE Scan on aot_was_in_spv aot_was_in_spv_4 (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=377.016..466.498 rows=87,631 loops=1)

346. 227.376 488.943 ↓ 1.0 547,763 1

Hash (cost=16,285.97..16,285.97 rows=537,397 width=36) (actual time=488.943..488.943 rows=547,763 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2912kB
347. 261.567 261.567 ↓ 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.013..261.567 rows=547,763 loops=1)

348.          

SubPlan (for Hash Right Join)

349. 0.002 0.002 ↑ 1.0 1 1

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

350. 28.776 7,411.247 ↓ 276.4 55,285 1

Hash (cost=4.00..4.00 rows=200 width=228) (actual time=7,411.247..7,411.247 rows=55,285 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
351. 7,382.471 7,382.471 ↓ 276.4 55,285 1

CTE Scan on pmts (cost=0.00..4.00 rows=200 width=228) (actual time=7,229.303..7,382.471 rows=55,285 loops=1)

352.          

SubPlan (for Hash Left Join)

353. 0.002 0.002 ↑ 1.0 1 1

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

354. 0.001 0.001 ↑ 1.0 1 1

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

355. 0.001 0.001 ↑ 1.0 1 1

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

356. 0.001 0.001 ↑ 1.0 1 1

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

357. 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)

358. 0.001 0.001 ↑ 1.0 1 1

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

359. 0.002 0.002 ↑ 1.0 1 1

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

360. 0.000 0.000 ↑ 1.0 1 1

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

361. 0.002 0.002 ↑ 1.0 1 1

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

362. 0.001 0.001 ↑ 1.0 1 1

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

Planning time : 19.640 ms
Execution time : 70,890.607 ms