explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kcdt

Settings
# exclusive inclusive rows x rows loops node
1. 88,278.059 88,278.059 ↑ 6.0 71,183 1

CTE Scan on bb_data (cost=7,413,612.58..7,422,127.30 rows=425,736 width=5,003) (actual time=87,809.992..88,278.059 rows=71,183 loops=1)

2.          

CTE aot_was_in_spv

3. 54.990 606.579 ↓ 1.2 71,183 1

HashAggregate (cost=102,506.94..103,097.13 rows=59,019 width=4) (actual time=582.948..606.579 rows=71,183 loops=1)

  • Group Key: lo.id
4. 100.419 551.589 ↓ 1.2 71,183 1

Hash Join (cost=39,851.30..102,359.39 rows=59,019 width=4) (actual time=419.690..551.589 rows=71,183 loops=1)

  • Hash Cond: (aot.asset_id = lo.asset_id)
5. 32.914 37.439 ↓ 1.2 71,183 1

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,209.83..60,402.54 rows=60,052 width=4) (actual time=4.898..37.439 rows=71,183 loops=1)

  • Recheck Cond: (to_entity_id = 158)
  • Heap Blocks: exact=2711
6. 4.525 4.525 ↓ 1.2 71,183 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,194.82 rows=60,052 width=0) (actual time=4.525..4.525 rows=71,183 loops=1)

  • Index Cond: (to_entity_id = 158)
7. 182.396 413.731 ↓ 1.0 519,937 1

Hash (cost=30,116.56..30,116.56 rows=519,592 width=8) (actual time=413.731..413.731 rows=519,937 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3569kB
8. 231.335 231.335 ↓ 1.0 519,937 1

Seq Scan on loan lo (cost=0.00..30,116.56 rows=519,592 width=8) (actual time=0.011..231.335 rows=519,937 loops=1)

  • Filter: (originator_id = 1003)
  • Rows Removed by Filter: 8825
9.          

CTE loan_data_start

10. 94.523 1,055.352 ↑ 3.8 68,012 1

Nested Loop (cost=1,328.92..884,301.02 rows=259,992 width=92) (actual time=44.280..1,055.352 rows=68,012 loops=1)

11. 103.813 391.365 ↑ 3.7 71,183 1

Nested Loop (cost=1,328.35..2,959.93 rows=259,992 width=16) (actual time=44.246..391.365 rows=71,183 loops=1)

12. 56.649 74.003 ↓ 355.9 71,183 1

HashAggregate (cost=1,327.93..1,329.93 rows=200 width=4) (actual time=44.223..74.003 rows=71,183 loops=1)

  • Group Key: aot_was_in_spv.asset_loan_id
13. 17.354 17.354 ↓ 1.2 71,183 1

CTE Scan on aot_was_in_spv (cost=0.00..1,180.38 rows=59,019 width=4) (actual time=0.000..17.354 rows=71,183 loops=1)

14. 213.549 213.549 ↑ 1.0 1 71,183

Index Scan using fki_loan_asset_registration_2_asset_loan on loan_asset_registration l (cost=0.42..8.14 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=71,183)

  • Index Cond: (asset_loan_id = aot_was_in_spv.asset_loan_id)
15. 71.183 569.464 ↑ 1.0 1 71,183

Limit (cost=0.57..3.37 rows=1 width=61) (actual time=0.008..0.008 rows=1 loops=71,183)

16. 498.281 498.281 ↑ 461.0 1 71,183

Index Scan using unc_lendify_loan_summary_loan_id_as_of_date_desc on loan_summary ls (cost=0.57..1,291.31 rows=461 width=61) (actual time=0.007..0.007 rows=1 loops=71,183)

  • Index Cond: ((loan_id = l.lendify_loan_id) AND (as_of_date < '2019-12-31'::date))
17.          

CTE loan_data_end

18. 68.654 1,635.105 ↑ 3.7 71,183 1

Nested Loop (cost=1,328.92..884,301.02 rows=259,992 width=143) (actual time=672.842..1,635.105 rows=71,183 loops=1)

19. 82.850 996.987 ↑ 3.7 71,183 1

Nested Loop (cost=1,328.35..2,959.93 rows=259,992 width=16) (actual time=672.808..996.987 rows=71,183 loops=1)

20. 56.326 700.588 ↓ 355.9 71,183 1

HashAggregate (cost=1,327.93..1,329.93 rows=200 width=4) (actual time=672.783..700.588 rows=71,183 loops=1)

  • Group Key: aot_was_in_spv_1.asset_loan_id
21. 644.262 644.262 ↓ 1.2 71,183 1

CTE Scan on aot_was_in_spv aot_was_in_spv_1 (cost=0.00..1,180.38 rows=59,019 width=4) (actual time=582.951..644.262 rows=71,183 loops=1)

22. 213.549 213.549 ↑ 1.0 1 71,183

Index Scan using fki_loan_asset_registration_2_asset_loan on loan_asset_registration l_1 (cost=0.42..8.14 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=71,183)

  • Index Cond: (asset_loan_id = aot_was_in_spv_1.asset_loan_id)
23. 71.183 569.464 ↑ 1.0 1 71,183

Limit (cost=0.57..3.37 rows=1 width=108) (actual time=0.008..0.008 rows=1 loops=71,183)

24. 498.281 498.281 ↑ 461.0 1 71,183

Index Scan using unc_lendify_loan_summary_loan_id_as_of_date_desc on loan_summary ls_1 (cost=0.57..1,291.31 rows=461 width=108) (actual time=0.007..0.007 rows=1 loops=71,183)

  • Index Cond: ((loan_id = l_1.lendify_loan_id) AND (as_of_date <= '2020-01-05'::date))
25.          

CTE bb_data

26. 5,491.659 88,033.108 ↑ 6.0 71,183 1

Sort (cost=5,540,849.07..5,541,913.41 rows=425,736 width=3,235) (actual time=87,809.980..88,033.108 rows=71,183 loops=1)

  • Sort Key: (CASE WHEN (le.id = 158) THEN 0 ELSE 1 END), (CASE WHEN (le.id = 158) THEN aot_1.ownership_start_date ELSE ln.origination_date END), ln.origination_date, ln.external_id
  • Sort Method: external merge Disk: 58184kB
27. 2,467.930 82,541.449 ↑ 6.0 71,183 1

Merge Left Join (cost=3,763,101.08..4,313,633.60 rows=425,736 width=3,235) (actual time=73,650.251..82,541.449 rows=71,183 loops=1)

  • Merge Cond: (ln.id = ln_1.id)
  • Filter: ((aot_curr_owner.to_entity_id = 158) OR (COALESCE(aot_1.ownership_end_date, '1900-01-01'::date) >= (date_trunc('month'::text, ('2019-12-31'::date)::timestamp with time zone))::date) OR ((((COALESCE((sum(CASE WHEN ((NOT cf_2.charge_off_recovery) AND (lcr.cash_transfer_date <= '2020-01-05'::date)) THEN cf_2.principal ELSE NULL::numeric END)), 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((sum(CASE WHEN ((NOT cf_2.charge_off_recovery) AND (lcr.cash_transfer_date <= '2020-01-05'::date)) THEN cf_2.origination_fee ELSE NULL::numeric END)), 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((max(lcr.cash_transfer_date)), whcf_adj.max_adj_transfer_date)), '1900-01-01'::date) >= '2019-12-31'::date))
28. 203.600 67,762.428 ↑ 6.0 71,183 1

Merge Join (cost=2,635,152.51..2,679,266.40 rows=425,740 width=913) (actual time=65,122.483..67,762.428 rows=71,183 loops=1)

  • Merge Cond: (ln.id = aot_was_in_spv_3.asset_loan_id)
29. 360.551 67,379.565 ↑ 1.6 519,337 1

Merge Left Join (cost=2,633,814.94..2,675,795.30 rows=851,480 width=913) (actual time=64,126.454..67,379.565 rows=519,337 loops=1)

  • Merge Cond: (ln.id = pmts.loan_id)
30. 549.268 59,979.410 ↑ 1.6 519,337 1

Merge Left Join (cost=2,095,314.05..2,135,155.07 rows=851,480 width=689) (actual time=57,098.932..59,979.410 rows=519,337 loops=1)

  • Merge Cond: (ln.id = lsr.loan_id)
31. 378.381 58,208.418 ↑ 1.6 519,337 1

Merge Left Join (cost=2,015,883.62..2,039,686.18 rows=851,480 width=657) (actual time=56,381.919..58,208.418 rows=519,337 loops=1)

  • Merge Cond: (ln.id = ln_end.asset_loan_id)
32. 356.649 57,614.983 ↑ 1.6 519,337 1

Merge Left Join (cost=1,957,973.46..1,972,716.34 rows=851,480 width=425) (actual time=56,264.389..57,614.983 rows=519,337 loops=1)

  • Merge Cond: (ln.id = ln_start.asset_loan_id)
33. 481.377 55,922.819 ↑ 1.6 519,337 1

Merge Left Join (cost=1,908,060.80..1,913,744.01 rows=851,480 width=269) (actual time=55,013.012..55,922.819 rows=519,337 loops=1)

  • Merge Cond: (ln.id = aot_was_in_spv_2.asset_loan_id)
34. 1,387.746 55,327.716 ↑ 1.6 519,337 1

Sort (cost=1,902,203.49..1,904,332.19 rows=851,480 width=265) (actual time=54,934.262..55,327.716 rows=519,337 loops=1)

  • Sort Key: ln.id
  • Sort Method: external merge Disk: 122904kB
35. 1,033.402 53,939.970 ↑ 1.6 519,361 1

Hash Left Join (cost=890,280.12..1,602,965.36 rows=851,480 width=265) (actual time=51,311.273..53,939.970 rows=519,361 loops=1)

  • Hash Cond: (nt.loan_id = borrower.loan_id)
36. 911.433 52,402.750 ↑ 1.6 519,361 1

Hash Left Join (cost=858,775.01..1,503,748.40 rows=851,480 width=212) (actual time=50,795.758..52,402.750 rows=519,361 loops=1)

  • Hash Cond: (nt.loan_id = lp.loan_id)
37. 454.063 48,126.920 ↑ 1.6 519,361 1

Hash Right Join (cost=784,328.54..1,366,888.07 rows=851,480 width=208) (actual time=47,414.863..48,126.920 rows=519,361 loops=1)

  • Hash Cond: (aot_1.asset_id = ln.asset_id)
38. 82.904 265.681 ↓ 1.2 71,183 1

Nested Loop Left Join (cost=1,210.40..559,228.55 rows=60,050 width=26) (actual time=4.945..265.681 rows=71,183 loops=1)

39. 35.885 40.411 ↓ 1.2 71,183 1

Bitmap Heap Scan on asset_ownership_transfer aot_1 (cost=1,209.83..60,552.67 rows=60,050 width=20) (actual time=4.925..40.411 rows=71,183 loops=1)

  • Recheck Cond: (to_entity_id = 158)
  • Filter: (ownership_start_date <= '2020-01-06'::date)
  • Heap Blocks: exact=2711
40. 4.526 4.526 ↓ 1.2 71,183 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,194.82 rows=60,052 width=0) (actual time=4.526..4.526 rows=71,183 loops=1)

  • Index Cond: (to_entity_id = 158)
41. 142.366 142.366 ↑ 1.0 1 71,183

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs (cost=0.57..8.29 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=71,183)

  • Index Cond: (aot_1.loan_balance_snapshot_id = id)
42. 546.186 47,407.176 ↑ 1.6 519,361 1

Hash (cost=750,022.63..750,022.63 rows=851,480 width=190) (actual time=47,407.176..47,407.176 rows=519,361 loops=1)

  • Buckets: 32768 Batches: 64 Memory Usage: 2135kB
43. 307.187 46,860.990 ↑ 1.6 519,361 1

Hash Left Join (cost=571,280.82..750,022.63 rows=851,480 width=190) (actual time=44,396.189..46,860.990 rows=519,361 loops=1)

  • Hash Cond: (nt.tenant_id = lt.id)
44. 303.005 46,553.655 ↑ 1.6 519,361 1

Hash Left Join (cost=571,271.03..738,304.99 rows=851,480 width=193) (actual time=44,396.029..46,553.655 rows=519,361 loops=1)

  • Hash Cond: (aot_curr_owner.to_entity_id = le.id)
45. 697.766 46,250.530 ↑ 1.6 519,361 1

Hash Right Join (cost=571,264.32..726,590.43 rows=851,480 width=160) (actual time=44,395.895..46,250.530 rows=519,361 loops=1)

  • Hash Cond: (aot_curr_owner.asset_id = ln.asset_id)
46. 1,201.478 1,201.478 ↑ 1.7 528,762 1

Seq Scan on asset_ownership_transfer aot_curr_owner (cost=0.00..123,892.93 rows=904,370 width=12) (actual time=0.044..1,201.478 rows=528,762 loops=1)

  • Filter: (('2020-01-06'::date >= ownership_start_date) AND ('2020-01-06'::date <= COALESCE((ownership_end_date - 1), (timezone('PST8PDT'::text, now()))::date)))
  • Rows Removed by Filter: 2166107
47. 519.372 44,351.286 ↓ 1.0 519,361 1

Hash (cost=554,347.24..554,347.24 rows=497,766 width=152) (actual time=44,351.286..44,351.286 rows=519,361 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 3359kB
48. 791.425 43,831.914 ↓ 1.0 519,361 1

Hash Join (cost=159,823.09..554,347.24 rows=497,766 width=152) (actual time=2,546.782..43,831.914 rows=519,361 loops=1)

  • Hash Cond: (nt.loan_number = (ln.external_id)::text)
49. 738.012 41,268.323 ↓ 1.0 519,986 1

Hash Right Join (cost=54,401.93..421,594.52 rows=506,907 width=88) (actual time=765.694..41,268.323 rows=519,986 loops=1)

  • Hash Cond: (de.loan_number = nt.loan_number)
50. 407.961 39,771.123 ↓ 13.2 519,838 1

Hash Join (cost=83.52..361,017.42 rows=39,250 width=51) (actual time=1.433..39,771.123 rows=519,838 loops=1)

  • Hash Cond: ((de.tenant_id = loc.tenant_id) AND (de.location_id = loc.location_id))
51. 39,361.771 39,361.771 ↓ 1.0 519,986 1

Seq Scan on loan_event de (cost=0.00..356,736.88 rows=507,270 width=23) (actual time=0.021..39,361.771 rows=519,986 loops=1)

  • Filter: (transaction_type = 'DISBURSEMENT'::text)
  • Rows Removed by Filter: 8506240
52. 0.692 1.391 ↓ 1.0 1,743 1

Hash (cost=57.41..57.41 rows=1,741 width=40) (actual time=1.391..1.391 rows=1,743 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 144kB
53. 0.699 0.699 ↓ 1.0 1,743 1

Seq Scan on location loc (cost=0.00..57.41 rows=1,741 width=40) (actual time=0.004..0.699 rows=1,743 loops=1)

54. 292.511 759.188 ↓ 1.0 519,986 1

Hash (cost=43,031.07..43,031.07 rows=506,907 width=52) (actual time=759.188..759.188 rows=519,986 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3316kB
55. 466.677 466.677 ↓ 1.0 519,986 1

Seq Scan on loan_product nt (cost=0.00..43,031.07 rows=506,907 width=52) (actual time=0.005..466.677 rows=519,986 loops=1)

56. 343.818 1,772.166 ↓ 1.0 519,361 1

Hash (cost=92,340.77..92,340.77 rows=519,151 width=79) (actual time=1,772.166..1,772.166 rows=519,361 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2297kB
57. 615.641 1,428.348 ↓ 1.0 519,361 1

Hash Left Join (cost=38,641.46..92,340.77 rows=519,151 width=79) (actual time=479.081..1,428.348 rows=519,361 loops=1)

  • Hash Cond: (ln.id = loan.id)
58. 337.086 337.086 ↓ 1.0 519,361 1

Seq Scan on loan ln (cost=0.00..31,438.28 rows=519,151 width=78) (actual time=0.011..337.086 rows=519,361 loops=1)

  • Filter: ((origination_date <= '2020-01-05'::date) AND (originator_id = 1003))
  • Rows Removed by Filter: 9401
59. 180.159 475.621 ↓ 1.0 519,937 1

Hash (cost=30,116.56..30,116.56 rows=519,592 width=5) (actual time=475.621..475.621 rows=519,937 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3371kB
60. 295.462 295.462 ↓ 1.0 519,937 1

Seq Scan on loan (cost=0.00..30,116.56 rows=519,592 width=5) (actual time=0.009..295.462 rows=519,937 loops=1)

  • Filter: (originator_id = 1003)
  • Rows Removed by Filter: 8825
61. 0.050 0.120 ↑ 1.1 155 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
62. 0.070 0.070 ↑ 1.1 155 1

Seq Scan on legal_entity le (cost=0.00..4.65 rows=165 width=33) (actual time=0.005..0.070 rows=155 loops=1)

63. 0.063 0.148 ↓ 1.0 219 1

Hash (cost=7.13..7.13 rows=213 width=5) (actual time=0.148..0.148 rows=219 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
64. 0.085 0.085 ↓ 1.0 219 1

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

65. 244.810 3,364.397 ↓ 1.0 519,986 1

Hash (cost=65,634.14..65,634.14 rows=506,907 width=12) (actual time=3,364.397..3,364.397 rows=519,986 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3823kB
66. 746.068 3,119.587 ↓ 1.0 519,986 1

Hash Right Join (cost=51,843.41..65,634.14 rows=506,907 width=12) (actual time=1,300.972..3,119.587 rows=519,986 loops=1)

  • Hash Cond: (loan_data_end.loan_id = lp.loan_id)
67. 1,745.980 1,745.980 ↑ 3.7 71,183 1

CTE Scan on loan_data_end (cost=0.00..5,199.84 rows=259,992 width=16) (actual time=672.847..1,745.980 rows=71,183 loops=1)

68. 193.687 627.539 ↓ 1.0 519,986 1

Hash (cost=43,031.07..43,031.07 rows=506,907 width=16) (actual time=627.539..627.539 rows=519,986 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 4077kB
69. 433.852 433.852 ↓ 1.0 519,986 1

Seq Scan on loan_product lp (cost=0.00..43,031.07 rows=506,907 width=16) (actual time=0.005..433.852 rows=519,986 loops=1)

70. 265.087 503.818 ↑ 1.0 519,985 1

Hash (cost=18,874.16..18,874.16 rows=521,516 width=69) (actual time=503.818..503.818 rows=519,985 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3314kB
71. 238.731 238.731 ↑ 1.0 519,985 1

Seq Scan on borrower_application borrower (cost=0.00..18,874.16 rows=521,516 width=69) (actual time=0.006..238.731 rows=519,985 loops=1)

72. 96.680 113.726 ↓ 1.2 71,183 1

Sort (cost=5,857.31..6,004.86 rows=59,019 width=4) (actual time=78.742..113.726 rows=71,183 loops=1)

  • Sort Key: aot_was_in_spv_2.asset_loan_id
  • Sort Method: external sort Disk: 1256kB
73. 17.046 17.046 ↓ 1.2 71,183 1

CTE Scan on aot_was_in_spv aot_was_in_spv_2 (cost=0.00..1,180.38 rows=59,019 width=4) (actual time=0.002..17.046 rows=71,183 loops=1)

74. 35.136 1,335.515 ↑ 3.8 68,012 1

Materialize (cost=49,912.66..51,212.62 rows=259,992 width=164) (actual time=1,251.372..1,335.515 rows=68,012 loops=1)

75. 145.183 1,300.379 ↑ 3.8 68,012 1

Sort (cost=49,912.66..50,562.64 rows=259,992 width=164) (actual time=1,251.369..1,300.379 rows=68,012 loops=1)

  • Sort Key: ln_start.asset_loan_id
  • Sort Method: external merge Disk: 4504kB
76. 1,155.196 1,155.196 ↑ 3.8 68,012 1

CTE Scan on loan_data_start ln_start (cost=0.00..5,199.84 rows=259,992 width=164) (actual time=44.284..1,155.196 rows=68,012 loops=1)

77. 37.416 215.054 ↑ 3.7 71,183 1

Materialize (cost=57,910.16..59,210.12 rows=259,992 width=240) (actual time=117.524..215.054 rows=71,183 loops=1)

78. 143.063 177.638 ↑ 3.7 71,183 1

Sort (cost=57,910.16..58,560.14 rows=259,992 width=240) (actual time=117.521..177.638 rows=71,183 loops=1)

  • Sort Key: ln_end.asset_loan_id
  • Sort Method: external merge Disk: 7168kB
79. 34.575 34.575 ↑ 3.7 71,183 1

CTE Scan on loan_data_end ln_end (cost=0.00..5,199.84 rows=259,992 width=240) (actual time=0.026..34.575 rows=71,183 loops=1)

80. 227.221 1,221.724 ↓ 1.0 528,180 1

Materialize (cost=79,430.35..82,039.25 rows=521,780 width=36) (actual time=709.867..1,221.724 rows=528,180 loops=1)

81. 759.269 994.503 ↓ 1.0 528,180 1

Sort (cost=79,430.35..80,734.80 rows=521,780 width=36) (actual time=709.864..994.503 rows=528,180 loops=1)

  • Sort Key: lsr.loan_id
  • Sort Method: external merge Disk: 25712kB
82. 235.234 235.234 ↓ 1.0 528,762 1

Seq Scan on loan_securitization_review lsr (cost=0.00..15,609.80 rows=521,780 width=36) (actual time=0.008..235.234 rows=528,762 loops=1)

83. 25.384 7,039.604 ↓ 46.5 20,498 1

Sort (cost=538,500.89..538,501.99 rows=441 width=228) (actual time=7,027.518..7,039.604 rows=20,498 loops=1)

  • Sort Key: pmts.loan_id
  • Sort Method: quicksort Memory: 2370kB
84. 8.849 7,014.220 ↓ 46.5 20,498 1

Subquery Scan on pmts (cost=538,457.26..538,481.52 rows=441 width=228) (actual time=6,970.894..7,014.220 rows=20,498 loops=1)

85. 79.051 7,005.371 ↓ 46.5 20,498 1

HashAggregate (cost=538,457.26..538,477.11 rows=441 width=30) (actual time=6,970.892..7,005.371 rows=20,498 loops=1)

  • Group Key: cf.loan_id
86. 27.747 6,926.320 ↓ 47.5 20,969 1

Nested Loop (cost=204,267.95..538,448.44 rows=441 width=30) (actual time=5,770.471..6,926.320 rows=20,969 loops=1)

87. 3,466.788 6,407.695 ↓ 1.9 163,626 1

Hash Join (cost=204,267.52..484,160.17 rows=88,285 width=38) (actual time=5,102.406..6,407.695 rows=163,626 loops=1)

  • Hash Cond: (ler.asset_loan_cashflow_id = cf.id)
88. 2,734.073 2,734.073 ↑ 1.0 7,545,609 1

Seq Scan on loan_event_registration ler (cost=0.00..176,068.31 rows=7,545,731 width=16) (actual time=0.006..2,734.073 rows=7,545,609 loops=1)

89. 87.845 206.834 ↓ 1.3 163,626 1

Hash (cost=201,784.69..201,784.69 rows=122,226 width=34) (actual time=206.834..206.834 rows=163,626 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3461kB
90. 105.077 118.989 ↓ 1.3 163,626 1

Bitmap Heap Scan on loan_cashflow cf (cost=2,563.69..201,784.69 rows=122,226 width=34) (actual time=16.559..118.989 rows=163,626 loops=1)

  • Recheck Cond: (recipient_entity_id = 158)
  • Heap Blocks: exact=16167
91. 13.912 13.912 ↓ 1.3 163,635 1

Bitmap Index Scan on nc_asset_loan_cashflow_recipient_entity_id (cost=0.00..2,533.13 rows=122,226 width=0) (actual time=13.912..13.912 rows=163,635 loops=1)

  • Index Cond: (recipient_entity_id = 158)
92. 490.878 490.878 ↓ 0.0 0 163,626

Index Scan using loan_event_pkey on loan_event le_1 (cost=0.43..0.60 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=163,626)

  • Index Cond: (id = ler.lendify_loan_event_id)
  • Filter: (((posting_date)::date >= '2019-12-31'::date) AND ((posting_date)::date <= '2020-01-05'::date))
  • Rows Removed by Filter: 1
93. 112.312 179.263 ↓ 355.9 71,183 1

Sort (cost=1,337.57..1,338.07 rows=200 width=4) (actual time=137.373..179.263 rows=71,183 loops=1)

  • Sort Key: aot_was_in_spv_3.asset_loan_id
  • Sort Method: external sort Disk: 1256kB
94. 48.626 66.951 ↓ 355.9 71,183 1

HashAggregate (cost=1,327.93..1,329.93 rows=200 width=4) (actual time=47.954..66.951 rows=71,183 loops=1)

  • Group Key: aot_was_in_spv_3.asset_loan_id
95. 18.325 18.325 ↓ 1.2 71,183 1

CTE Scan on aot_was_in_spv aot_was_in_spv_3 (cost=0.00..1,180.38 rows=59,019 width=4) (actual time=0.002..18.325 rows=71,183 loops=1)

96. 156.328 12,311.091 ↓ 3.6 71,183 1

Materialize (cost=1,127,948.58..1,301,988.88 rows=19,558 width=2,326) (actual time=8,527.584..12,311.091 rows=71,183 loops=1)

97. 583.684 12,154.763 ↓ 3.6 71,183 1

Merge Left Join (cost=1,127,948.58..1,301,939.99 rows=19,558 width=2,326) (actual time=8,527.577..12,154.763 rows=71,183 loops=1)

  • Merge Cond: (ln_1.id = cf_2.loan_id)
  • Join Filter: (aot_2.to_entity_id = cf_2.recipient_entity_id)
98. 169.265 1,555.355 ↓ 3.6 71,183 1

Nested Loop (cost=306,034.43..472,351.45 rows=19,558 width=1,196) (actual time=896.652..1,555.355 rows=71,183 loops=1)

99. 163.619 1,101.358 ↓ 3.6 71,183 1

Merge Left Join (cost=306,033.86..306,180.70 rows=19,561 width=1,190) (actual time=896.635..1,101.358 rows=71,183 loops=1)

  • Merge Cond: ((ln_1.id = whcf_adj.loan_id) AND (aot_2.to_entity_id = whcf_adj.recipient_entity_id))
100. 95.490 834.323 ↓ 3.6 71,183 1

Sort (cost=102,726.92..102,775.82 rows=19,561 width=34) (actual time=793.539..834.323 rows=71,183 loops=1)

  • Sort Key: ln_1.id, aot_2.to_entity_id
  • Sort Method: external merge Disk: 1800kB
101. 31.828 738.833 ↓ 3.6 71,183 1

Hash Left Join (cost=58,188.30..101,332.64 rows=19,561 width=34) (actual time=565.399..738.833 rows=71,183 loops=1)

  • Hash Cond: (ln_1.asset_id = aot_sale.asset_id)
102. 115.755 706.991 ↓ 3.6 71,183 1

Hash Join (cost=58,171.24..101,242.22 rows=19,561 width=24) (actual time=565.371..706.991 rows=71,183 loops=1)

  • Hash Cond: (aot_2.asset_id = ln_1.asset_id)
103. 34.196 107.122 ↓ 3.6 71,183 1

Bitmap Heap Scan on asset_ownership_transfer aot_2 (cost=19,021.78..59,088.51 rows=19,903 width=16) (actual time=73.294..107.122 rows=71,183 loops=1)

  • Recheck Cond: ((to_entity_id = 158) AND (from_entity_id = 130))
  • Heap Blocks: exact=2711
104. 0.329 72.926 ↓ 0.0 0 1

BitmapAnd (cost=19,021.78..19,021.78 rows=19,903 width=0) (actual time=72.926..72.926 rows=0 loops=1)

105. 4.851 4.851 ↓ 1.2 71,183 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,194.82 rows=60,052 width=0) (actual time=4.851..4.851 rows=71,183 loops=1)

  • Index Cond: (to_entity_id = 158)
106. 67.746 67.746 ↓ 1.0 923,986 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_from_entity_id (cost=0.00..17,816.76 rows=899,244 width=0) (actual time=67.746..67.746 rows=923,986 loops=1)

  • Index Cond: (from_entity_id = 130)
107. 187.909 484.114 ↓ 1.0 519,937 1

Hash (cost=30,116.56..30,116.56 rows=519,592 width=12) (actual time=484.114..484.114 rows=519,937 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2311kB
108. 296.205 296.205 ↓ 1.0 519,937 1

Seq Scan on loan ln_1 (cost=0.00..30,116.56 rows=519,592 width=12) (actual time=0.011..296.205 rows=519,937 loops=1)

  • Filter: (originator_id = 1003)
  • Rows Removed by Filter: 8825
109. 0.000 0.014 ↓ 0.0 0 1

Hash (cost=17.05..17.05 rows=1 width=18) (actual time=0.014..0.014 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
110. 0.001 0.014 ↓ 0.0 0 1

Nested Loop (cost=1.00..17.05 rows=1 width=18) (actual time=0.014..0.014 rows=0 loops=1)

111. 0.013 0.013 ↓ 0.0 0 1

Index Scan using nc_asset_asset_ownership_transfer_from_entity_id on asset_ownership_transfer aot_sale (cost=0.43..8.45 rows=1 width=12) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (from_entity_id = 158)
  • Filter: (to_entity_id = 130)
112. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = aot_sale.loan_balance_snapshot_id)
113. 0.697 103.416 ↓ 30.3 485 1

Sort (cost=203,306.94..203,306.98 rows=16 width=1,036) (actual time=103.086..103.416 rows=485 loops=1)

  • Sort Key: whcf_adj.loan_id, whcf_adj.recipient_entity_id
  • Sort Method: quicksort Memory: 94kB
114. 0.295 102.719 ↓ 30.3 485 1

Subquery Scan on whcf_adj (cost=203,305.02..203,306.62 rows=16 width=1,036) (actual time=101.534..102.719 rows=485 loops=1)

115. 14.783 102.424 ↓ 30.3 485 1

HashAggregate (cost=203,305.02..203,306.46 rows=16 width=39) (actual time=101.532..102.424 rows=485 loops=1)

  • Group Key: cf_1.loan_id, cf_1.recipient_entity_id
116. 1.061 87.641 ↓ 49.4 790 1

Nested Loop Anti Join (cost=2,533.64..203,300.10 rows=16 width=39) (actual time=14.546..87.641 rows=790 loops=1)

117. 72.388 84.210 ↓ 2.7 790 1

Bitmap Heap Scan on loan_cashflow cf_1 (cost=2,533.20..202,365.34 rows=293 width=43) (actual time=14.528..84.210 rows=790 loops=1)

  • Recheck Cond: (recipient_entity_id = 158)
  • Filter: ((GREATEST(cashflow_date, '2017-06-23'::date) <= '2020-01-05'::date) AND ((cashflow_type)::text = ANY ('{CREDIT,CREDIT_REVERSAL}'::text[])))
  • Rows Removed by Filter: 162836
  • Heap Blocks: exact=16167
118. 11.822 11.822 ↓ 1.3 163,635 1

Bitmap Index Scan on nc_asset_loan_cashflow_recipient_entity_id (cost=0.00..2,533.13 rows=122,226 width=0) (actual time=11.822..11.822 rows=163,635 loops=1)

  • Index Cond: (recipient_entity_id = 158)
119. 2.370 2.370 ↓ 0.0 0 790

Index Only Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri (cost=0.44..4.63 rows=2 width=4) (actual time=0.003..0.003 rows=0 loops=790)

  • Index Cond: (loan_cashflow_id = cf_1.id)
  • Heap Fetches: 0
120. 284.732 284.732 ↑ 1.0 1 71,183

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs_1 (cost=0.57..8.49 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=71,183)

  • Index Cond: (id = aot_2.loan_balance_snapshot_id)
121. 67.576 10,015.724 ↓ 4.0 48,576 1

Materialize (cost=821,914.15..829,503.44 rows=12,223 width=1,168) (actual time=7,630.884..10,015.724 rows=48,576 loops=1)

122. 2,210.666 9,948.148 ↓ 4.0 48,576 1

GroupAggregate (cost=821,914.15..829,350.65 rows=12,223 width=44) (actual time=7,630.878..9,948.148 rows=48,576 loops=1)

  • Group Key: cf_2.loan_id, cf_2.recipient_entity_id
123. 261.764 7,737.482 ↓ 7.6 135,009 1

Sort (cost=821,914.15..821,958.84 rows=17,876 width=44) (actual time=7,630.738..7,737.482 rows=135,009 loops=1)

  • Sort Key: cf_2.loan_id, cf_2.recipient_entity_id
  • Sort Method: external merge Disk: 7848kB
124. 89.285 7,475.718 ↓ 7.6 135,009 1

Nested Loop (cost=204,267.81..820,651.59 rows=17,876 width=44) (actual time=2,360.249..7,475.718 rows=135,009 loops=1)

125. 824.519 7,110.163 ↓ 7.7 138,135 1

Hash Join (cost=204,267.52..814,694.04 rows=17,998 width=44) (actual time=2,360.214..7,110.163 rows=138,135 loops=1)

  • Hash Cond: (lcri_1.loan_cashflow_id = cf_2.id)
126. 6,074.310 6,074.310 ↓ 1.1 1,653,979 1

Seq Scan on loan_cashflow_report_item lcri_1 (cost=0.00..588,498.85 rows=1,538,317 width=13) (actual time=6.294..6,074.310 rows=1,653,979 loops=1)

  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 18760428
127. 91.258 211.334 ↓ 1.3 163,626 1

Hash (cost=201,784.69..201,784.69 rows=122,226 width=39) (actual time=211.334..211.334 rows=163,626 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3621kB
128. 107.891 120.076 ↓ 1.3 163,626 1

Bitmap Heap Scan on loan_cashflow cf_2 (cost=2,563.69..201,784.69 rows=122,226 width=39) (actual time=14.926..120.076 rows=163,626 loops=1)

  • Recheck Cond: (recipient_entity_id = 158)
  • Heap Blocks: exact=16167
129. 12.185 12.185 ↓ 1.3 163,635 1

Bitmap Index Scan on nc_asset_loan_cashflow_recipient_entity_id (cost=0.00..2,533.13 rows=122,226 width=0) (actual time=12.185..12.185 rows=163,635 loops=1)

  • Index Cond: (recipient_entity_id = 158)
130. 276.270 276.270 ↑ 1.0 1 138,135

Index Scan using loan_cashflow_report_pkey on loan_cashflow_report lcr (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=138,135)

  • Index Cond: (id = lcri_1.loan_cashflow_report_id)
  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= '2020-01-05'::date))
  • Rows Removed by Filter: 0
Planning time : 16.081 ms
Execution time : 88,361.820 ms