explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FmFN

Settings
# exclusive inclusive rows x rows loops node
1. 64.654 156,515.906 ↓ 7.6 20,040 1

Nested Loop Left Join (cost=1,055.78..23,888.66 rows=2,631 width=441) (actual time=339.609..156,515.906 rows=20,040 loops=1)

2. 41.972 143,866.132 ↓ 7.6 20,040 1

Merge Right Join (cost=1,055.21..1,281.79 rows=2,631 width=422) (actual time=339.584..143,866.132 rows=20,040 loops=1)

  • Merge Cond: (a.application_source_id = ro.refinance_offer_id)
3. 38.434 143,486.685 ↓ 257.7 9,277 1

Nested Loop Left Join (cost=3.66..8,147.34 rows=36 width=398) (actual time=12.691..143,486.685 rows=9,277 loops=1)

4. 21.705 120,682.673 ↓ 328.2 3,938 1

Nested Loop Left Join (cost=2.97..7,623.13 rows=12 width=293) (actual time=11.182..120,682.673 rows=3,938 loops=1)

5. 16.820 117,116.768 ↓ 358.0 3,938 1

Nested Loop Left Join (cost=2.55..7,530.18 rows=11 width=288) (actual time=8.494..117,116.768 rows=3,938 loops=1)

6. 13.640 87,978.438 ↓ 358.0 3,938 1

Nested Loop Left Join (cost=2.12..6,819.60 rows=11 width=64) (actual time=6.734..87,978.438 rows=3,938 loops=1)

7. 13.455 87,504.052 ↓ 358.0 3,938 1

Nested Loop Left Join (cost=1.69..6,733.05 rows=11 width=48) (actual time=5.206..87,504.052 rows=3,938 loops=1)

8. 18.301 86,899.897 ↓ 358.0 3,938 1

Nested Loop Left Join (cost=1.27..6,646.48 rows=11 width=44) (actual time=4.139..86,899.897 rows=3,938 loops=1)

9. 16.746 85,550.552 ↓ 358.0 3,938 1

Nested Loop Left Join (cost=0.85..6,560.02 rows=11 width=40) (actual time=3.547..85,550.552 rows=3,938 loops=1)

10. 12,605.894 12,605.894 ↓ 3,931.0 3,931 1

Index Scan using application_application_source_id_application_source_type_id on application a (cost=0.41..6,358.36 rows=1 width=32) (actual time=2.152..12,605.894 rows=3,931 loops=1)

  • Index Cond: (application_source_type_id = 4)
  • Filter: ((upper((effective)::tstzrange) = 'infinity'::timestamp with time zone) AND (upper((asserted)::tstzrange) = 'infinity'::timestamp with time zone))
  • Rows Removed by Filter: 35,739
11. 72,927.912 72,927.912 ↑ 15.0 1 3,931

Index Scan using loan_application_id on loan l (cost=0.43..201.51 rows=15 width=12) (actual time=2.353..18.552 rows=1 loops=3,931)

  • Index Cond: (a.application_id = application_id)
  • Filter: ((product = 'balancecredit'::text) AND (now() <@ (effective)::tstzrange) AND (now() <@ (asserted)::tstzrange))
  • Rows Removed by Filter: 19
12. 1,331.044 1,331.044 ↑ 1.0 1 3,938

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

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

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

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

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lsse (cost=0.42..7.86 rows=1 width=24) (actual time=0.116..0.117 rows=1 loops=3,938)

  • Index Cond: (loan_id = l.loan_pro_loan_id)
15. 29,121.510 29,121.510 ↑ 1.0 1 3,938

Index Scan using note_entity_parent_id_parent_type on note_entity ne (cost=0.43..64.59 rows=1 width=228) (actual time=6.535..7.395 rows=1 loops=3,938)

  • Index Cond: (parent_id = l.loan_pro_loan_id)
  • Filter: ((category_id = 25) AND (subject = 'New refinanced loan'::text))
  • Rows Removed by Filter: 6
16. 3,544.200 3,544.200 ↑ 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.896..0.900 rows=1 loops=3,938)

  • Index Cond: (application_id = a.application_id)
17. 22,765.578 22,765.578 ↑ 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.936..5.781 rows=2 loops=3,938)

  • Index Cond: ((entity_id = lsse.id) AND (custom_field_id = ANY ('{1,73,100,52}'::integer[])))
18. 23.979 337.475 ↓ 282.3 20,040 1

Sort (cost=1,051.55..1,051.73 rows=71 width=32) (actual time=326.888..337.475 rows=20,040 loops=1)

  • Sort Key: ro.refinance_offer_id
  • Sort Method: quicksort Memory: 1,491kB
19. 26.309 313.496 ↓ 199.4 14,159 1

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

20. 18.166 18.166 ↓ 199.4 14,159 1

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

  • Filter: (upper(asserted) = 'infinity'::timestamp with time zone)
  • Rows Removed by Filter: 2
21. 269.021 269.021 ↑ 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.019..0.019 rows=1 loops=14,159)

  • Index Cond: (loan_id = ro.base_loan_id)
  • Heap Fetches: 9,649
22. 12,585.120 12,585.120 ↓ 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.628..0.628 rows=0 loops=20,040)

  • Index Cond: ((date = (a.application_created_at)::date) AND (loan_id = ro.base_loan_id))
Planning time : 154.884 ms
Execution time : 156,866.804 ms