explain.depesz.com

PostgreSQL's explain analyze made readable

Result: frKf

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,320.756 ↓ 0.0 0 1

Nested Loop Left Join (cost=681,675.11..850,129.75 rows=1 width=322) (actual time=3,320.756..3,320.756 rows=0 loops=1)

2.          

CTE table_trunc

3. 0.162 14.386 ↓ 17.7 532 1

Nested Loop (cost=1.41..287.99 rows=30 width=48) (actual time=1.571..14.386 rows=532 loops=1)

  • Join Filter: (allo_1.loan_id = lir.id)
4. 0.278 12.096 ↓ 17.7 532 1

Nested Loop (cost=0.98..274.30 rows=30 width=48) (actual time=1.557..12.096 rows=532 loops=1)

5. 8.626 8.626 ↓ 17.7 532 1

Index Scan using loan_allocation_loan_allocation_group_id_idx on loan_allocation allo_1 (cost=0.42..196.60 rows=30 width=8) (actual time=1.542..8.626 rows=532 loops=1)

  • Index Cond: (loan_allocation_group_id = '9277666'::bigint)
  • Filter: ((create_date >= '2020-09-01'::date) AND (create_date < '2020-09-30'::date))
6. 3.192 3.192 ↑ 1.0 1 532

Index Scan using loan_application_pkey on loan_application la (cost=0.56..2.58 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=532)

  • Index Cond: (id = allo_1.loan_id)
7. 2.128 2.128 ↑ 1.0 1 532

Index Scan using loan_in_review_pkey on loan_in_review lir (cost=0.42..0.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=532)

  • Index Cond: (id = la.id)
8. 0.001 3,320.757 ↓ 0.0 0 1

Nested Loop Left Join (cost=681,386.56..849,841.07 rows=1 width=642) (actual time=3,320.756..3,320.757 rows=0 loops=1)

9. 0.001 3,320.756 ↓ 0.0 0 1

Nested Loop Left Join (cost=681,386.12..849,838.60 rows=1 width=634) (actual time=3,320.756..3,320.756 rows=0 loops=1)

10. 0.001 3,320.755 ↓ 0.0 0 1

Merge Right Join (cost=681,385.69..849,836.62 rows=1 width=552) (actual time=3,320.755..3,320.755 rows=0 loops=1)

  • Merge Cond: (lga.loan_application_id = apcc.loan_app_id)
11. 0.007 3,287.822 ↑ 25,915.0 1 1

Subquery Scan on lga (cost=678,313.97..846,758.61 rows=25,915 width=16) (actual time=3,287.822..3,287.822 rows=1 loops=1)

  • Filter: (lga.row_num = 1)
12. 0.010 3,287.815 ↑ 5,182,912.0 1 1

WindowAgg (cost=678,313.97..781,972.21 rows=5,182,912 width=56) (actual time=3,287.815..3,287.815 rows=1 loops=1)

13. 2,409.540 3,287.805 ↑ 5,182,912.0 1 1

Sort (cost=678,313.97..691,271.25 rows=5,182,912 width=24) (actual time=3,287.805..3,287.805 rows=1 loops=1)

  • Sort Key: login_application.loan_application_id, login_application.update_date DESC
  • Sort Method: external merge Disk: 172,256kB
14. 878.265 878.265 ↑ 1.0 5,182,835 1

Seq Scan on login_application (cost=0.00..100,281.12 rows=5,182,912 width=24) (actual time=0.005..878.265 rows=5,182,835 loops=1)

15. 0.012 32.932 ↓ 0.0 0 1

Sort (cost=3,013.20..3,013.21 rows=1 width=548) (actual time=32.932..32.932 rows=0 loops=1)

  • Sort Key: apcc.loan_app_id
  • Sort Method: quicksort Memory: 25kB
16. 0.001 32.920 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.53..3,013.19 rows=1 width=548) (actual time=32.920..32.920 rows=0 loops=1)

  • Join Filter: (apcc.lir_borrower_id = lborrower.id)
17. 0.001 32.919 ↓ 0.0 0 1

Nested Loop (cost=1.96..3,010.60 rows=1 width=544) (actual time=32.919..32.919 rows=0 loops=1)

18. 0.119 32.918 ↓ 0.0 0 1

Nested Loop (cost=1.54..3,008.15 rows=1 width=470) (actual time=32.918..32.918 rows=0 loops=1)

19. 6.046 32.799 ↓ 17.7 532 1

Hash Right Join (cost=0.97..2,930.30 rows=30 width=131) (actual time=32.095..32.799 rows=532 loops=1)

  • Hash Cond: (contact_d.lead_id = allo.la_lead_id)
20. 12.019 12.019 ↑ 1.0 70,520 1

Seq Scan on contact_detail contact_d (cost=0.00..2,664.47 rows=70,547 width=99) (actual time=0.007..12.019 rows=70,520 loops=1)

21. 0.112 14.734 ↓ 17.7 532 1

Hash (cost=0.60..0.60 rows=30 width=48) (actual time=14.734..14.734 rows=532 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
22. 14.622 14.622 ↓ 17.7 532 1

CTE Scan on table_trunc allo (cost=0.00..0.60 rows=30 width=48) (actual time=1.573..14.622 rows=532 loops=1)

23. 0.000 0.000 ↓ 0.0 0 532

Index Scan using borrower_pkey on borrower fborrower (cost=0.56..2.59 rows=1 width=355) (actual time=0.000..0.000 rows=0 loops=532)

  • Index Cond: (id = allo.funnel_co_borrower_id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using borrower_pkey on borrower lborrower (cost=0.42..2.44 rows=1 width=82) (never executed)

  • Index Cond: (id = allo.lir_borrower_id)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using apcc_loan_borrower_idx on applicant_credit_char apcc (cost=0.56..2.58 rows=1 width=20) (never executed)

  • Index Cond: (allo.loan_id = loan_app_id)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using login_pkey on login (cost=0.43..1.97 rows=1 width=98) (never executed)

  • Index Cond: (lga.login_external_id = id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using partner_lead_pkey on partner_lead pl (cost=0.44..2.46 rows=1 width=24) (never executed)

  • Index Cond: (allo.la_partner_lead_id = id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using partner_lead_uuid_idx on partner_lead lpl (cost=0.56..0.68 rows=1 width=106) (never executed)

  • Index Cond: (pl.uuid = uuid)
Planning time : 5.242 ms