explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ksz4U

Settings
# exclusive inclusive rows x rows loops node
1. 7,722.112 7,722.112 ↓ 4.0 48,576 1

CTE Scan on whcf (cost=932,447.78..932,692.24 rows=12,223 width=1,168) (actual time=5,907.212..7,722.112 rows=48,576 loops=1)

2.          

CTE aot_was_in_spv

3. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=102,506.94..103,097.13 rows=59,019 width=4) (never executed)

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

Hash Join (cost=39,851.30..102,359.39 rows=59,019 width=4) (never executed)

  • Hash Cond: (aot.asset_id = lo.asset_id)
5. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,209.83..60,402.54 rows=60,052 width=4) (never executed)

  • Recheck Cond: (to_entity_id = 158)
6. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,194.82 rows=60,052 width=0) (never executed)

  • Index Cond: (to_entity_id = 158)
7. 0.000 0.000 ↓ 0.0 0

Hash (cost=30,116.56..30,116.56 rows=519,592 width=8) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan lo (cost=0.00..30,116.56 rows=519,592 width=8) (never executed)

  • Filter: (originator_id = 1003)
9.          

CTE whcf

10. 1,655.146 7,635.142 ↓ 4.0 48,576 1

GroupAggregate (cost=821,914.15..829,350.65 rows=12,223 width=44) (actual time=5,907.208..7,635.142 rows=48,576 loops=1)

  • Group Key: cf.loan_id, cf.recipient_entity_id
11. 218.646 5,979.996 ↓ 7.6 135,009 1

Sort (cost=821,914.15..821,958.84 rows=17,876 width=44) (actual time=5,907.076..5,979.996 rows=135,009 loops=1)

  • Sort Key: cf.loan_id, cf.recipient_entity_id
  • Sort Method: external merge Disk: 7848kB
12. 81.095 5,761.350 ↓ 7.6 135,009 1

Nested Loop (cost=204,267.81..820,651.59 rows=17,876 width=44) (actual time=2,271.989..5,761.350 rows=135,009 loops=1)

13. 805.544 5,403.985 ↓ 7.7 138,135 1

Hash Join (cost=204,267.52..814,694.04 rows=17,998 width=44) (actual time=2,271.948..5,403.985 rows=138,135 loops=1)

  • Hash Cond: (lcri.loan_cashflow_id = cf.id)
14. 4,397.862 4,397.862 ↓ 1.1 1,653,979 1

Seq Scan on loan_cashflow_report_item lcri (cost=0.00..588,498.85 rows=1,538,317 width=13) (actual time=6.186..4,397.862 rows=1,653,979 loops=1)

  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 18760428
15. 86.867 200.579 ↓ 1.3 163,626 1

Hash (cost=201,784.69..201,784.69 rows=122,226 width=39) (actual time=200.579..200.579 rows=163,626 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3621kB
16. 101.737 113.712 ↓ 1.3 163,626 1

Bitmap Heap Scan on loan_cashflow cf (cost=2,563.69..201,784.69 rows=122,226 width=39) (actual time=14.655..113.712 rows=163,626 loops=1)

  • Recheck Cond: (recipient_entity_id = 158)
  • Heap Blocks: exact=16167
17. 11.975 11.975 ↓ 1.3 163,635 1

Bitmap Index Scan on nc_asset_loan_cashflow_recipient_entity_id (cost=0.00..2,533.13 rows=122,226 width=0) (actual time=11.975..11.975 rows=163,635 loops=1)

  • Index Cond: (recipient_entity_id = 158)
18. 276.270 276.270 ↑ 1.0 1 138,135

Index Scan using loan_cashflow_report_pkey on loan_cashflow_report lcr (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=138,135)

  • Index Cond: (id = lcri.loan_cashflow_report_id)
  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= '2020-01-05'::date))
  • Rows Removed by Filter: 0
Planning time : 1.200 ms
Execution time : 7,736.973 ms