explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HYxr

Settings
# exclusive inclusive rows x rows loops node
1. 78.853 140,029.618 ↓ 86.0 20,040 1

Nested Loop Left Join (cost=4.35..3,155.91 rows=233 width=441) (actual time=17,895.182..140,029.618 rows=20,040 loops=1)

2. 49,375.508 127,145.205 ↓ 86.0 20,040 1

Nested Loop Left Join (cost=3.78..1,153.86 rows=233 width=422) (actual time=17,895.173..127,145.205 rows=20,040 loops=1)

  • Join Filter: (ro.refinance_offer_id = a.application_source_id)
  • Rows Removed by Join Filter: 131,343,766
3. 39.555 1,183.666 ↓ 199.4 14,159 1

Nested Loop Left Join (cost=0.42..1,049.37 rows=71 width=32) (actual time=2.953..1,183.666 rows=14,159 loops=1)

4. 39.709 39.709 ↓ 199.4 14,159 1

Seq Scan on refinance_offer ro (cost=0.00..649.41 rows=71 width=32) (actual time=0.007..39.709 rows=14,159 loops=1)

  • Filter: (upper(asserted) = 'infinity'::timestamp with time zone)
  • Rows Removed by Filter: 2
5. 1,104.402 1,104.402 ↑ 1.0 1 14,159

Index Only Scan using loan_settings_entity_loan_id on loan_settings_entity base_lsse (cost=0.42..5.62 rows=1 width=8) (actual time=0.077..0.078 rows=1 loops=14,159)

  • Index Cond: (loan_id = ro.base_loan_id)
  • Heap Fetches: 9,716
6. 58,730.132 76,586.031 ↓ 3,092.3 9,277 14,159

Materialize (cost=3.36..101.31 rows=3 width=398) (actual time=0.004..5.409 rows=9,277 loops=14,159)

7. 31.506 17,855.899 ↓ 3,092.3 9,277 1

Nested Loop Left Join (cost=3.36..101.29 rows=3 width=398) (actual time=15.864..17,855.899 rows=9,277 loops=1)

8. 9.965 5,297.615 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=2.67..57.61 rows=1 width=293) (actual time=10.502..5,297.615 rows=3,938 loops=1)

9. 10.344 3,995.986 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=2.39..49.70 rows=1 width=69) (actual time=9.881..3,995.986 rows=3,938 loops=1)

10. 7.819 3,402.818 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=1.97..41.25 rows=1 width=64) (actual time=4.669..3,402.818 rows=3,938 loops=1)

11. 10.718 2,780.671 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=1.55..33.08 rows=1 width=48) (actual time=4.099..2,780.671 rows=3,938 loops=1)

12. 11.403 2,742.387 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=1.12..24.95 rows=1 width=44) (actual time=4.063..2,742.387 rows=3,938 loops=1)

13. 10.285 809.240 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=0.70..16.75 rows=1 width=40) (actual time=1.002..809.240 rows=3,938 loops=1)

14. 16.686 16.686 ↓ 3,931.0 3,931 1

Index Scan using application_application_id_refinance_ind_infinity on application a (cost=0.28..8.29 rows=1 width=32) (actual time=0.037..16.686 rows=3,931 loops=1)

15. 782.269 782.269 ↑ 1.0 1 3,931

Index Scan using loan_application_id_infinity on loan l (cost=0.42..8.44 rows=1 width=12) (actual time=0.197..0.199 rows=1 loops=3,931)

  • Index Cond: (a.application_id = application_id)
16. 1,921.744 1,921.744 ↑ 1.0 1 3,938

Index Scan using loan_setup_entity_loan_id on loan_setup_entity lse (cost=0.42..8.20 rows=1 width=8) (actual time=0.488..0.488 rows=1 loops=3,938)

  • Index Cond: (loan_id = l.loan_pro_loan_id)
17. 27.566 27.566 ↑ 1.0 1 3,938

Index Scan using loan_entity_loan_id on loan_entity le (cost=0.42..8.12 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=3,938)

  • Index Cond: (id = l.loan_pro_loan_id)
18. 614.328 614.328 ↑ 1.0 1 3,938

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lsse (cost=0.42..8.16 rows=1 width=24) (actual time=0.153..0.156 rows=1 loops=3,938)

  • Index Cond: (loan_id = l.loan_pro_loan_id)
19. 582.824 582.824 ↑ 1.0 1 3,938

Index Scan using app_parsed_application_id on app_parsed ap (cost=0.42..8.44 rows=1 width=13) (actual time=0.146..0.148 rows=1 loops=3,938)

  • Index Cond: (application_id = a.application_id)
20. 1,291.664 1,291.664 ↑ 1.0 1 3,938

Index Scan using note_entity_parent_id_refinanced on note_entity ne (cost=0.28..7.90 rows=1 width=228) (actual time=0.327..0.328 rows=1 loops=3,938)

  • Index Cond: (parent_id = l.loan_pro_loan_id)
21. 12,526.778 12,526.778 ↑ 5.0 2 3,938

Index Scan using custom_field__entity_entity_id_entity_type_custom_field_id on custom_field__entity cfe (cost=0.69..43.58 rows=10 width=117) (actual time=1.347..3.181 rows=2 loops=3,938)

  • Index Cond: ((entity_id = lsse.id) AND (custom_field_id = ANY ('{1,73,100,52}'::integer[])))
22. 12,805.560 12,805.560 ↓ 0.0 0 20,040

Index Scan using loan_status_archive_date_loan_id_rename on loan_status_archive base_lsa (cost=0.57..8.59 rows=1 width=31) (actual time=0.639..0.639 rows=0 loops=20,040)

  • Index Cond: ((date = (a.application_created_at)::date) AND (loan_id = ro.base_loan_id))
Planning time : 67.738 ms
Execution time : 140,199.399 ms