explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y4fm

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 10,670.646 ↑ 1.0 40 1

Limit (cost=8,256,405.92..8,256,406.02 rows=40 width=719) (actual time=10,670.636..10,670.646 rows=40 loops=1)

2.          

CTE saved_studies

3. 0.191 7.181 ↑ 3.6 218 1

Hash Left Join (cost=1,101.00..1,131.11 rows=777 width=318) (actual time=6.893..7.181 rows=218 loops=1)

  • Hash Cond: ((f_rpt_user_study_dtl.activity_id)::text = study_profile.activity_id)
4. 0.492 1.299 ↑ 3.6 218 1

HashAggregate (cost=661.64..669.41 rows=777 width=520) (actual time=1.159..1.299 rows=218 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.344 0.807 ↑ 3.6 218 1

Hash Join (cost=29.80..591.71 rows=777 width=520) (actual time=0.114..0.807 rows=218 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.234 0.383 ↑ 99.6 223 1

ProjectSet (cost=0.00..137.89 rows=22,200 width=1,576) (actual time=0.015..0.383 rows=223 loops=1)

7. 0.149 0.149 ↓ 1.0 223 1

Seq Scan on f_rpt_user_study_dtl (cost=0.00..24.67 rows=222 width=512) (actual time=0.006..0.149 rows=223 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 146
8. 0.064 0.080 ↑ 1.0 7 1

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

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

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

  • Recheck Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'therapeutic_area'::text))
  • Heap Blocks: exact=1
10. 0.011 0.011 ↑ 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.011..0.011 rows=7 loops=1)

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 1,908kB
12. 2.332 2.332 ↑ 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.332 rows=7,126 loops=1)

13.          

CTE smry_data

14. 0.279 9.493 ↓ 2.5 625 1

HashAggregate (cost=5,513.05..5,515.57 rows=252 width=46) (actual time=9.421..9.493 rows=625 loops=1)

  • Group Key: f_rpt_user_scenario_smry.user_study_id, f_rpt_user_scenario_smry.scenario_id
15. 1.563 9.214 ↑ 4.0 625 1

Group (cost=5,135.05..5,475.25 rows=2,520 width=2,355) (actual time=7.617..9.214 rows=625 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.681 7.651 ↑ 4.0 627 1

Sort (cost=5,135.05..5,141.35 rows=2,520 width=2,355) (actual time=7.612..7.651 rows=627 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,288kB
17. 1.044 3.970 ↑ 4.0 627 1

Hash Join (cost=29.80..2,423.68 rows=2,520 width=2,355) (actual time=0.055..3.970 rows=627 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.853 2.902 ↑ 114.5 629 1

ProjectSet (cost=0.00..1,018.68 rows=72,000 width=2,379) (actual time=0.012..2.902 rows=629 loops=1)

19. 2.049 2.049 ↑ 1.1 629 1

Seq Scan on f_rpt_user_scenario_smry (cost=0.00..651.48 rows=720 width=2,347) (actual time=0.003..2.049 rows=629 loops=1)

  • Filter: (is_active AND (user_study_id IS NOT NULL))
  • Rows Removed by Filter: 1,986
20. 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
21. 0.004 0.013 ↑ 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.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
22. 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))
23.          

CTE usr_study_data

24. 0.669 10.344 ↑ 5.6 69 1

CTE Scan on saved_studies saved_studies_1 (cost=5.67..23.15 rows=388 width=118) (actual time=9.807..10.344 rows=69 loops=1)

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

SubPlan (for CTE Scan)

26. 9.675 9.675 ↓ 2.5 625 1

CTE Scan on smry_data (cost=0.00..5.04 rows=252 width=118) (actual time=9.424..9.675 rows=625 loops=1)

27.          

CTE metric_data

28. 59.648 10,598.382 ↓ 1.3 464 1

HashAggregate (cost=8,231,119.83..8,231,123.28 rows=345 width=23) (actual time=10,598.322..10,598.382 rows=464 loops=1)

  • Group Key: f_rpt_user_scenario_study_metrics_dtl.scenario_id
29. 1,576.650 10,538.734 ↑ 5.7 431,842 1

Hash Join (cost=8,095,297.27..8,224,972.40 rows=2,458,972 width=23) (actual time=9,089.298..10,538.734 rows=431,842 loops=1)

  • Hash Cond: ((ctl_admin_attribute_access_2.attribute_value)::text = (btrim((unnest(string_to_array(rtrim(ltrim(f_rpt_user_scenario_study_metrics_dtl.list_of_countries, '|'::text), '|'::text), '|'::text))))))
30. 0.020 0.045 ↑ 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.035..0.045 rows=21 loops=1)

  • Recheck Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'country'::text))
  • Heap Blocks: exact=2
31. 0.025 0.025 ↑ 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.025..0.025 rows=21 loops=1)

  • Index Cond: (((user_id)::text = 'SKar9'::text) AND ((attribute_name)::text = 'country'::text))
32. 2,219.029 8,962.039 ↓ 4.0 10,475,400 1

Hash (cost=8,036,657.15..8,036,657.15 rows=2,633,150 width=55) (actual time=8,962.039..8,962.039 rows=10,475,400 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 512 (originally 128) Memory Usage: 169,327kB
33. 1,994.110 6,743.010 ↓ 4.0 10,475,400 1

Hash Join (cost=1,010.17..8,036,657.15 rows=2,633,150 width=55) (actual time=2.065..6,743.010 rows=10,475,400 loops=1)

  • Hash Cond: ((f_rpt_user_scenario_study_metrics_dtl.scenario_id)::text = (smry_data_1.scenario_id)::text)
34. 0.000 4,748.498 ↓ 2.0 10,565,564 1

Gather (cost=1,000.00..7,940,866.16 rows=5,266,299 width=1,229) (actual time=1.634..4,748.498 rows=10,565,564 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
35. 1,218.208 5,081.481 ↑ 62.3 3,521,855 3 / 3

Result (cost=0.00..7,413,236.26 rows=219,429,100 width=1,229) (actual time=0.018..5,081.481 rows=3,521,855 loops=3)

36. 2,207.831 3,863.273 ↑ 62.3 3,521,855 3 / 3

ProjectSet (cost=0.00..2,476,081.51 rows=219,429,100 width=55) (actual time=0.013..3,863.273 rows=3,521,855 loops=3)

37. 1,655.442 1,655.442 ↑ 1.1 1,991,159 3 / 3

Parallel Seq Scan on f_rpt_user_scenario_study_metrics_dtl (cost=0.00..1,346,021.64 rows=2,194,291 width=52) (actual time=0.005..1,655.442 rows=1,991,159 loops=3)

  • Filter: (is_active AND is_active)
  • Rows Removed by Filter: 260,486
38. 0.097 0.402 ↓ 3.1 625 1

Hash (cost=7.67..7.67 rows=200 width=118) (actual time=0.402..0.402 rows=625 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
39. 0.230 0.305 ↓ 3.1 625 1

HashAggregate (cost=5.67..7.67 rows=200 width=118) (actual time=0.238..0.305 rows=625 loops=1)

  • Group Key: (smry_data_1.scenario_id)::text
40. 0.075 0.075 ↓ 2.5 625 1

CTE Scan on smry_data smry_data_1 (cost=0.00..5.04 rows=252 width=118) (actual time=0.001..0.075 rows=625 loops=1)

41. 0.260 10,670.637 ↑ 14.6 40 1

Sort (cost=18,612.80..18,614.26 rows=583 width=719) (actual time=10,670.635..10,670.637 rows=40 loops=1)

  • Sort Key: saved_studies.activity_id
  • Sort Method: top-N heapsort Memory: 58kB
42. 7.476 10,670.377 ↑ 2.7 218 1

CTE Scan on saved_studies (cost=18,574.95..18,594.37 rows=583 width=719) (actual time=10,670.279..10,670.377 rows=218 loops=1)

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

SubPlan (for CTE Scan)

44. 10.362 10.362 ↑ 5.6 69 1

CTE Scan on usr_study_data (cost=0.00..7.76 rows=388 width=118) (actual time=9.808..10.362 rows=69 loops=1)

45. 1.015 10,652.539 ↑ 2.7 2,610 1

Subquery Scan on f_rpt_user_scenario_smry_vw (cost=17,158.64..18,548.84 rows=6,951 width=23) (actual time=10,644.357..10,652.539 rows=2,610 loops=1)

  • Filter: ((hashed SubPlan 7) OR (NOT (hashed SubPlan 8)))
46. 4.836 52.994 ↑ 3.6 2,610 1

Group (cost=17,143.11..18,394.29 rows=9,268 width=2,355) (actual time=45.604..52.994 rows=2,610 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
47. 38.239 48.158 ↑ 3.5 2,612 1

Sort (cost=17,143.11..17,166.28 rows=9,268 width=2,355) (actual time=45.599..48.158 rows=2,612 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,320kB
48. 4.010 9.919 ↑ 3.5 2,612 1

Hash Join (cost=29.80..7,089.44 rows=9,268 width=2,355) (actual time=0.055..9.919 rows=2,612 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)))
49. 5.235 5.884 ↑ 101.3 2,614 1

ProjectSet (cost=0.00..2,001.96 rows=264,800 width=2,379) (actual time=0.015..5.884 rows=2,614 loops=1)

50. 0.649 0.649 ↑ 1.0 2,615 1

Seq Scan on f_rpt_user_scenario_smry f_rpt_user_scenario_smry_1 (cost=0.00..651.48 rows=2,648 width=2,347) (actual time=0.003..0.649 rows=2,615 loops=1)

51. 0.012 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
52. 0.003 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
53. 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))
54.          

SubPlan (for Subquery Scan)

55. 10,598.495 10,598.495 ↓ 1.3 464 1

CTE Scan on metric_data (cost=0.00..6.90 rows=345 width=118) (actual time=10,598.327..10,598.495 rows=464 loops=1)

56. 0.035 0.035 ↓ 1.3 464 1

CTE Scan on metric_data metric_data_1 (cost=0.00..6.90 rows=345 width=118) (actual time=0.000..0.035 rows=464 loops=1)

Planning time : 3.650 ms
Execution time : 10,673.198 ms