explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KnNZ

Settings
# exclusive inclusive rows x rows loops node
1. 599.745 599.745 ↓ 4.0 87,631 1

CTE Scan on loan (cost=126,862.16..127,301.14 rows=21,949 width=362) (actual time=461.643..599.745 rows=87,631 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. 140.818 514.157 ↓ 4.0 87,631 1

HashAggregate (cost=101,161.09..101,380.58 rows=21,949 width=78) (actual time=461.639..514.157 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. 112.851 373.337 ↓ 4.0 87,631 1

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

9. 45.367 85.224 ↓ 1.3 87,631 1

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (actual time=5.883..85.224 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.008..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. 34.418 39.849 ↓ 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.873..39.849 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.431 5.431 ↓ 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.431..5.431 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.asset_id)
  • Filter: ((origination_date <= $1) AND (originator_id = 1003))
15.          

CTE spv_cashflow

16. 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)
17. 0.000 0.000 ↓ 0.0 0

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

18. 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
19. 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)

20. 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)
21. 0.000 0.000 ↓ 0.0 0

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

22. 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.748 ms
Execution time : 617.901 ms