explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cuy

Settings
# exclusive inclusive rows x rows loops node
1. 2,702.537 2,702.537 ↑ 26.0 45 1

CTE Scan on pmt (cost=677,217.91..677,241.35 rows=1,172 width=228) (actual time=2,702.446..2,702.537 rows=45 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. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=100,499.81..101,158.27 rows=65,846 width=4) (never executed)

  • Group Key: lo.id
6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,630.08..100,335.20 rows=65,846 width=4) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (never executed)

8. 0.000 0.000 ↓ 0.0 0

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

  • Group Key: constant.owner_entity_id
9. 0.000 0.000 ↓ 0.0 0

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

10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,629.63..66,039.29 rows=66,962 width=8) (never executed)

  • Recheck Cond: (to_entity_id = constant.owner_entity_id)
  • Filter: ((ownership_end_date IS NULL) OR (to_entity_id <> 504))
11. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,612.89 rows=79,528 width=0) (never executed)

  • Index Cond: (to_entity_id = constant.owner_entity_id)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using unc_asset_loan_asset_id on loan lo (cost=0.42..0.49 rows=1 width=8) (never executed)

  • Index Cond: (asset_id = aot.asset_id)
  • Filter: (originator_id = 1003)
13.          

CTE pmt

14. 0.164 2,702.499 ↑ 26.0 45 1

HashAggregate (cost=576,006.89..576,059.63 rows=1,172 width=30) (actual time=2,702.443..2,702.499 rows=45 loops=1)

  • Group Key: cf.loan_id
15.          

Initplan (for HashAggregate)

16. 0.001 0.001 ↑ 1.0 1 1

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

17. 0.004 0.004 ↑ 1.0 1 1

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

18. 231.212 2,702.330 ↑ 26.0 45 1

Nested Loop (cost=6,816.05..575,983.41 rows=1,172 width=30) (actual time=2,701.656..2,702.330 rows=45 loops=1)

19. 422.522 1,749.320 ↓ 1.5 360,899 1

Nested Loop (cost=6,815.62..431,924.00 rows=234,304 width=38) (actual time=36.802..1,749.320 rows=360,899 loops=1)

20. 212.576 605.000 ↓ 1.1 360,899 1

Nested Loop (cost=6,815.19..267,596.38 rows=324,481 width=34) (actual time=36.788..605.000 rows=360,899 loops=1)

21. 0.004 0.007 ↑ 1.0 1 1

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

  • Group Key: constant_3.spv_entity_id
22. 0.003 0.003 ↑ 1.0 1 1

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

23. 361.236 392.417 ↓ 1.1 360,899 1

Bitmap Heap Scan on loan_cashflow cf (cost=6,815.16..264,351.53 rows=324,481 width=38) (actual time=36.777..392.417 rows=360,899 loops=1)

  • Recheck Cond: (recipient_entity_id = constant_3.spv_entity_id)
  • Rows Removed by Index Recheck: 1063862
  • Heap Blocks: exact=32438 lossy=26555
24. 31.181 31.181 ↓ 1.1 360,911 1

Bitmap Index Scan on nc_asset_loan_cashflow_recipient_entity_id (cost=0.00..6,734.04 rows=324,481 width=0) (actual time=31.181..31.181 rows=360,911 loops=1)

  • Index Cond: (recipient_entity_id = constant_3.spv_entity_id)
25. 721.798 721.798 ↑ 1.0 1 360,899

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) (actual time=0.002..0.002 rows=1 loops=360,899)

  • Index Cond: (asset_loan_cashflow_id = cf.id)
26. 721.798 721.798 ↓ 0.0 0 360,899

Index Scan using loan_event_pkey on loan_event le (cost=0.43..0.60 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=360,899)

  • Index Cond: (id = ler.lendify_loan_event_id)
  • Filter: (((posting_date)::date >= $4) AND ((posting_date)::date <= $5))
  • Rows Removed by Filter: 1
Planning time : 0.984 ms
Execution time : 2,702.849 ms