explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QHB8

Settings
# exclusive inclusive rows x rows loops node
1. 4,552.021 4,552.021 ↑ 335.7 3,201 1

CTE Scan on aot (cost=18,550,455.07..18,571,943.97 rows=1,074,445 width=64) (actual time=4,392.604..4,552.021 rows=3,201 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. 4.454 4,386.031 ↓ 7.1 3,201 1

HashAggregate (cost=70,092.50..70,097.00 rows=450 width=78) (actual time=4,384.673..4,386.031 rows=3,201 loops=1)

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

Initplan (for HashAggregate)

7. 0.002 0.002 ↑ 1.0 1 1

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

8. 3.114 4,381.575 ↓ 7.1 3,201 1

Nested Loop (cost=1,618.14..70,078.98 rows=450 width=78) (actual time=4,334.271..4,381.575 rows=3,201 loops=1)

9. 1.394 4,368.858 ↓ 2.3 3,201 1

Nested Loop (cost=1,617.71..66,289.45 rows=1,373 width=4) (actual time=4,333.711..4,368.858 rows=3,201 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. 4,242.532 4,367.456 ↓ 2.3 3,201 1

Bitmap Heap Scan on asset_ownership_transfer aot_1 (cost=1,617.69..66,275.69 rows=1,373 width=8) (actual time=4,333.698..4,367.456 rows=3,201 loops=1)

  • Recheck Cond: (to_entity_id = constant_1.owner_entity_id)
  • Filter: (((ownership_end_date IS NULL) OR (to_entity_id <> 504)) AND (ownership_start_date >= '2020-01-01'::date))
  • Rows Removed by Filter: 198,638
  • Heap Blocks: exact=27,584
13. 124.924 124.924 ↓ 3.1 249,894 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,617.35 rows=79,589 width=0) (actual time=124.924..124.924 rows=249,894 loops=1)

  • Index Cond: (to_entity_id = constant_1.owner_entity_id)
14. 9.603 9.603 ↑ 1.0 1 3,201

Index Scan using unc_asset_loan_asset_id on loan lo (cost=0.42..2.75 rows=1 width=78) (actual time=0.003..0.003 rows=1 loops=3,201)

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

CTE aot

16. 7.491 4,549.438 ↑ 335.7 3,201 1

Nested Loop (cost=10.60..18,455,351.75 rows=1,074,445 width=28) (actual time=4,392.600..4,549.438 rows=3,201 loops=1)

17. 2.824 4,391.500 ↓ 16.0 3,201 1

HashAggregate (cost=10.12..12.12 rows=200 width=4) (actual time=4,390.107..4,391.500 rows=3,201 loops=1)

  • Group Key: loan.asset_id
18. 4,388.676 4,388.676 ↓ 7.1 3,201 1

CTE Scan on loan (cost=0.00..9.00 rows=450 width=4) (actual time=4,384.676..4,388.676 rows=3,201 loops=1)

19. 115.235 115.236 ↑ 7.0 1 3,201

Index Scan using nc_asset_asset_ownership_transfer_asset_id on asset_ownership_transfer aot_2 (cost=0.47..8.66 rows=7 width=28) (actual time=0.012..0.036 rows=1 loops=3,201)

  • Index Cond: (asset_id = loan.asset_id)
  • Filter: ((hashed SubPlan 6) OR (hashed SubPlan 7))
  • Rows Removed by Filter: 2
20.          

SubPlan (for Index Scan)

21. 0.001 0.001 ↑ 1.0 1 1

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

22. 0.000 0.000 ↑ 1.0 1 1

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

23.          

SubPlan (for Nested Loop)

24. 28.809 28.809 ↑ 1.0 1 3,201

Index Scan using loan_balance_snapshot_pkey on loan_balance_snapshot lbs (cost=0.57..8.59 rows=1 width=6) (actual time=0.009..0.009 rows=1 loops=3,201)

  • Index Cond: (id = aot_2.loan_balance_snapshot_id)
25. 6.402 6.402 ↑ 1.0 1 3,201

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=3,201)

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

CTE spv_cashflow

27. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=10.59..25,006.31 rows=162,324 width=233) (never executed)

  • Hash Cond: (cf.recipient_entity_id = constant_4.spv_entity_id)
28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=10.56..8,712.93 rows=5,519,040 width=233) (never executed)

29. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=10.12..12.12 rows=200 width=4) (never executed)

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

CTE Scan on loan loan_1 (cost=0.00..9.00 rows=450 width=4) (never executed)

31. 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)
32. 0.000 0.000 ↓ 0.0 0

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

33. 0.000 0.000 ↓ 0.0 0

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

Planning time : 32.080 ms
Execution time : 4,553.267 ms