explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qh9B

Settings
# exclusive inclusive rows x rows loops node
1. 64.996 113,609.848 ↓ 86.0 20,040 1

Nested Loop Left Join (cost=1,055.77..5,229.89 rows=233 width=441) (actual time=8,280.879..113,609.848 rows=20,040 loops=1)

2. 40.467 93,284.412 ↓ 86.0 20,040 1

Merge Right Join (cost=1,055.20..3,227.84 rows=233 width=422) (actual time=8,247.818..93,284.412 rows=20,040 loops=1)

  • Merge Cond: (a.application_source_id = ro.refinance_offer_id)
3. 40.010 85,144.916 ↓ 3,092.3 9,277 1

Nested Loop Left Join (cost=3.65..6,520.46 rows=3 width=398) (actual time=158.938..85,144.916 rows=9,277 loops=1)

4. 11.237 52,399.816 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=2.96..6,476.78 rows=1 width=293) (actual time=116.191..52,399.816 rows=3,938 loops=1)

5. 14.204 32,824.595 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=2.53..6,408.27 rows=1 width=69) (actual time=79.951..32,824.595 rows=3,938 loops=1)

6. 20.567 29,230.749 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=2.10..6,399.82 rows=1 width=64) (actual time=73.869..29,230.749 rows=3,938 loops=1)

7. 13.690 28,840.010 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=1.68..6,391.65 rows=1 width=48) (actual time=67.765..28,840.010 rows=3,938 loops=1)

8. 16.502 28,590.040 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=1.26..6,383.52 rows=1 width=44) (actual time=42.157..28,590.040 rows=3,938 loops=1)

9. 15.784 24,493.770 ↓ 3,938.0 3,938 1

Nested Loop Left Join (cost=0.84..6,375.31 rows=1 width=40) (actual time=29.246..24,493.770 rows=3,938 loops=1)

10. 22,948.827 22,948.827 ↓ 3,931.0 3,931 1

Index Scan using application_application_source_id_application_source_type_id on application a (cost=0.41..6,366.86 rows=1 width=32) (actual time=23.045..22,948.827 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,751
11. 1,529.159 1,529.159 ↑ 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.388..0.389 rows=1 loops=3,931)

  • Index Cond: (a.application_id = application_id)
12. 4,079.768 4,079.768 ↑ 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=1.036..1.036 rows=1 loops=3,938)

  • Index Cond: (loan_id = l.loan_pro_loan_id)
13. 236.280 236.280 ↑ 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.060..0.060 rows=1 loops=3,938)

  • Index Cond: (id = l.loan_pro_loan_id)
14. 370.172 370.172 ↑ 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.093..0.094 rows=1 loops=3,938)

  • Index Cond: (loan_id = l.loan_pro_loan_id)
15. 3,579.642 3,579.642 ↑ 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.908..0.909 rows=1 loops=3,938)

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

Index Scan using note_entity_parent_id_parent_type on note_entity ne (cost=0.43..68.50 rows=1 width=228) (actual time=4.743..4.968 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
17. 32,705.090 32,705.090 ↑ 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=3.858..8.305 rows=2 loops=3,938)

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

Sort (cost=1,051.55..1,051.73 rows=71 width=32) (actual time=8,088.873..8,099.029 rows=20,040 loops=1)

  • Sort Key: ro.refinance_offer_id
  • Sort Method: quicksort Memory: 1,491kB
19. 16.931 8,073.400 ↓ 199.4 14,159 1

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

20. 14.157 14.157 ↓ 199.4 14,159 1

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

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

  • Index Cond: (loan_id = ro.base_loan_id)
  • Heap Fetches: 9,716
22. 20,260.440 20,260.440 ↓ 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=1.011..1.011 rows=0 loops=20,040)

  • Index Cond: ((date = (a.application_created_at)::date) AND (loan_id = ro.base_loan_id))
Planning time : 25.535 ms
Execution time : 113,738.638 ms