explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3kay

Settings
# exclusive inclusive rows x rows loops node
1. 3,635.361 3,635.361 ↑ 4.9 295,087 1

CTE Scan on aot (cost=24,715,034.56..24,743,664.48 rows=1,431,496 width=64) (actual time=625.909..3,635.361 rows=295,087 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 loan

5. 136.260 498.008 ↓ 4.0 87,631 1

HashAggregate (cost=101,161.09..101,380.58 rows=21,949 width=78) (actual time=445.547..498.008 rows=87,631 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.001..0.002 rows=1 loops=1)

8. 103.695 361.746 ↓ 4.0 87,631 1

Nested Loop (cost=1,630.08..100,502.60 rows=21,949 width=78) (actual time=5.901..361.746 rows=87,631 loops=1)

9. 44.190 82.789 ↓ 1.3 87,631 1

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

10. 0.004 0.008 ↑ 1.0 1 1

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

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

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

12. 33.168 38.591 ↓ 1.3 87,631 1

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

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

  • Index Cond: (to_entity_id = constant_1.owner_entity_id)
14. 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=78) (actual time=0.002..0.002 rows=1 loops=87,631)

  • Index Cond: (asset_id = aot_1.asset_id)
  • Filter: ((origination_date <= $1) AND (originator_id = 1003))
15.          

CTE aot

16. 720.622 3,399.197 ↑ 4.9 295,087 1

Nested Loop (cost=494.28..24,588,172.40 rows=1,431,496 width=28) (actual time=625.906..3,399.197 rows=295,087 loops=1)

17. 96.980 677.354 ↓ 438.2 87,631 1

HashAggregate (cost=493.85..495.85 rows=200 width=4) (actual time=625.860..677.354 rows=87,631 loops=1)

  • Group Key: loan.asset_id
18. 580.374 580.374 ↓ 4.0 87,631 1

CTE Scan on loan (cost=0.00..438.98 rows=21,949 width=4) (actual time=445.550..580.374 rows=87,631 loops=1)

19. 525.786 525.786 ↑ 3.0 3 87,631

Index Scan using nc_asset_asset_ownership_transfer_asset_id on asset_ownership_transfer aot_2 (cost=0.43..8.57 rows=9 width=28) (actual time=0.004..0.006 rows=3 loops=87,631)

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

SubPlan (for Nested Loop)

21. 885.261 885.261 ↑ 1.0 1 295,087

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs (cost=0.57..8.59 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=295,087)

  • Index Cond: (id = aot_2.loan_balance_snapshot_id)
22. 590.174 590.174 ↑ 1.0 1 295,087

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=295,087)

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

CTE spv_cashflow

24. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=494.32..25,481.57 rows=162,240 width=233) (never executed)

  • Hash Cond: (cf.recipient_entity_id = constant_2.spv_entity_id)
25. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=494.29..9,196.66 rows=5,516,175 width=233) (never executed)

26. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=493.85..495.85 rows=200 width=4) (never executed)

  • Group Key: loan_1.asset_loan_id
27. 0.000 0.000 ↓ 0.0 0

CTE Scan on loan loan_1 (cost=0.00..438.98 rows=21,949 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using nc_asset_loan_cashflow_loan_id on loan_cashflow cf (cost=0.43..43.14 rows=36 width=233) (never executed)

  • Index Cond: (loan_id = loan_1.asset_loan_id)
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=4) (never executed)

30. 0.000 0.000 ↓ 0.0 0

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

Planning time : 0.900 ms
Execution time : 3,698.254 ms