explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k6dq

Settings
# exclusive inclusive rows x rows loops node
1. 1.618 5,790.945 ↑ 1.1 17,254 1

Append (cost=310.18..2,665,648.76 rows=19,574 width=40) (actual time=7.792..5,790.945 rows=17,254 loops=1)

  • Buffers: shared hit=5895 read=24120, temp read=2044 written=1022
2. 217.269 277.765 ↓ 2.8 10,697 1

Hash Join (cost=310.18..9,446.69 rows=3,868 width=40) (actual time=7.791..277.765 rows=10,697 loops=1)

  • Output: (to_char(fu_convert_time_zone(pd.head_count_close_date, ''::character varying, ua.timezone), 'YYYYMMDD'::text))::integer, (to_char(fu_convert_time_zone(pd.head_count_close_date, ''::character varying, ua.timezone), 'HH24MISSMS'::text))::integer, pac.user_id, 15, pd.id, pd.id, '-1'::integer, CURRENT_DATE
  • Hash Cond: (pd.id = pac.position_id)
  • Buffers: shared hit=969 read=5121
3. 52.879 52.879 ↓ 1.0 30,944 1

Seq Scan on public.position_description pd (cost=0.00..6,903.38 rows=30,842 width=16) (actual time=0.078..52.879 rows=30,944 loops=1)

  • Output: pd.id, pd.name, pd.business_unit, pd.line_manager_id, pd.internal_recruiter_id, pd.vertical_id, pd.location_id, pd.full_description, pd.public_description, pd.visible_to_vendors, pd.visible_to_hr, pd.hidden_timestamp, pd.latest_status_change_date, pd.latest_status, pd.insert_timestamp, pd.compensation_xml, pd.contract_type, pd.candidate_description_template_xml, pd.priority, pd.sourcing_difficulty, pd.time_to_hire, pd.time_to_source, pd.candidate_rejection_process, pd.hr_id, pd.visible_to_all, pd.head_count_open_date, pd.head_count_close_date, pd.creator_account_id, pd.interview_feedback_final_decisions, pd.position_type, pd.total_cost, pd.costcenter_signoff, pd.candidate_ref_no1, pd.candidate_ref_no2, pd.candidate_ref_no3, pd.notes1, pd.notes2, pd.notes3, pd.post_to_job_street, pd.post_to_job_db, pd.post_to_linkedin, pd.post_to_strais_times, pd.post_to_recruite, pd.manual_closed, pd.intership_type, pd.notify_whom_by_email, pd.currency_type, pd.internal_mobility, pd.internal_mobility_release_at, pd.matrix_manager_id, pd.offer_letter, pd.external_release, pd.contract_expired_remind_days, pd.contract_expired_remind_whom, pd.business_partner_id, pd.internal_mobility_internal_recruiter, pd.internal_mobility_line_manager, pd.key_words, pd.highshool_graduate, pd.key_word_amount, pd.final_decision_maker_last_interviewer, pd.specific_url_careersite, pd.emails_to_cv_accepted_notification, pd.company_id, pd.contact_id, pd.internal_recruiter, pd.hiring_line_manager, pd.head_count, pd.company_location_id, pd.trigger_index_update_timestamp, pd.hot_job, pd.annual_salary_from, pd.annual_salary_to, pd.updated_timestamp, pd.updated_by, pd.floated_job, pd.auto_submit_candidate, pd.employment_type, pd.percentage_placement, pd.time_to_fill, pd.difficulty_level, pd.reason_for_difficulty, pd.forecast_annual_fee, pd.forecast_annual_fee_currency, pd.use_compensation_fee, pd.projected_placement_date, pd.note, pd.external_id, pd.private_job, pd.summary, pd.share_rating, pd.use_time_temp, pd.purchase_order, pd.job_site_detail, pd.internal_recruiter_contact_id, pd.site_manager_contact_id, pd.hot_end_date, pd.published_date, pd.submission_date, pd.submission_limit, pd.submission_time, pd.deleted_timestamp
  • Filter: ((pd.floated_job = 0) AND (pd.head_count_close_date <= now()))
  • Rows Removed by Filter: 20106
  • Buffers: shared hit=889 read=5121
4. 3.092 7.617 ↓ 1.8 11,469 1

Hash (cost=230.16..230.16 rows=6,402 width=24) (actual time=7.617..7.617 rows=11,469 loops=1)

  • Output: pac.user_id, pac.position_id, ua.timezone
  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 765kB
  • Buffers: shared hit=80
5. 2.659 4.525 ↓ 1.8 11,469 1

Hash Join (cost=21.85..230.16 rows=6,402 width=24) (actual time=0.189..4.525 rows=11,469 loops=1)

  • Output: pac.user_id, pac.position_id, ua.timezone
  • Inner Unique: true
  • Hash Cond: (pac.user_id = ua.id)
  • Buffers: shared hit=80
6. 1.697 1.697 ↑ 1.0 11,469 1

Seq Scan on public.position_agency_consultant pac (cost=0.00..177.69 rows=11,469 width=8) (actual time=0.014..1.697 rows=11,469 loops=1)

  • Output: pac.id, pac.position_id, pac.user_id, pac.insert_timestamp
  • Filter: (pac.user_id IS NOT NULL)
  • Buffers: shared hit=63
7. 0.033 0.169 ↑ 1.0 139 1

Hash (cost=20.11..20.11 rows=139 width=20) (actual time=0.169..0.169 rows=139 loops=1)

  • Output: ua.timezone, ua.id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=17
8. 0.136 0.136 ↑ 1.0 139 1

Seq Scan on public.user_account ua (cost=0.00..20.11 rows=139 width=20) (actual time=0.011..0.136 rows=139 loops=1)

  • Output: ua.timezone, ua.id
  • Filter: ((ua.deleted_timestamp IS NULL) AND (ua.id <> '-10'::integer))
  • Rows Removed by Filter: 110
  • Buffers: shared hit=17
9. 0.073 280.020 ↓ 1.1 223 1

Subquery Scan on *SELECT* 2 (cost=60.43..747,057.36 rows=200 width=40) (actual time=0.920..280.020 rows=223 loops=1)

  • Output: "*SELECT* 2".date_key, "*SELECT* 2".time_key, "*SELECT* 2".consultant_key, 16, "*SELECT* 2".uni_key, "*SELECT* 2".job_key, "*SELECT* 2".candidate_key, "*SELECT* 2".eventdate
  • Buffers: shared hit=1382 read=6365
10. 6.182 279.947 ↓ 1.1 223 1

Nested Loop (cost=60.43..747,054.36 rows=200 width=32) (actual time=0.920..279.947 rows=223 loops=1)

  • Output: (to_char(fu_convert_time_zone(ih.completed_date, ''::character varying, ua_1.timezone), 'YYYYMMDD'::text))::integer, (to_char(fu_convert_time_zone(ih.completed_date, ''::character varying, ua_1.timezone), 'HH24MISSMS'::text))::integer, c.user_id, 16, b.position_candidate_id, b.position_description_id, b.candidate_id, CURRENT_DATE
  • Inner Unique: true
  • Buffers: shared hit=1382 read=6365
11. 28.033 272.841 ↑ 1.2 308 1

Hash Join (cost=60.28..746,891.81 rows=358 width=24) (actual time=0.414..272.841 rows=308 loops=1)

  • Output: b.position_candidate_id, b.position_description_id, b.candidate_id, ih.completed_date, c.user_id
  • Hash Cond: (c.candidate_key = b.candidate_id)
  • Buffers: shared hit=766 read=6365
12. 23.125 244.437 ↑ 97.8 373,321 1

Subquery Scan on c (cost=0.00..564,253.41 rows=36,514,908 width=12) (actual time=0.023..244.437 rows=373,321 loops=1)

  • Output: c.candidate_key, c.user_id
  • Filter: (c.user_id IS NOT NULL)
  • Buffers: shared hit=738 read=6365
13. 152.032 221.312 ↑ 98.3 373,321 1

ProjectSet (cost=0.00..197,269.41 rows=36,698,400 width=12) (actual time=0.022..221.312 rows=373,321 loops=1)

  • Output: candidate_extension.candidate_id, unnest(candidate_extension.candidate_owner_ids)
  • Buffers: shared hit=738 read=6365
14. 69.280 69.280 ↑ 1.0 366,115 1

Seq Scan on public.candidate_extension (cost=0.00..11,025.03 rows=366,984 width=33) (actual time=0.018..69.280 rows=366,115 loops=1)

  • Output: candidate_extension.candidate_id, candidate_extension.candidate_owner_ids, candidate_extension.candidate_owner_names, candidate_extension.talent_pool_names, candidate_extension.talent_pool_ids, candidate_extension.job_types, candidate_extension.current_job_title, candidate_extension.current_employer, candidate_extension.candidate_next_availability_date, candidate_extension.available_next_week_status, candidate_extension.available_this_week_status, candidate_extension.last_activity_date
  • Filter: (candidate_extension.candidate_owner_ids IS NOT NULL)
  • Rows Removed by Filter: 26088
  • Buffers: shared hit=738 read=6365
15. 0.038 0.371 ↑ 1.1 340 1

Hash (cost=55.78..55.78 rows=360 width=20) (actual time=0.371..0.371 rows=340 loops=1)

  • Output: b.position_candidate_id, b.position_description_id, b.candidate_id, ih.completed_date
  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
  • Buffers: shared hit=28
16. 0.104 0.333 ↑ 1.1 340 1

Hash Join (cost=30.86..55.78 rows=360 width=20) (actual time=0.182..0.333 rows=340 loops=1)

  • Output: b.position_candidate_id, b.position_description_id, b.candidate_id, ih.completed_date
  • Inner Unique: true
  • Hash Cond: (b.id = ih.interview_id)
  • Buffers: shared hit=28
17. 0.059 0.059 ↑ 1.0 864 1

Seq Scan on public.interview b (cost=0.00..22.64 rows=864 width=16) (actual time=0.007..0.059 rows=864 loops=1)

  • Output: b.id, b.position_candidate_id, b.position_description_id, b.candidate_id, b.interview_date, b.interview_date_time_from, b.interview_no, b.insert_timestamp, b.recommended_date_time1, b.recommended_date_time2, b.recommended_date_time3, b.meeting_length_mins, b.cancelled_date, b.user_account_id, b.candidate_rejected_date1, b.candidate_rejected_date2, b.candidate_rejected_date3, b.end_recommended_date_time1, b.end_recommended_date_time2, b.end_recommended_date_time3, b.time_zone, b.additional_user_id, b.company_location_id, b.contact_person_name, b.interview_date_time_to, b.interview_key
  • Buffers: shared hit=14
18. 0.040 0.170 ↑ 1.1 340 1

Hash (cost=26.36..26.36 rows=360 width=16) (actual time=0.170..0.170 rows=340 loops=1)

  • Output: ih.completed_date, ih.interview_id
  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=14
19. 0.130 0.130 ↑ 1.1 340 1

Seq Scan on public.interview_history ih (cost=0.00..26.36 rows=360 width=16) (actual time=0.007..0.130 rows=340 loops=1)

  • Output: ih.completed_date, ih.interview_id
  • Filter: ((ih.completed_date IS NOT NULL) AND (ih.interview_no = 1))
  • Rows Removed by Filter: 649
  • Buffers: shared hit=14
20. 0.924 0.924 ↑ 1.0 1 308

Index Scan using client_account_pkey on public.user_account ua_1 (cost=0.14..0.17 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=308)

  • Output: ua_1.id, ua_1.name, ua_1.name_kana, ua_1.email, ua_1.encrypted_password, ua_1.super_user, ua_1.previous_login_timestamp, ua_1.current_login_timestamp, ua_1.hidden_timestamp, ua_1.supervisor_id, ua_1.internal_recruiter, ua_1.line_manager, ua_1.last_alert_check_timestamp, ua_1.insert_timestamp, ua_1.last_updated_timestamp, ua_1.internal_staff, ua_1.calendar_login_id, ua_1.calendar_password, ua_1.calendar_server, ua_1.job_title, ua_1.phone, ua_1.phone_ext, ua_1.hr, ua_1.interviewer, ua_1.language, ua_1.email_password, ua_1.email_host_port, ua_1.email_api, ua_1.session_id, ua_1.locked_user, ua_1.user_location, ua_1.timezone, ua_1.requisition_signatory, ua_1.matrix_manager, ua_1.business_partner, ua_1.system_admin, ua_1.deleted_timestamp, ua_1.length_calendar_password, ua_1.length_email_password, ua_1.director, ua_1.manager, ua_1.consultant, ua_1.researcher, ua_1.email_host_port_out, ua_1.email_support_type, ua_1.exchange_version, ua_1.currency_type, ua_1.reset_request_time, ua_1.date_format, ua_1.ssl_tls, ua_1.authentication, ua_1.email_signature, ua_1.kpi_permission, ua_1.default_candidate_view, ua_1.default_document_view, ua_1.display_training, ua_1.reset_password_token, ua_1.job_dashboard_default_view, ua_1.hash_salt, ua_1.hash_password, ua_1.hash_iterations, ua_1.display_intro, ua_1.pin_comment, ua_1.new_search, ua_1.view_mode, ua_1.email_connected, ua_1.position_of_box, ua_1.email_account_type, ua_1.freshdesk_user_id, ua_1.outgoing_authentication, ua_1.outgoing_encrypt_type, ua_1.outgoing_email_user_name, ua_1.outgoing_email_password, ua_1.incoming_email_user_name, ua_1.distance_unit, ua_1.mail_status, ua_1.mail_refresh_date, ua_1.exchange_host, ua_1.profile_picture_filename, ua_1.town_city, ua_1.branch, ua_1.division, ua_1.entity, ua_1.default_welcome_page_view, ua_1.candidate_quick_view_tab, ua_1.synced_to_cognito, ua_1.first_name, ua_1.last_name, ua_1.approver_flag
  • Index Cond: (ua_1.id = c.user_id)
  • Filter: ((ua_1.deleted_timestamp IS NULL) AND (ua_1.id <> '-10'::integer))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=616
21. 0.020 253.229 ↓ 1.2 57 1

Subquery Scan on *SELECT* 3 (cost=53.55..701,277.62 rows=47 width=40) (actual time=2.276..253.229 rows=57 loops=1)

  • Output: "*SELECT* 3".date_key, "*SELECT* 3".time_key, "*SELECT* 3".consultant_key, 17, "*SELECT* 3".uni_key, "*SELECT* 3".job_key, "*SELECT* 3".candidate_key, "*SELECT* 3".eventdate
  • Buffers: shared hit=970 read=6333
22. 1.858 253.209 ↓ 1.2 57 1

Nested Loop (cost=53.55..701,276.92 rows=47 width=32) (actual time=2.276..253.209 rows=57 loops=1)

  • Output: (to_char(fu_convert_time_zone(ih_1.completed_date, ''::character varying, ua_2.timezone), 'YYYYMMDD'::text))::integer, (to_char(fu_convert_time_zone(ih_1.completed_date, ''::character varying, ua_2.timezone), 'HH24MISSMS'::text))::integer, c_1.user_id, 17, b_1.position_candidate_id, b_1.position_description_id, b_1.candidate_id, CURRENT_DATE
  • Inner Unique: true
  • Buffers: shared hit=970 read=6333
23. 24.144 251.093 ↓ 1.0 86 1

Hash Join (cost=53.41..701,238.57 rows=85 width=24) (actual time=1.566..251.093 rows=86 loops=1)

  • Output: b_1.position_candidate_id, b_1.position_description_id, b_1.candidate_id, ih_1.completed_date, c_1.user_id
  • Hash Cond: (c_1.candidate_key = b_1.candidate_id)
  • Buffers: shared hit=798 read=6333
24. 23.049 226.674 ↑ 97.8 373,321 1

Subquery Scan on c_1 (cost=0.00..564,253.41 rows=36,514,908 width=12) (actual time=0.029..226.674 rows=373,321 loops=1)

  • Output: c_1.candidate_key, c_1.user_id
  • Filter: (c_1.user_id IS NOT NULL)
  • Buffers: shared hit=770 read=6333
25. 141.685 203.625 ↑ 98.3 373,321 1

ProjectSet (cost=0.00..197,269.41 rows=36,698,400 width=12) (actual time=0.028..203.625 rows=373,321 loops=1)

  • Output: candidate_extension_1.candidate_id, unnest(candidate_extension_1.candidate_owner_ids)
  • Buffers: shared hit=770 read=6333
26. 61.940 61.940 ↑ 1.0 366,115 1

Seq Scan on public.candidate_extension candidate_extension_1 (cost=0.00..11,025.03 rows=366,984 width=33) (actual time=0.027..61.940 rows=366,115 loops=1)

  • Output: candidate_extension_1.candidate_id, candidate_extension_1.candidate_owner_ids, candidate_extension_1.candidate_owner_names, candidate_extension_1.talent_pool_names, candidate_extension_1.talent_pool_ids, candidate_extension_1.job_types, candidate_extension_1.current_job_title, candidate_extension_1.current_employer, candidate_extension_1.candidate_next_availability_date, candidate_extension_1.available_next_week_status, candidate_extension_1.available_this_week_status, candidate_extension_1.last_activity_date
  • Filter: (candidate_extension_1.candidate_owner_ids IS NOT NULL)
  • Rows Removed by Filter: 26088
  • Buffers: shared hit=770 read=6333
27. 0.019 0.275 ↓ 1.2 106 1

Hash (cost=52.34..52.34 rows=85 width=20) (actual time=0.275..0.275 rows=106 loops=1)

  • Output: b_1.position_candidate_id, b_1.position_description_id, b_1.candidate_id, ih_1.completed_date
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=28
28. 0.080 0.256 ↓ 1.2 106 1

Hash Join (cost=27.43..52.34 rows=85 width=20) (actual time=0.138..0.256 rows=106 loops=1)

  • Output: b_1.position_candidate_id, b_1.position_description_id, b_1.candidate_id, ih_1.completed_date
  • Inner Unique: true
  • Hash Cond: (b_1.id = ih_1.interview_id)
  • Buffers: shared hit=28
29. 0.053 0.053 ↑ 1.0 864 1

Seq Scan on public.interview b_1 (cost=0.00..22.64 rows=864 width=16) (actual time=0.007..0.053 rows=864 loops=1)

  • Output: b_1.id, b_1.position_candidate_id, b_1.position_description_id, b_1.candidate_id, b_1.interview_date, b_1.interview_date_time_from, b_1.interview_no, b_1.insert_timestamp, b_1.recommended_date_time1, b_1.recommended_date_time2, b_1.recommended_date_time3, b_1.meeting_length_mins, b_1.cancelled_date, b_1.user_account_id, b_1.candidate_rejected_date1, b_1.candidate_rejected_date2, b_1.candidate_rejected_date3, b_1.end_recommended_date_time1, b_1.end_recommended_date_time2, b_1.end_recommended_date_time3, b_1.time_zone, b_1.additional_user_id, b_1.company_location_id, b_1.contact_person_name, b_1.interview_date_time_to, b_1.interview_key
  • Buffers: shared hit=14
30. 0.014 0.123 ↓ 1.2 106 1

Hash (cost=26.36..26.36 rows=85 width=16) (actual time=0.123..0.123 rows=106 loops=1)

  • Output: ih_1.completed_date, ih_1.interview_id
  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=14
31. 0.109 0.109 ↓ 1.2 106 1

Seq Scan on public.interview_history ih_1 (cost=0.00..26.36 rows=85 width=16) (actual time=0.008..0.109 rows=106 loops=1)

  • Output: ih_1.completed_date, ih_1.interview_id
  • Filter: ((ih_1.completed_date IS NOT NULL) AND (ih_1.interview_no = 2))
  • Rows Removed by Filter: 883
  • Buffers: shared hit=14
32. 0.258 0.258 ↑ 1.0 1 86

Index Scan using client_account_pkey on public.user_account ua_2 (cost=0.14..0.17 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=86)

  • Output: ua_2.id, ua_2.name, ua_2.name_kana, ua_2.email, ua_2.encrypted_password, ua_2.super_user, ua_2.previous_login_timestamp, ua_2.current_login_timestamp, ua_2.hidden_timestamp, ua_2.supervisor_id, ua_2.internal_recruiter, ua_2.line_manager, ua_2.last_alert_check_timestamp, ua_2.insert_timestamp, ua_2.last_updated_timestamp, ua_2.internal_staff, ua_2.calendar_login_id, ua_2.calendar_password, ua_2.calendar_server, ua_2.job_title, ua_2.phone, ua_2.phone_ext, ua_2.hr, ua_2.interviewer, ua_2.language, ua_2.email_password, ua_2.email_host_port, ua_2.email_api, ua_2.session_id, ua_2.locked_user, ua_2.user_location, ua_2.timezone, ua_2.requisition_signatory, ua_2.matrix_manager, ua_2.business_partner, ua_2.system_admin, ua_2.deleted_timestamp, ua_2.length_calendar_password, ua_2.length_email_password, ua_2.director, ua_2.manager, ua_2.consultant, ua_2.researcher, ua_2.email_host_port_out, ua_2.email_support_type, ua_2.exchange_version, ua_2.currency_type, ua_2.reset_request_time, ua_2.date_format, ua_2.ssl_tls, ua_2.authentication, ua_2.email_signature, ua_2.kpi_permission, ua_2.default_candidate_view, ua_2.default_document_view, ua_2.display_training, ua_2.reset_password_token, ua_2.job_dashboard_default_view, ua_2.hash_salt, ua_2.hash_password, ua_2.hash_iterations, ua_2.display_intro, ua_2.pin_comment, ua_2.new_search, ua_2.view_mode, ua_2.email_connected, ua_2.position_of_box, ua_2.email_account_type, ua_2.freshdesk_user_id, ua_2.outgoing_authentication, ua_2.outgoing_encrypt_type, ua_2.outgoing_email_user_name, ua_2.outgoing_email_password, ua_2.incoming_email_user_name, ua_2.distance_unit, ua_2.mail_status, ua_2.mail_refresh_date, ua_2.exchange_host, ua_2.profile_picture_filename, ua_2.town_city, ua_2.branch, ua_2.division, ua_2.entity, ua_2.default_welcome_page_view, ua_2.candidate_quick_view_tab, ua_2.synced_to_cognito, ua_2.first_name, ua_2.last_name, ua_2.approver_flag
  • Index Cond: (ua_2.id = c_1.user_id)
  • Filter: ((ua_2.deleted_timestamp IS NULL) AND (ua_2.id <> '-10'::integer))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=172
33. 1.704 4,978.313 ↑ 2.5 6,277 1

Subquery Scan on *SELECT* 4 (cost=1,056,427.24..1,207,828.40 rows=15,459 width=40) (actual time=356.867..4,978.313 rows=6,277 loops=1)

  • Output: "*SELECT* 4".date_key, "*SELECT* 4".time_key, "*SELECT* 4".consultant_key, 20, "*SELECT* 4".uni_key, "*SELECT* 4".job_key, "*SELECT* 4".candidate_key, "*SELECT* 4".eventdate
  • Buffers: shared hit=2574 read=6301, temp read=2044 written=1022
34. 4,621.452 4,976.609 ↑ 2.5 6,277 1

Hash Join (cost=1,056,427.24..1,207,596.51 rows=15,459 width=32) (actual time=356.866..4,976.609 rows=6,277 loops=1)

  • Output: (to_char(fu_convert_time_zone(pc.rejected_date, ''::character varying, ua_3.timezone), 'YYYYMMDD'::text))::integer, (to_char(fu_convert_time_zone(pc.rejected_date, ''::character varying, ua_3.timezone), 'HH24MISSMS'::text))::integer, c_2.user_id, 20, pc.id, pc.position_description_id, pc.candidate_id, CURRENT_DATE
  • Hash Cond: (pc.candidate_id = c_2.candidate_key)
  • Buffers: shared hit=2574 read=6301, temp read=2044 written=1022
35. 6.624 7.721 ↓ 1.0 28,057 1

Bitmap Heap Scan on public.position_candidate pc (cost=528.11..3,106.42 rows=27,831 width=20) (actual time=1.248..7.721 rows=28,057 loops=1)

  • Output: pc.rejected_date, pc.id, pc.position_description_id, pc.candidate_id
  • Recheck Cond: (pc.rejected_date IS NOT NULL)
  • Heap Blocks: exact=1676
  • Buffers: shared hit=1755
36. 1.097 1.097 ↓ 1.0 28,057 1

Bitmap Index Scan on position_candidate_rejected_timestamp__idx (cost=0.00..521.15 rows=27,831 width=0) (actual time=1.097..1.097 rows=28,057 loops=1)

  • Index Cond: (pc.rejected_date IS NOT NULL)
  • Buffers: shared hit=79
37. 100.866 347.436 ↑ 745.1 27,356 1

Hash (cost=661,758.33..661,758.33 rows=20,383,824 width=28) (actual time=347.436..347.436 rows=27,356 loops=1)

  • Output: c_2.user_id, c_2.candidate_key, ua_3.timezone
  • Buckets: 65536 Batches: 512 Memory Usage: 524kB
  • Buffers: shared hit=819 read=6301
38. 25.491 246.570 ↑ 745.1 27,356 1

Hash Join (cost=21.85..661,758.33 rows=20,383,824 width=28) (actual time=0.126..246.570 rows=27,356 loops=1)

  • Output: c_2.user_id, c_2.candidate_key, ua_3.timezone
  • Inner Unique: true
  • Hash Cond: (c_2.user_id = ua_3.id)
  • Buffers: shared hit=819 read=6301
39. 22.011 220.984 ↑ 97.8 373,321 1

Subquery Scan on c_2 (cost=0.00..564,253.41 rows=36,514,908 width=12) (actual time=0.022..220.984 rows=373,321 loops=1)

  • Output: c_2.candidate_key, c_2.user_id
  • Filter: (c_2.user_id IS NOT NULL)
  • Buffers: shared hit=802 read=6301
40. 137.811 198.973 ↑ 98.3 373,321 1

ProjectSet (cost=0.00..197,269.41 rows=36,698,400 width=12) (actual time=0.021..198.973 rows=373,321 loops=1)

  • Output: candidate_extension_2.candidate_id, unnest(candidate_extension_2.candidate_owner_ids)
  • Buffers: shared hit=802 read=6301
41. 61.162 61.162 ↑ 1.0 366,115 1

Seq Scan on public.candidate_extension candidate_extension_2 (cost=0.00..11,025.03 rows=366,984 width=33) (actual time=0.019..61.162 rows=366,115 loops=1)

  • Output: candidate_extension_2.candidate_id, candidate_extension_2.candidate_owner_ids, candidate_extension_2.candidate_owner_names, candidate_extension_2.talent_pool_names, candidate_extension_2.talent_pool_ids, candidate_extension_2.job_types, candidate_extension_2.current_job_title, candidate_extension_2.current_employer, candidate_extension_2.candidate_next_availability_date, candidate_extension_2.available_next_week_status, candidate_extension_2.available_this_week_status, candidate_extension_2.last_activity_date
  • Filter: (candidate_extension_2.candidate_owner_ids IS NOT NULL)
  • Rows Removed by Filter: 26088
  • Buffers: shared hit=802 read=6301
42. 0.017 0.095 ↑ 1.0 139 1

Hash (cost=20.11..20.11 rows=139 width=20) (actual time=0.095..0.095 rows=139 loops=1)

  • Output: ua_3.timezone, ua_3.id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=17
43. 0.078 0.078 ↑ 1.0 139 1

Seq Scan on public.user_account ua_3 (cost=0.00..20.11 rows=139 width=20) (actual time=0.007..0.078 rows=139 loops=1)

  • Output: ua_3.timezone, ua_3.id
  • Filter: ((ua_3.deleted_timestamp IS NULL) AND (ua_3.id <> '-10'::integer))
  • Rows Removed by Filter: 110
  • Buffers: shared hit=17