explain.depesz.com

PostgreSQL's explain analyze made readable

Result: emxz

Settings
# exclusive inclusive rows x rows loops node
1. 2.204 81,818.379 ↓ 46.0 46 1

Unique (cost=13,153.18..13,153.26 rows=1 width=2,746) (actual time=81,815.740..81,818.379 rows=46 loops=1)

2. 33.651 81,816.175 ↓ 3,082.0 3,082 1

Sort (cost=13,153.18..13,153.18 rows=1 width=2,746) (actual time=81,815.738..81,816.175 rows=3,082 loops=1)

  • Sort Key: applicants.last_name, applicants.first_name, applicants.id, applicants.ssn, applicants.created_at, applicants.updated_at, applicants.score, applicants.identifier, applicants.email, applicants.middle_name, applicants.suffix, applicants.title, applicants.last_refresh, applicants.date_added, applicants.cas_id, applicants.application_complete, applicants.verified, applicants.verified_date, applicants.professional_pin, applicants.match_id, applicants.pre_submission_release_authorized, applicants.photo_file_name, applicants.photo_content_type, applicants.photo_file_size, applicants.photo_updated_at, applicants.id_number, applicants.id_number_type, applicants.visible_for_advisor, applicants.direct_applicant_complete, applicants.email_type_id, applicants.dentpin, applicants.date_submitted
  • Sort Method: quicksort Memory: 915kB
3. 1,103.272 81,782.524 ↓ 3,082.0 3,082 1

Nested Loop (cost=4.60..13,153.17 rows=1 width=2,746) (actual time=20.834..81,782.524 rows=3,082 loops=1)

  • Join Filter: (assignment_bases.applicant_id = applicant_designation_details_mv.applicant_id)
  • Rows Removed by Join Filter: 8243278
4. 56.469 1,514.196 ↓ 24,254.0 24,254 1

Nested Loop Semi Join (cost=2.87..9,846.87 rows=1 width=2,750) (actual time=0.442..1,514.196 rows=24,254 loops=1)

  • Join Filter: (assignment_bases.applicant_id = applicant_designation_details_mv_2.applicant_id)
5. 36.376 924.139 ↓ 24,254.0 24,254 1

Nested Loop (cost=2.00..9,727.02 rows=1 width=2,746) (actual time=0.403..924.139 rows=24,254 loops=1)

  • Join Filter: (assignment_bases.assignment_type_base_id = assignment_type_bases.id)
6. 57.570 742.239 ↓ 24,254.0 24,254 1

Nested Loop (cost=1.71..9,721.85 rows=1 width=2,754) (actual time=0.378..742.239 rows=24,254 loops=1)

7. 14.249 393.621 ↓ 24,254.0 24,254 1

Nested Loop (cost=1.28..9,713.39 rows=1 width=12) (actual time=0.324..393.621 rows=24,254 loops=1)

8. 0.215 1.344 ↓ 8.3 322 1

Nested Loop (cost=0.85..329.90 rows=39 width=4) (actual time=0.113..1.344 rows=322 loops=1)

9. 0.191 0.191 ↓ 2.2 67 1

Index Only Scan using index_user_identity_programs_on_user_identity_id_and_program_id on user_identity_programs (cost=0.43..66.62 rows=30 width=4) (actual time=0.092..0.191 rows=67 loops=1)

  • Index Cond: (user_identity_id = 406065)
  • Heap Fetches: 67
10. 0.938 0.938 ↓ 1.7 5 67

Index Scan using index_assignment_type_bases_programs_on_program_id on assignment_type_bases_programs (cost=0.42..8.75 rows=3 width=8) (actual time=0.009..0.014 rows=5 loops=67)

  • Index Cond: (program_id = user_identity_programs.program_id)
11. 378.028 378.028 ↓ 75.0 75 322

Index Scan using index_assignment_bases_on_assignment_type_base_id on assignment_bases (cost=0.43..240.59 rows=1 width=8) (actual time=0.012..1.174 rows=75 loops=322)

  • Index Cond: (assignment_type_base_id = assignment_type_bases_programs.assignment_type_base_id)
  • Filter: (user_identity_id = 406065)
  • Rows Removed by Filter: 950
12. 291.048 291.048 ↑ 1.0 1 24,254

Index Scan using applicants_pkey on applicants (cost=0.43..8.45 rows=1 width=2,742) (actual time=0.011..0.012 rows=1 loops=24,254)

  • Index Cond: (id = assignment_bases.applicant_id)
  • Filter: (association_id = 446)
13. 145.524 145.524 ↑ 1.0 1 24,254

Index Only Scan using assignment_types_pkey on assignment_type_bases (cost=0.29..5.16 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=24,254)

  • Index Cond: (id = assignment_type_bases_programs.assignment_type_base_id)
  • Heap Fetches: 24254
14. 92.192 533.588 ↑ 1.0 1 24,254

Nested Loop Semi Join (cost=0.87..60.35 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=24,254)

15. 315.302 315.302 ↑ 2.3 3 24,254

Index Scan using index_addw_on_applicant_id on applicant_designation_details_mv applicant_designation_details_mv_2 (cost=0.44..18.15 rows=7 width=8) (actual time=0.010..0.013 rows=3 loops=24,254)

  • Index Cond: (applicant_id = applicants.id)
  • Filter: status_show
  • Rows Removed by Filter: 0
16. 126.094 126.094 ↓ 0.0 0 63,047

Index Only Scan using index_user_identity_programs_on_user_identity_id_and_program_id on user_identity_programs user_identity_programs_2 (cost=0.43..5.33 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=63,047)

  • Index Cond: ((user_identity_id = 406065) AND (program_id = applicant_designation_details_mv_2.program_id))
  • Heap Fetches: 24254
17. 4,899.308 79,165.056 ↓ 340.0 340 24,254

Nested Loop Semi Join (cost=1.73..3,306.28 rows=1 width=4) (actual time=0.018..3.264 rows=340 loops=24,254)

18. 4,220.196 4,220.196 ↓ 3.4 361 24,254

Index Scan using index_addw_on_local_status_id on applicant_designation_details_mv (cost=0.44..184.60 rows=107 width=8) (actual time=0.008..0.174 rows=361 loops=24,254)

  • Index Cond: (local_status_id = 321421)
19. 8,755.694 70,045.552 ↑ 1.0 1 8,755,694

Nested Loop Semi Join (cost=1.29..28.91 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=8,755,694)

20. 26,267.082 26,267.082 ↑ 1.0 1 8,755,694

Index Scan using applicant_designation_details_mv_pkey on applicant_designation_details_mv applicant_designation_details_mv_1 (cost=0.44..8.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=8,755,694)

  • Index Cond: (id = applicant_designation_details_mv.id)
  • Filter: status_show
21. 0.000 35,022.776 ↑ 1.0 1 8,755,694

Nested Loop (cost=0.85..10.65 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=8,755,694)

  • Join Filter: (programs.id = user_identity_programs_1.program_id)
22. 17,511.388 17,511.388 ↑ 1.0 1 8,755,694

Index Scan using programs_pkey on programs (cost=0.42..5.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8,755,694)

  • Index Cond: (id = applicant_designation_details_mv_1.program_id)
  • Filter: ((type)::text = 'Program'::text)
23. 17,511.388 17,511.388 ↑ 1.0 1 8,755,694

Index Only Scan using index_user_identity_programs_on_user_identity_id_and_program_id on user_identity_programs user_identity_programs_1 (cost=0.43..5.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8,755,694)

  • Index Cond: ((user_identity_id = 406065) AND (program_id = applicant_designation_details_mv_1.program_id))
  • Heap Fetches: 8246360