explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bh2o

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 58,692.258 ↑ 1.0 10 1

Limit (cost=244,819.84..244,821.92 rows=10 width=1,113) (actual time=58,691.849..58,692.258 rows=10 loops=1)

2.          

CTE internal_sites

3. 224.534 224.534 ↓ 12,190.0 524,170 1

Index Scan using usr_scenario_key_list_scenario_id_active_idx on f_rpt_user_scenario_key_list (cost=0.57..206,182.44 rows=43 width=49) (actual time=0.037..224.534 rows=524,170 loops=1)

  • Index Cond: (((scenario_id)::text = '20201008171422_SNadkar2'::text) AND (is_active = true))
  • Filter: (is_active AND (scenario_version = '20201008171422'::bigint))
4.          

CTE most_recent_sqv_type

5. 0.001 682.066 ↓ 0.0 0 1

Subquery Scan on a (cost=1,195.71..1,195.75 rows=1 width=21) (actual time=682.066..682.066 rows=0 loops=1)

  • Filter: (a.rnk = 1)
6. 0.002 682.065 ↓ 0.0 0 1

WindowAgg (cost=1,195.71..1,195.73 rows=1 width=37) (actual time=682.065..682.065 rows=0 loops=1)

7. 0.012 682.063 ↓ 0.0 0 1

Sort (cost=1,195.71..1,195.72 rows=1 width=29) (actual time=682.063..682.063 rows=0 loops=1)

  • Sort Key: f_rpt_user_scenario_institutions_recommended_list_dtl.institution_id, f_rpt_user_scenario_institutions_recommended_list_dtl.actual_sqv_last_updated_time DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.001 682.051 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1.66..1,195.70 rows=1 width=29) (actual time=682.051..682.051 rows=0 loops=1)

9. 21.784 682.050 ↓ 0.0 0 1

Nested Loop (cost=1.38..1,179.38 rows=1 width=52) (actual time=682.050..682.050 rows=0 loops=1)

10. 106.309 183.606 ↓ 1,108.5 47,666 1

HashAggregate (cost=0.97..1.40 rows=43 width=218) (actual time=175.699..183.606 rows=47,666 loops=1)

  • Group Key: (internal_sites.institution_id)::text
11. 77.297 77.297 ↓ 12,190.0 524,170 1

CTE Scan on internal_sites (cost=0.00..0.86 rows=43 width=218) (actual time=0.007..77.297 rows=524,170 loops=1)

12. 476.660 476.660 ↓ 0.0 0 47,666

Index Scan using rpt_study_filter_institution_id_idx on f_rpt_user_scenario_institutions_recommended_list_dtl (cost=0.41..27.38 rows=1 width=52) (actual time=0.010..0.010 rows=0 loops=47,666)

  • Index Cond: ((institution_id)::text = (internal_sites.institution_id)::text)
  • Filter: (((actual_sqv_type)::text <> 'Declined'::text) AND ((institution_decision_indicator)::text = 'Selected'::text))
  • Rows Removed by Filter: 0
13. 0.000 0.000 ↓ 0.0 0

Index Scan using rpt_smry_scen_id_active_idx on f_rpt_user_scenario_smry (cost=0.28..8.30 rows=1 width=23) (never executed)

  • Index Cond: (((scenario_id)::text = (f_rpt_user_scenario_institutions_recommended_list_dtl.scenario_id)::text) AND (is_active = true))
  • Filter: (primary_scenario_flag AND is_active)
14.          

CTE num_of_investigators

15. 488.764 7,350.973 ↓ 3.1 27,982 1

HashAggregate (cost=36,760.38..36,851.37 rows=9,099 width=26) (actual time=7,347.237..7,350.973 rows=27,982 loops=1)

  • Group Key: f_rpt_study_institution_investigator.institution_id
16. 3,585.869 6,862.209 ↓ 316.3 2,877,833 1

Nested Loop (cost=1.53..36,692.14 rows=9,099 width=26) (actual time=164.745..6,862.209 rows=2,877,833 loops=1)

17. 106.501 178.050 ↓ 1,108.5 47,666 1

HashAggregate (cost=0.97..1.40 rows=43 width=218) (actual time=164.694..178.050 rows=47,666 loops=1)

  • Group Key: (internal_sites_1.institution_id)::text
18. 71.549 71.549 ↓ 12,190.0 524,170 1

CTE Scan on internal_sites internal_sites_1 (cost=0.00..0.86 rows=43 width=218) (actual time=0.010..71.549 rows=524,170 loops=1)

19. 3,098.290 3,098.290 ↑ 3.5 60 47,666

Index Scan using study_institution_inv_institution_id_cycle_id on f_rpt_study_institution_investigator (cost=0.56..851.15 rows=212 width=26) (actual time=0.019..0.065 rows=60 loops=47,666)

  • Index Cond: ((institution_id = (internal_sites_1.institution_id)::text) AND (cycle_id = '2020092513523283074'::text))
20. 0.054 58,692.250 ↑ 2.2 10 1

Unique (cost=590.28..594.85 rows=22 width=1,113) (actual time=58,691.847..58,692.250 rows=10 loops=1)

21. 32,702.257 58,692.196 ↑ 1.2 19 1

Sort (cost=590.28..590.34 rows=22 width=1,113) (actual time=58,691.847..58,692.196 rows=19 loops=1)

  • Sort Key: f_rpt_study_institution.institution_id, f_rpt_study_institution.institution_name, f_rpt_study_institution.institution_address, f_rpt_study_institution.institution_city, f_rpt_study_institution.institution_state, f_rpt_study_institution.country, f_rpt_study_institution.country_iso3, f_rpt_study_institution.institution_zip, f_rpt_study_institution.institution_latitude, f_rpt_study_institution.institution_longitude, f_rpt_study_institution.region, f_rpt_study_institution.institution_coordinator_name, f_rpt_study_institution.institution_coordinator_email, f_rpt_study_institution.institution_coordinator_phone, f_rpt_study_institution.completed_trials, f_rpt_study_institution.ongoing_trials, f_rpt_study_institution.overall_completed_trials, f_rpt_study_institution.overall_ongoing_trials, f_rpt_study_institution.subjects_enrolled, f_rpt_study_institution.startup_time, f_rpt_study_institution.lost_opportunity_time, f_rpt_study_institution.contract_execution_time, (sum(CASE WHEN (f_rpt_study_institution.site_default_rate_prec_ind = 1) THEN f_rpt_study_institution.site_default_rate_trial_flag ELSE 0 END) OVER (?)), (sum(CASE WHEN (f_rpt_study_institution.site_default_rate_prec_ind = 1) THEN f_rpt_study_institution.site_default_rate_trial_consider_flag ELSE 0 END) OVER (?)), (sum(CASE WHEN (f_rpt_study_institution.site_default_rate_prec_ind = 1) THEN f_rpt_study_institution.site_default_rate_trial_flag_pred ELSE 0 END) OVER (?)), f_rpt_study_institution.enrollment_duration, f_rpt_study_institution.enrollment_rate, f_rpt_study_institution.screen_failure_rate, f_rpt_study_institution.major_protocol_deviations, f_rpt_study_institution.variance_actual_vs_planned_site_open_date, f_rpt_study_institution.variance_actual_vs_planned_enrollment_rate, f_rpt_study_institution.norm_startup_time, f_rpt_study_institution.norm_enrollment_rate, f_rpt_study_institution.data_src_name, f_rpt_study_institution.completed_trials_prec_ind, f_rpt_study_institution.ongoing_trials_prec_ind, f_rpt_study_institution.overall_completed_trials_prec_ind, f_rpt_study_institution.overall_ongoing_trials_prec_ind, f_rpt_study_institution.subjects_enrolled_prec_ind, f_rpt_study_institution.startup_time_prec_ind, f_rpt_study_institution.lost_opportunity_time_prec_ind, f_rpt_study_institution.contract_execution_time_prec_ind, f_rpt_study_institution.site_default_rate_prec_ind, f_rpt_study_institution.enrollment_duration_prec_ind, f_rpt_study_institution.enrollment_rate_prec_ind, f_rpt_study_institution.screen_failure_rate_prec_ind, f_rpt_study_institution.major_protocol_deviations_prec_ind, f_rpt_study_institution.variance_actual_vs_planned_site_open_date_prec_ind, f_rpt_study_institution.variance_actual_vs_planned_enrollment_rate_prec_ind, (median(CASE WHEN (f_rpt_study_institution.startup_time_prec_ind = '1'::numeric) THEN f_rpt_study_institution.norm_startup_time ELSE NULL::numeric END) OVER (?)), (median(CASE WHEN (f_rpt_study_institution.startup_time_prec_ind = '1'::numeric) THEN f_rpt_study_institution.startup_time ELSE NULL::numeric END) OVER (?)), (max(f_rpt_study_institution.recent_site_qualified_candidate_date) OVER (?)), f_rpt_study_institution.total_ongoing_trials, f_rpt_study_institution.total_overall_ongoing_trials, f_rpt_study_institution.total_completed_trials, f_rpt_study_institution.total_overall_completed_trials, d.num_of_investigators, (sum(CASE WHEN (f_rpt_study_institution.site_activation_failure_prec_ind = 1) THEN f_rpt_study_institution.site_cancelled_flag ELSE 0 END) OVER (?)), (sum(CASE WHEN (f_rpt_study_institution.site_activation_failure_prec_ind = 1) THEN f_rpt_study_institution.site_activation_failure_status_flag ELSE 0 END) OVER (?)), f_rpt_study_institution.early_termination_rate, c.actual_sqv_type, f_rpt_study_institution.perc_subjects_enrolled_vs_committed, f_rpt_study_institution.subjects_enrolled_for_max, f_rpt_study_institution.completion_rate, f_rpt_study_institution.norm_screen_failure_rate, f_rpt_study_institution.norm_early_termination_rate, f_rpt_study_institution.early_termination_rate_prec_ind, f_rpt_study_institution.perc_subjects_enrolled_vs_committed_prec_ind, f_rpt_study_institution.subjects_enrolled_for_max_prec_ind, f_rpt_study_institution.completion_rate_prec_ind, f_rpt_study_institution.norm_startup_time_pred, f_rpt_study_institution.norm_enrollment_rate_pred, f_rpt_study_institution.norm_screen_failure_rate_pred, f_rpt_study_institution.norm_early_termination_rate_pred, f_rpt_study_institution.startup_time_pred, f_rpt_study_institution.enrollment_rate_pred, f_rpt_study_institution.screen_failure_rate_pred, f_rpt_study_institution.early_termination_rate_pred, f_rpt_study_institution.enrollment_rate_pred_flag, (sum(CASE WHEN ((f_rpt_study_institution.enrollment_rate_pred_flag = 0) AND (f_rpt_study_institution.enrollment_rate_prec_ind = '1'::numeric)) THEN 1 ELSE 0 END) OVER (?)), (sum(CASE WHEN ((f_rpt_study_institution.enrollment_rate_pred_flag = 1) AND (f_rpt_study_institution.enrollment_rate_prec_ind = '1'::numeric)) THEN 1 ELSE 0 END) OVER (?)), (sum(CASE WHEN (f_rpt_study_institution.enrollment_rate_prec_ind = '1'::numeric) THEN 1 ELSE 0 END) OVER (?))
  • Sort Method: external merge Disk: 241,456kB
22. 1,138.486 25,989.939 ↓ 23,388.6 514,550 1

WindowAgg (cost=589.24..589.79 rows=22 width=1,113) (actual time=24,708.028..25,989.939 rows=514,550 loops=1)

23. 1,087.587 24,851.453 ↓ 23,388.6 514,550 1

Sort (cost=589.24..589.30 rows=22 width=1,117) (actual time=24,707.458..24,851.453 rows=514,550 loops=1)

  • Sort Key: f_rpt_study_institution.country
  • Sort Method: external merge Disk: 232,200kB
24. 1,397.910 23,763.866 ↓ 23,388.6 514,550 1

WindowAgg (cost=587.30..588.75 rows=22 width=1,117) (actual time=21,219.578..23,763.866 rows=514,550 loops=1)

25. 378.068 22,365.956 ↓ 23,388.6 514,550 1

Merge Left Join (cost=587.30..587.43 rows=22 width=1,049) (actual time=21,219.498..22,365.956 rows=514,550 loops=1)

  • Merge Cond: (f_rpt_study_institution.institution_id = (c.institution_id)::text)
26. 3,082.996 21,305.813 ↓ 23,388.6 514,550 1

Sort (cost=587.27..587.33 rows=22 width=533) (actual time=20,537.413..21,305.813 rows=514,550 loops=1)

  • Sort Key: f_rpt_study_institution.institution_id
  • Sort Method: external merge Disk: 201,832kB
27. 737.270 18,222.817 ↓ 23,388.6 514,550 1

Hash Right Join (cost=370.61..586.78 rows=22 width=533) (actual time=17,474.863..18,222.817 rows=514,550 loops=1)

  • Hash Cond: (d.institution_id = f_rpt_study_institution.institution_id)
28. 7,358.256 7,358.256 ↓ 3.1 27,982 1

CTE Scan on num_of_investigators d (cost=0.00..181.98 rows=9,099 width=40) (actual time=7,347.240..7,358.256 rows=27,982 loops=1)

29. 614.396 10,127.291 ↓ 23,388.6 514,550 1

Hash (cost=370.34..370.34 rows=22 width=525) (actual time=10,127.291..10,127.291 rows=514,550 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 128 (originally 1) Memory Usage: 3,969kB
30. 1,252.884 9,512.895 ↓ 23,388.6 514,550 1

Nested Loop (cost=0.56..370.34 rows=22 width=525) (actual time=0.066..9,512.895 rows=514,550 loops=1)

31. 397.461 397.461 ↓ 12,190.0 524,170 1

CTE Scan on internal_sites b (cost=0.00..0.86 rows=43 width=218) (actual time=0.039..397.461 rows=524,170 loops=1)

32. 7,862.550 7,862.550 ↑ 1.0 1 524,170

Index Scan using study_institution_key_id_cycle_id on f_rpt_study_institution (cost=0.56..8.58 rows=1 width=556) (actual time=0.015..0.015 rows=1 loops=524,170)

  • Index Cond: ((study_institution_key = (b.study_institution_key)::text) AND (cycle_id = '2020092513523283074'::text))
33. 0.008 682.075 ↓ 0.0 0 1

Sort (cost=0.03..0.04 rows=1 width=634) (actual time=682.075..682.075 rows=0 loops=1)

  • Sort Key: c.institution_id
  • Sort Method: quicksort Memory: 25kB
34. 682.067 682.067 ↓ 0.0 0 1

CTE Scan on most_recent_sqv_type c (cost=0.00..0.02 rows=1 width=634) (actual time=682.067..682.067 rows=0 loops=1)

Planning time : 2.995 ms
Execution time : 58,786.875 ms