Skip to content

Instantly share code, notes, and snippets.

@jcreed

jcreed/query plan

Created May 22, 2019 14:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jcreed/7dca0520f14cab7cbc6f3bf86f3f5d2d to your computer and use it in GitHub Desktop.
Save jcreed/7dca0520f14cab7cbc6f3bf86f3f5d2d to your computer and use it in GitHub Desktop.
Download ZIP
200599 - new index
Raw
query plan
Unique (cost=52105.42..52105.70 rows=1 width=7404)
-> Sort (cost=52105.42..52105.43 rows=1 width=7404)
Sort Key: designations.rank, designations.school_name, applicants.last_name, emails.created_at DESC, applicants_emails.id, applicants_emails.applicant_id, applicants_emails.email_id, applicants_emails.status_description, applicants_emails.created_at, applicants_emails.updated_at, applicants.ssn, applicants.created_at, applicants.updated_at, applicants.score, applicants.identifier, applicants.email, applicants.middle_name, applicants.first_name, applicants.suffix, applicants.title, applicants.last_refresh, applicants.date_added, applicants.cas_id, applicants.association_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, emails.body, emails.subject, emails.user_id, emails.bcc_user, emails.reply_to_user, emails.cc_user, emails.sent_at, emails.email_template_id, emails.enqueued_at, emails.user_identity_id, emails.updated_at, emails.priority, designations.id, designations.identifier, designations.school_code, designations.school_state, designations.deadline, designations.school_short, designations.school_abbr, designations.format, designations.no_lors, designations.comments, designations.previously_applied, designations.mailed, designations.mailed_date, designations.decision_id, designations.application_status_id, designations.program_id, designations.date_locked, designations.association_id, designations.designation_submitted, designations.application_status_last_updated, designations.decision_last_updated, designations.decision_last_updated_by_id, designations.local_status_last_updated, designations.local_status_last_updated_by_id, designations.verified_date, designations.complete_date, designations.academic_update_status_id, designations.academic_update_started_date, designations.academic_update_submitted_date, designations.academic_update_verified_date, designations.first_complete_date, designations.early_decision_id, designations.verified_datetime, designations.area_of_interest_id, designations.department_id, designations.designation_term_id, designations.application_type_id, designations.created_at, designations.updated_at, designations.enrollment_year, designations.decision_last_updated_by_user_id, designations.local_status_last_updated_by_user_id, designations.designation_degree_id, designations.organization_institution_number, designations.campus_id, designations.postmatch_consideration, designations.all_evaluation_letters_received, designations.alternate_program, designations.added_date, designations.invitation_code, designations.accepted_program_type_id, designations.designation_effective_date, designations.designation_projected_graduation_date, designations.last_exported_at, designations.alternate_program_available_id
-> Merge Join (cost=52035.72..52105.41 rows=1 width=7404)
Merge Cond: (applicants_1.id = applicants.id)
-> Unique (cost=25834.35..25842.48 rows=1625 width=4)
-> Sort (cost=25834.35..25838.41 rows=1625 width=4)
Sort Key: applicants_1.id
-> Nested Loop (cost=15111.78..25747.69 rows=1625 width=4)
-> HashAggregate (cost=15111.35..15258.88 rows=14753 width=4)
Group Key: applicant_designation_details_mv.applicant_id
-> Nested Loop (cost=50.07..15074.47 rows=14753 width=4)
-> HashAggregate (cost=49.51..49.72 rows=21 width=4)
Group Key: user_identity_programs_1.program_id
-> 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..49.46 rows=21 width=4)
Index Cond: (user_identity_id = 308818)
-> Index Only Scan using idx_designation_details_on_status_show_program_id_applicant_id on applicant_designation_details_mv (cost=0.56..713.12 rows=234 width=8)
Index Cond: ((status_show = true) AND (program_id = user_identity_programs_1.program_id))
Filter: status_show
-> Index Scan using applicants_pkey on applicants applicants_1 (cost=0.43..0.70 rows=1 width=4)
Index Cond: (id = applicant_designation_details_mv.applicant_id)
Filter: (association_id = 400)
-> Materialize (cost=26201.37..26242.61 rows=1 width=7324)
-> Nested Loop (cost=26201.37..26242.61 rows=1 width=7324)
-> Nested Loop (cost=26201.09..26240.67 rows=1 width=7324)
-> Nested Loop (cost=26200.65..26238.08 rows=1 width=6212)
-> Nested Loop (cost=26200.22..26233.56 rows=1 width=6132)
-> Merge Join (cost=26199.78..26228.47 rows=1 width=3389)
Merge Cond: (applicants_2.id = designations.applicant_id)
-> Unique (cost=25834.35..25842.48 rows=1625 width=4)
-> Sort (cost=25834.35..25838.41 rows=1625 width=4)
Sort Key: applicants_2.id
-> Nested Loop (cost=15111.78..25747.69 rows=1625 width=4)
-> HashAggregate (cost=15111.35..15258.88 rows=14753 width=4)
Group Key: applicant_designation_details_mv_1.applicant_id
-> Nested Loop (cost=50.07..15074.47 rows=14753 width=4)
-> HashAggregate (cost=49.51..49.72 rows=21 width=4)
Group Key: user_identity_programs_2.program_id
-> 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..49.46 rows=21 width=4)
Index Cond: (user_identity_id = 308818)
-> Index Only Scan using idx_designation_details_on_status_show_program_id_applicant_id on applicant_designation_details_mv applicant_designation_details_mv_1 (cost=0.56..713.12 rows=234 width=8)
Index Cond: ((status_show = true) AND (program_id = user_identity_programs_2.program_id))
Filter: status_show
-> Index Scan using applicants_pkey on applicants applicants_2 (cost=0.43..0.70 rows=1 width=4)
Index Cond: (id = applicant_designation_details_mv_1.applicant_id)
Filter: (association_id = 400)
-> Sort (cost=365.43..365.55 rows=48 width=3385)
Sort Key: designations.applicant_id
-> Nested Loop (cost=227.34..364.09 rows=48 width=3385)
-> HashAggregate (cost=226.91..227.12 rows=21 width=8)
Group Key: programs.id
-> Nested Loop (cost=0.85..226.85 rows=21 width=8)
-> Index Only Scan using index_user_identity_programs_on_user_identity_id_and_program_id on user_identity_programs (cost=0.43..49.46 rows=21 width=4)
Index Cond: (user_identity_id = 308818)
-> Index Scan using programs_pkey on programs (cost=0.42..8.44 rows=1 width=4)
Index Cond: (id = user_identity_programs.program_id)
Filter: ((type)::text = 'Program'::text)
-> Index Scan using index_designations_on_program_id_and_local_status_id on designations (cost=0.43..6.50 rows=2 width=3385)
Index Cond: ((program_id = programs.id) AND (local_status_id = 200599))
-> Index Scan using applicants_pkey on applicants (cost=0.43..5.08 rows=1 width=2743)
Index Cond: (id = designations.applicant_id)
-> Index Scan using index_applicants_emails_on_applicant_id on applicants_emails (cost=0.44..4.27 rows=25 width=80)
Index Cond: (applicant_id = applicants.id)
-> Index Scan using emails_pkey on emails (cost=0.43..2.58 rows=1 width=1112)
Index Cond: (id = applicants_emails.email_id)
Filter: (user_identity_id = ANY ('{307957,308019,308116,308271,308357,308435,308527,308624,308755,308818,309702,309706,309709,315412,315413,315414,315415,315416,315417,315418,315419,315420,315421,315422,315423,315424,315425,315426,315427,315428,315429,315430,315431,351317,352296,367011}'::integer[]))
-> Index Scan using application_statuses_pkey on statuses (cost=0.28..1.93 rows=1 width=4)
Index Cond: (id = designations.application_status_id)
Filter: (show AND ((type)::text = 'ApplicationStatus'::text))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment