explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ImJi

Settings
# exclusive inclusive rows x rows loops node
1. 20,153.253 20,153.253 ↓ 0.0 0 1

CTE Scan on pmt (cost=281,858.93..281,870.65 rows=586 width=228) (actual time=20,153.253..20,153.253 rows=0 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 aot_was_in_spv

5. 10.178 325.446 ↑ 5.6 11,861 1

HashAggregate (cost=100,499.81..101,158.27 rows=65,846 width=4) (actual time=321.089..325.446 rows=11,861 loops=1)

  • Group Key: lo.id
6. 7.564 315.268 ↑ 5.6 11,861 1

Nested Loop (cost=1,630.08..100,335.20 rows=65,846 width=4) (actual time=50.562..315.268 rows=11,861 loops=1)

7. 6.070 272.121 ↑ 5.6 11,861 1

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (actual time=50.547..272.121 rows=11,861 loops=1)

8. 0.004 0.006 ↑ 1.0 1 1

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

  • Group Key: constant.owner_entity_id
9. 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)

10. 224.288 266.045 ↑ 5.6 11,861 1

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,629.63..66,039.29 rows=66,962 width=8) (actual time=50.539..266.045 rows=11,861 loops=1)

  • Recheck Cond: (to_entity_id = constant.owner_entity_id)
  • Filter: ((ownership_end_date IS NULL) OR (to_entity_id <> 504))
  • Rows Removed by Filter: 527,077
  • Heap Blocks: exact=50,222
11. 41.757 41.757 ↓ 7.9 627,264 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=41.757..41.757 rows=627,264 loops=1)

  • Index Cond: (to_entity_id = constant.owner_entity_id)
12. 35.583 35.583 ↑ 1.0 1 11,861

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.003 rows=1 loops=11,861)

  • Index Cond: (asset_id = aot.asset_id)
  • Filter: (originator_id = 1,003)
13.          

CTE pmt

14. 0.004 20,153.251 ↓ 0.0 0 1

HashAggregate (cost=180,674.28..180,700.65 rows=586 width=30) (actual time=20,153.251..20,153.251 rows=0 loops=1)

  • Group Key: cf.loan_id
15.          

Initplan (for HashAggregate)

16. 0.000 0.000 ↓ 0.0 0

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

17. 0.000 0.000 ↓ 0.0 0

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

18. 0.001 20,153.247 ↓ 0.0 0 1

Nested Loop (cost=1,482.87..180,662.52 rows=586 width=30) (actual time=20,153.247..20,153.247 rows=0 loops=1)

19. 0.001 20,153.246 ↓ 0.0 0 1

Nested Loop (cost=1,482.43..108,632.81 rows=117,152 width=38) (actual time=20,153.246..20,153.246 rows=0 loops=1)

20. 34.883 20,153.245 ↓ 0.0 0 1

Hash Semi Join (cost=1,482.00..26,469.25 rows=162,240 width=34) (actual time=20,153.245..20,153.245 rows=0 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_3.spv_entity_id)
21. 96.733 20,118.356 ↑ 48.7 113,189 1

Nested Loop (cost=1,481.97..10,184.34 rows=5,516,175 width=38) (actual time=338.726..20,118.356 rows=113,189 loops=1)

22. 12.345 344.224 ↓ 59.3 11,861 1

HashAggregate (cost=1,481.54..1,483.54 rows=200 width=4) (actual time=337.116..344.224 rows=11,861 loops=1)

  • Group Key: aot_was_in_spv.asset_loan_id
23. 331.879 331.879 ↑ 5.6 11,861 1

CTE Scan on aot_was_in_spv (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=321.091..331.879 rows=11,861 loops=1)

24. 19,677.399 19,677.399 ↑ 3.6 10 11,861

Index Scan using nc_asset_loan_cashflow_loan_id on loan_cashflow cf (cost=0.43..43.14 rows=36 width=38) (actual time=0.355..1.659 rows=10 loops=11,861)

  • Index Cond: (loan_id = aot_was_in_spv.asset_loan_id)
25. 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: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.004 0.004 ↑ 1.0 1 1

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

27. 0.000 0.000 ↓ 0.0 0

Index Scan using fki_loan_event_registration_2_asset_loan_cashflow on loan_event_registration ler (cost=0.43..0.50 rows=1 width=16) (never executed)

  • Index Cond: (asset_loan_cashflow_id = cf.id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_event_pkey on loan_event le (cost=0.43..0.60 rows=1 width=8) (never executed)

  • Index Cond: (id = ler.lendify_loan_event_id)
  • Filter: (((posting_date)::date >= $4) AND ((posting_date)::date <= $5))
Planning time : 1.135 ms
Execution time : 20,153.650 ms