explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hmg7

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 9,039.084 ↑ 1.0 1 1

Aggregate (cost=2,622,994.06..2,622,994.07 rows=1 width=8) (actual time=9,039.084..9,039.084 rows=1 loops=1)

2.          

CTE saved_studies

3. 0.177 7.188 ↑ 3.6 224 1

Hash Left Join (cost=1,129.52..1,156.93 rows=812 width=404) (actual time=6.895..7.188 rows=224 loops=1)

  • Hash Cond: ((f_rpt_user_study_dtl.activity_id)::text = study_profile.activity_id)
4. 0.500 1.290 ↑ 3.6 224 1

HashAggregate (cost=690.16..698.28 rows=812 width=520) (actual time=1.131..1.290 rows=224 loops=1)

  • Group Key: f_rpt_user_study_dtl.user_study_id, f_rpt_user_study_dtl.user_study_version, f_rpt_user_study_dtl.activity_id, f_rpt_user_study_dtl.study_title, f_rpt_user_study_dtl.study_name, f_rpt_user_study_dtl.study_status, f_rpt_user_study_dtl.study_created_date, f_rpt_user_study_dtl.study_step, f_rpt_user_study_dtl.therapeutic_area, f_rpt_user_study_dtl.indication_name, f_rpt_user_study_dtl.drug_name, f_rpt_user_study_dtl.study_phase, f_rpt_user_study_dtl.study_stage, f_rpt_user_study_dtl.study_type, f_rpt_user_study_dtl.planned_enrollment_duration, f_rpt_user_study_dtl.planned_number_of_institutions, f_rpt_user_study_dtl.planned_number_of_subjects, f_rpt_user_study_dtl.institution_selection_completed_flag, f_rpt_user_study_dtl.country_selection_completed_flag, f_rpt_user_study_dtl.updated_protocol_id_flag, f_rpt_user_study_dtl.global_feasibility_lead, f_rpt_user_study_dtl.planned_first_site_open, f_rpt_user_study_dtl.planned_first_subject_screened, f_rpt_user_study_dtl.planned_first_subject_enrolled, f_rpt_user_study_dtl.planned_last_site_open, f_rpt_user_study_dtl.planned_last_subject_screened, f_rpt_user_study_dtl.planned_last_subject_enrolled, f_rpt_user_study_dtl.planned_last_subject_last_visit, f_rpt_user_study_dtl.primary_scenario_id, f_rpt_user_study_dtl.user_action_flag, f_rpt_user_study_dtl.created_by, f_rpt_user_study_dtl.created_time, f_rpt_user_study_dtl.last_updated_by, f_rpt_user_study_dtl.last_updated_time, f_rpt_user_study_dtl.is_active, ctl_admin_attribute_access.user_id
5. 0.349 0.790 ↑ 3.6 224 1

Hash Join (cost=29.80..617.08 rows=812 width=520) (actual time=0.072..0.790 rows=224 loops=1)

  • Hash Cond: (lower(btrim((unnest(string_to_array((f_rpt_user_study_dtl.therapeutic_area)::text, '|'::text))))) = lower(btrim((ctl_admin_attribute_access.attribute_value)::text)))
6. 0.246 0.407 ↑ 101.3 229 1

ProjectSet (cost=0.00..144.16 rows=23,200 width=1,576) (actual time=0.023..0.407 rows=229 loops=1)

7. 0.161 0.161 ↑ 1.0 229 1

Seq Scan on f_rpt_user_study_dtl (cost=0.00..25.84 rows=232 width=512) (actual time=0.010..0.161 rows=229 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 154
8. 0.015 0.034 ↑ 1.0 7 1

Hash (cost=29.72..29.72 rows=7 width=21) (actual time=0.034..0.034 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.005 0.019 ↑ 1.0 7 1

Bitmap Heap Scan on ctl_admin_attribute_access (cost=4.36..29.72 rows=7 width=21) (actual time=0.018..0.019 rows=7 loops=1)

  • Recheck Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
  • Heap Blocks: exact=1
10. 0.014 0.014 ↑ 1.0 7 1

Bitmap Index Scan on attr_acc_usr_id_attr_name_indx (cost=0.00..4.36 rows=7 width=0) (actual time=0.014..0.014 rows=7 loops=1)

  • Index Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
11. 3.358 5.721 ↑ 1.0 7,126 1

Hash (cost=350.27..350.27 rows=7,127 width=234) (actual time=5.721..5.721 rows=7,126 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 1,908kB
12. 2.363 2.363 ↑ 1.0 7,126 1

Seq Scan on f_rpt_study_profile study_profile (cost=0.00..350.27 rows=7,127 width=234) (actual time=0.004..2.363 rows=7,126 loops=1)

13.          

CTE smry_data

14. 0.260 9.477 ↓ 2.5 639 1

HashAggregate (cost=5,642.17..5,644.75 rows=258 width=46) (actual time=9.405..9.477 rows=639 loops=1)

  • Group Key: f_rpt_user_scenario_smry.user_study_id, f_rpt_user_scenario_smry.scenario_id
15. 1.593 9.217 ↑ 4.0 639 1

Group (cost=5,255.17..5,603.47 rows=2,580 width=2,355) (actual time=7.587..9.217 rows=639 loops=1)

  • Group Key: f_rpt_user_scenario_smry.scenario_id, f_rpt_user_scenario_smry.scenario_name, f_rpt_user_scenario_smry.scenario_version, f_rpt_user_scenario_smry.scenario_created_date, f_rpt_user_scenario_smry.stage, f_rpt_user_scenario_smry.sub_step, f_rpt_user_scenario_smry.study_status, f_rpt_user_scenario_smry.global_feasibility_lead, f_rpt_user_scenario_smry.scenario_status, f_rpt_user_scenario_smry.activity_id, f_rpt_user_scenario_smry.study_title, f_rpt_user_scenario_smry.study_phase, f_rpt_user_scenario_smry.therapeutic_area, f_rpt_user_scenario_smry.drug_name, f_rpt_user_scenario_smry.patient_segment, f_rpt_user_scenario_smry.indication_name, f_rpt_user_scenario_smry.age_range, f_rpt_user_scenario_smry.study_type, f_rpt_user_scenario_smry.included_countries, f_rpt_user_scenario_smry.excluded_countries, f_rpt_user_scenario_smry.cda_required, f_rpt_user_scenario_smry.cda_rationale, f_rpt_user_scenario_smry.nct_id_exclusion, f_rpt_user_scenario_smry.nct_id_inclusion, f_rpt_user_scenario_smry.trial_start_date_within, f_rpt_user_scenario_smry.planned_number_of_institutions, f_rpt_user_scenario_smry.planned_number_of_subjects, f_rpt_user_scenario_smry.planned_enrollment_duration, f_rpt_user_scenario_smry.default_data_driven_list_columns, f_rpt_user_scenario_smry.default_recommended_list_columns, f_rpt_user_scenario_smry.default_selected_list_columns, f_rpt_user_scenario_smry.default_investigator_list_columns, f_rpt_user_scenario_smry.country_commitments_completion_flag, f_rpt_user_scenario_smry.updated_protocol_id_flag, f_rpt_user_scenario_smry.country_outreach_status, f_rpt_user_scenario_smry.created_by, f_rpt_user_scenario_smry.created_time, f_rpt_user_scenario_smry.last_updated_by, f_rpt_user_scenario_smry.last_updated_time, f_rpt_user_scenario_smry.is_active, f_rpt_user_scenario_smry.cycle_id, f_rpt_user_scenario_smry.primary_scenario_flag, f_rpt_user_scenario_smry.scenario_shared_flag, f_rpt_user_scenario_smry.sponsor_name, f_rpt_user_scenario_smry.user_study_id, f_rpt_user_scenario_smry.scenario_locked_flag, f_rpt_user_scenario_smry.shared_users_list, f_rpt_user_scenario_smry.metric_estimation_flag, f_rpt_user_scenario_smry.actual_studies_count_threshold, f_rpt_user_scenario_smry.default_initial_list_columns, f_rpt_user_scenario_smry.min_num_of_countries, f_rpt_user_scenario_smry.perc_extra_countries, ctl_admin_attribute_access_1.user_id
16. 3.562 7.624 ↑ 4.0 641 1

Sort (cost=5,255.17..5,261.62 rows=2,580 width=2,355) (actual time=7.584..7.624 rows=641 loops=1)

  • Sort Key: f_rpt_user_scenario_smry.scenario_id, f_rpt_user_scenario_smry.scenario_name, f_rpt_user_scenario_smry.scenario_version, f_rpt_user_scenario_smry.scenario_created_date, f_rpt_user_scenario_smry.stage, f_rpt_user_scenario_smry.sub_step, f_rpt_user_scenario_smry.study_status, f_rpt_user_scenario_smry.global_feasibility_lead, f_rpt_user_scenario_smry.scenario_status, f_rpt_user_scenario_smry.activity_id, f_rpt_user_scenario_smry.study_title, f_rpt_user_scenario_smry.study_phase, f_rpt_user_scenario_smry.therapeutic_area, f_rpt_user_scenario_smry.drug_name, f_rpt_user_scenario_smry.patient_segment, f_rpt_user_scenario_smry.indication_name, f_rpt_user_scenario_smry.age_range, f_rpt_user_scenario_smry.study_type, f_rpt_user_scenario_smry.included_countries, f_rpt_user_scenario_smry.excluded_countries, f_rpt_user_scenario_smry.cda_required, f_rpt_user_scenario_smry.cda_rationale, f_rpt_user_scenario_smry.nct_id_exclusion, f_rpt_user_scenario_smry.nct_id_inclusion, f_rpt_user_scenario_smry.trial_start_date_within, f_rpt_user_scenario_smry.planned_number_of_institutions, f_rpt_user_scenario_smry.planned_number_of_subjects, f_rpt_user_scenario_smry.planned_enrollment_duration, f_rpt_user_scenario_smry.default_data_driven_list_columns, f_rpt_user_scenario_smry.default_recommended_list_columns, f_rpt_user_scenario_smry.default_selected_list_columns, f_rpt_user_scenario_smry.default_investigator_list_columns, f_rpt_user_scenario_smry.country_commitments_completion_flag, f_rpt_user_scenario_smry.updated_protocol_id_flag, f_rpt_user_scenario_smry.country_outreach_status, f_rpt_user_scenario_smry.created_by, f_rpt_user_scenario_smry.created_time, f_rpt_user_scenario_smry.last_updated_by, f_rpt_user_scenario_smry.last_updated_time, f_rpt_user_scenario_smry.is_active, f_rpt_user_scenario_smry.cycle_id, f_rpt_user_scenario_smry.primary_scenario_flag, f_rpt_user_scenario_smry.scenario_shared_flag, f_rpt_user_scenario_smry.sponsor_name, f_rpt_user_scenario_smry.user_study_id, f_rpt_user_scenario_smry.scenario_locked_flag, f_rpt_user_scenario_smry.shared_users_list, f_rpt_user_scenario_smry.metric_estimation_flag, f_rpt_user_scenario_smry.actual_studies_count_threshold, f_rpt_user_scenario_smry.default_initial_list_columns, f_rpt_user_scenario_smry.min_num_of_countries, f_rpt_user_scenario_smry.perc_extra_countries
  • Sort Method: quicksort Memory: 1,317kB
17. 1.061 4.062 ↑ 4.0 641 1

Hash Join (cost=29.80..2,480.47 rows=2,580 width=2,355) (actual time=0.055..4.062 rows=641 loops=1)

  • Hash Cond: (lower(btrim((unnest(string_to_array((f_rpt_user_scenario_smry.therapeutic_area)::text, '|'::text))))) = lower(btrim((ctl_admin_attribute_access_1.attribute_value)::text)))
18. 0.851 2.976 ↑ 114.6 643 1

ProjectSet (cost=0.00..1,042.99 rows=73,700 width=2,379) (actual time=0.015..2.976 rows=643 loops=1)

19. 2.125 2.125 ↑ 1.1 643 1

Seq Scan on f_rpt_user_scenario_smry (cost=0.00..667.12 rows=737 width=2,347) (actual time=0.004..2.125 rows=643 loops=1)

  • Filter: (is_active AND (user_study_id IS NOT NULL))
  • Rows Removed by Filter: 2,015
20. 0.011 0.025 ↑ 1.0 7 1

Hash (cost=29.72..29.72 rows=7 width=21) (actual time=0.025..0.025 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.004 0.014 ↑ 1.0 7 1

Bitmap Heap Scan on ctl_admin_attribute_access ctl_admin_attribute_access_1 (cost=4.36..29.72 rows=7 width=21) (actual time=0.013..0.014 rows=7 loops=1)

  • Recheck Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
  • Heap Blocks: exact=1
22. 0.010 0.010 ↑ 1.0 7 1

Bitmap Index Scan on attr_acc_usr_id_attr_name_indx (cost=0.00..4.36 rows=7 width=0) (actual time=0.010..0.010 rows=7 loops=1)

  • Index Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
23.          

CTE usr_study_data

24. 0.658 10.322 ↑ 5.9 69 1

CTE Scan on saved_studies saved_studies_1 (cost=5.80..24.07 rows=406 width=118) (actual time=9.793..10.322 rows=69 loops=1)

  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 155
25.          

SubPlan (for CTE Scan)

26. 9.664 9.664 ↓ 2.5 639 1

CTE Scan on smry_data (cost=0.00..5.16 rows=258 width=118) (actual time=9.406..9.664 rows=639 loops=1)

27.          

CTE metric_data

28. 74.361 8,968.089 ↓ 1.2 472 1

HashAggregate (cost=2,597,117.38..2,597,121.17 rows=379 width=23) (actual time=8,968.026..8,968.089 rows=472 loops=1)

  • Group Key: f_rpt_user_scenario_study_metrics_country.scenario_id
29. 1,710.479 8,893.728 ↑ 23.9 434,606 1

Hash Join (cost=395.36..2,571,163.71 rows=10,381,468 width=23) (actual time=0.440..8,893.728 rows=434,606 loops=1)

  • Hash Cond: ((f_rpt_user_scenario_study_metrics_country.country_name)::text = (ctl_admin_attribute_access_2.attribute_value)::text)
30. 3,784.542 7,183.222 ↓ 2.0 22,034,710 1

Hash Join (cost=10.30..771,648.63 rows=11,116,822 width=30) (actual time=0.403..7,183.222 rows=22,034,710 loops=1)

  • Hash Cond: ((f_rpt_user_scenario_study_metrics_country.scenario_id)::text = (smry_data_1.scenario_id)::text)
31. 3,398.301 3,398.301 ↑ 1.0 22,186,782 1

Seq Scan on f_rpt_user_scenario_study_metrics_country (cost=0.00..589,600.36 rows=22,233,644 width=30) (actual time=0.007..3,398.301 rows=22,186,782 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 2,961,553
32. 0.084 0.379 ↓ 3.2 639 1

Hash (cost=7.80..7.80 rows=200 width=118) (actual time=0.379..0.379 rows=639 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
33. 0.221 0.295 ↓ 3.2 639 1

HashAggregate (cost=5.80..7.80 rows=200 width=118) (actual time=0.227..0.295 rows=639 loops=1)

  • Group Key: (smry_data_1.scenario_id)::text
34. 0.074 0.074 ↓ 2.5 639 1

CTE Scan on smry_data smry_data_1 (cost=0.00..5.16 rows=258 width=118) (actual time=0.001..0.074 rows=639 loops=1)

35. 0.004 0.027 ↑ 11.4 21 1

Hash (cost=382.05..382.05 rows=240 width=13) (actual time=0.027..0.027 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.009 0.023 ↑ 11.4 21 1

Bitmap Heap Scan on ctl_admin_attribute_access ctl_admin_attribute_access_2 (cost=10.75..382.05 rows=240 width=13) (actual time=0.020..0.023 rows=21 loops=1)

  • Recheck Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'country'::text))
  • Heap Blocks: exact=2
37. 0.014 0.014 ↑ 11.4 21 1

Bitmap Index Scan on attr_acc_usr_id_attr_name_indx (cost=0.00..10.69 rows=240 width=0) (actual time=0.014..0.014 rows=21 loops=1)

  • Index Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'country'::text))
38. 7.619 9,039.067 ↑ 2.7 224 1

CTE Scan on saved_studies (cost=19,025.32..19,045.62 rows=609 width=24) (actual time=9,038.847..9,039.067 rows=224 loops=1)

  • Filter: ((hashed SubPlan 6) OR (hashed SubPlan 9))
39.          

SubPlan (for CTE Scan)

40. 10.343 10.343 ↑ 5.9 69 1

CTE Scan on usr_study_data (cost=0.00..8.12 rows=406 width=118) (actual time=9.794..10.343 rows=69 loops=1)

41. 1.038 9,021.105 ↑ 2.7 2,653 1

Subquery Scan on f_rpt_user_scenario_smry_vw (cost=17,574.58..18,998.38 rows=7,119 width=23) (actual time=9,012.433..9,021.105 rows=2,653 loops=1)

  • Filter: ((hashed SubPlan 7) OR (NOT (hashed SubPlan 8)))
42. 5.079 51.819 ↑ 3.6 2,653 1

Group (cost=17,557.53..18,838.95 rows=9,492 width=2,355) (actual time=43.966..51.819 rows=2,653 loops=1)

  • Group Key: f_rpt_user_scenario_smry_1.scenario_id, f_rpt_user_scenario_smry_1.scenario_name, f_rpt_user_scenario_smry_1.scenario_version, f_rpt_user_scenario_smry_1.scenario_created_date, f_rpt_user_scenario_smry_1.stage, f_rpt_user_scenario_smry_1.sub_step, f_rpt_user_scenario_smry_1.study_status, f_rpt_user_scenario_smry_1.global_feasibility_lead, f_rpt_user_scenario_smry_1.scenario_status, f_rpt_user_scenario_smry_1.activity_id, f_rpt_user_scenario_smry_1.study_title, f_rpt_user_scenario_smry_1.study_phase, f_rpt_user_scenario_smry_1.therapeutic_area, f_rpt_user_scenario_smry_1.drug_name, f_rpt_user_scenario_smry_1.patient_segment, f_rpt_user_scenario_smry_1.indication_name, f_rpt_user_scenario_smry_1.age_range, f_rpt_user_scenario_smry_1.study_type, f_rpt_user_scenario_smry_1.included_countries, f_rpt_user_scenario_smry_1.excluded_countries, f_rpt_user_scenario_smry_1.cda_required, f_rpt_user_scenario_smry_1.cda_rationale, f_rpt_user_scenario_smry_1.nct_id_exclusion, f_rpt_user_scenario_smry_1.nct_id_inclusion, f_rpt_user_scenario_smry_1.trial_start_date_within, f_rpt_user_scenario_smry_1.planned_number_of_institutions, f_rpt_user_scenario_smry_1.planned_number_of_subjects, f_rpt_user_scenario_smry_1.planned_enrollment_duration, f_rpt_user_scenario_smry_1.default_data_driven_list_columns, f_rpt_user_scenario_smry_1.default_recommended_list_columns, f_rpt_user_scenario_smry_1.default_selected_list_columns, f_rpt_user_scenario_smry_1.default_investigator_list_columns, f_rpt_user_scenario_smry_1.country_commitments_completion_flag, f_rpt_user_scenario_smry_1.updated_protocol_id_flag, f_rpt_user_scenario_smry_1.country_outreach_status, f_rpt_user_scenario_smry_1.created_by, f_rpt_user_scenario_smry_1.created_time, f_rpt_user_scenario_smry_1.last_updated_by, f_rpt_user_scenario_smry_1.last_updated_time, f_rpt_user_scenario_smry_1.is_active, f_rpt_user_scenario_smry_1.cycle_id, f_rpt_user_scenario_smry_1.primary_scenario_flag, f_rpt_user_scenario_smry_1.scenario_shared_flag, f_rpt_user_scenario_smry_1.sponsor_name, f_rpt_user_scenario_smry_1.user_study_id, f_rpt_user_scenario_smry_1.scenario_locked_flag, f_rpt_user_scenario_smry_1.shared_users_list, f_rpt_user_scenario_smry_1.metric_estimation_flag, f_rpt_user_scenario_smry_1.actual_studies_count_threshold, f_rpt_user_scenario_smry_1.default_initial_list_columns, f_rpt_user_scenario_smry_1.min_num_of_countries, f_rpt_user_scenario_smry_1.perc_extra_countries, ctl_admin_attribute_access_3.user_id
43. 36.674 46.740 ↑ 3.6 2,655 1

Sort (cost=17,557.53..17,581.26 rows=9,492 width=2,355) (actual time=43.961..46.740 rows=2,655 loops=1)

  • Sort Key: f_rpt_user_scenario_smry_1.scenario_id, f_rpt_user_scenario_smry_1.scenario_name, f_rpt_user_scenario_smry_1.scenario_version, f_rpt_user_scenario_smry_1.scenario_created_date, f_rpt_user_scenario_smry_1.stage, f_rpt_user_scenario_smry_1.sub_step, f_rpt_user_scenario_smry_1.study_status, f_rpt_user_scenario_smry_1.global_feasibility_lead, f_rpt_user_scenario_smry_1.scenario_status, f_rpt_user_scenario_smry_1.activity_id, f_rpt_user_scenario_smry_1.study_title, f_rpt_user_scenario_smry_1.study_phase, f_rpt_user_scenario_smry_1.therapeutic_area, f_rpt_user_scenario_smry_1.drug_name, f_rpt_user_scenario_smry_1.patient_segment, f_rpt_user_scenario_smry_1.indication_name, f_rpt_user_scenario_smry_1.age_range, f_rpt_user_scenario_smry_1.study_type, f_rpt_user_scenario_smry_1.included_countries, f_rpt_user_scenario_smry_1.excluded_countries, f_rpt_user_scenario_smry_1.cda_required, f_rpt_user_scenario_smry_1.cda_rationale, f_rpt_user_scenario_smry_1.nct_id_exclusion, f_rpt_user_scenario_smry_1.nct_id_inclusion, f_rpt_user_scenario_smry_1.trial_start_date_within, f_rpt_user_scenario_smry_1.planned_number_of_institutions, f_rpt_user_scenario_smry_1.planned_number_of_subjects, f_rpt_user_scenario_smry_1.planned_enrollment_duration, f_rpt_user_scenario_smry_1.default_data_driven_list_columns, f_rpt_user_scenario_smry_1.default_recommended_list_columns, f_rpt_user_scenario_smry_1.default_selected_list_columns, f_rpt_user_scenario_smry_1.default_investigator_list_columns, f_rpt_user_scenario_smry_1.country_commitments_completion_flag, f_rpt_user_scenario_smry_1.updated_protocol_id_flag, f_rpt_user_scenario_smry_1.country_outreach_status, f_rpt_user_scenario_smry_1.created_by, f_rpt_user_scenario_smry_1.created_time, f_rpt_user_scenario_smry_1.last_updated_by, f_rpt_user_scenario_smry_1.last_updated_time, f_rpt_user_scenario_smry_1.is_active, f_rpt_user_scenario_smry_1.cycle_id, f_rpt_user_scenario_smry_1.primary_scenario_flag, f_rpt_user_scenario_smry_1.scenario_shared_flag, f_rpt_user_scenario_smry_1.sponsor_name, f_rpt_user_scenario_smry_1.user_study_id, f_rpt_user_scenario_smry_1.scenario_locked_flag, f_rpt_user_scenario_smry_1.shared_users_list, f_rpt_user_scenario_smry_1.metric_estimation_flag, f_rpt_user_scenario_smry_1.actual_studies_count_threshold, f_rpt_user_scenario_smry_1.default_initial_list_columns, f_rpt_user_scenario_smry_1.min_num_of_countries, f_rpt_user_scenario_smry_1.perc_extra_countries
  • Sort Method: external merge Disk: 4,392kB
44. 4.001 10.066 ↑ 3.6 2,655 1

Hash Join (cost=29.80..7,259.96 rows=9,492 width=2,355) (actual time=0.053..10.066 rows=2,655 loops=1)

  • Hash Cond: (lower(btrim((unnest(string_to_array((f_rpt_user_scenario_smry_1.therapeutic_area)::text, '|'::text))))) = lower(btrim((ctl_admin_attribute_access_3.attribute_value)::text)))
45. 5.428 6.041 ↑ 102.1 2,657 1

ProjectSet (cost=0.00..2,050.24 rows=271,200 width=2,379) (actual time=0.013..6.041 rows=2,657 loops=1)

46. 0.613 0.613 ↑ 1.0 2,658 1

Seq Scan on f_rpt_user_scenario_smry f_rpt_user_scenario_smry_1 (cost=0.00..667.12 rows=2,712 width=2,347) (actual time=0.003..0.613 rows=2,658 loops=1)

47. 0.011 0.024 ↑ 1.0 7 1

Hash (cost=29.72..29.72 rows=7 width=21) (actual time=0.024..0.024 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.004 0.013 ↑ 1.0 7 1

Bitmap Heap Scan on ctl_admin_attribute_access ctl_admin_attribute_access_3 (cost=4.36..29.72 rows=7 width=21) (actual time=0.012..0.013 rows=7 loops=1)

  • Recheck Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
  • Heap Blocks: exact=1
49. 0.009 0.009 ↑ 1.0 7 1

Bitmap Index Scan on attr_acc_usr_id_attr_name_indx (cost=0.00..4.36 rows=7 width=0) (actual time=0.009..0.009 rows=7 loops=1)

  • Index Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
50.          

SubPlan (for Subquery Scan)

51. 8,968.210 8,968.210 ↓ 1.2 472 1

CTE Scan on metric_data (cost=0.00..7.58 rows=379 width=118) (actual time=8,968.028..8,968.210 rows=472 loops=1)

52. 0.038 0.038 ↓ 1.2 472 1

CTE Scan on metric_data metric_data_1 (cost=0.00..7.58 rows=379 width=118) (actual time=0.000..0.038 rows=472 loops=1)

Planning time : 2.321 ms
Execution time : 9,040.971 ms