Re: [EXT] YNT: Need help tuning a query
Amn Ojee Uw <amnojeeuw@gmail.com>
From: Amn Ojee Uw <amnojeeuw@gmail.com>
To: pgsql-general@lists.postgresql.org
Date: 2023-09-30T09:58:27Z
Lists: pgsql-general
Wow!! This is what I call cryptic!! On 9/29/23 2:46 a.m., Vladimir Sitnikov wrote: > Oh, I misplaced the added where conditions. > It should have been as follows, however, the overall idea is the same > > --- orignial.sql > +++ tuned_v2.sql > @@ -83,6 +83,7 @@ > AND (judg1.jrt_opt_out_flag <> 'Y' OR > judg1.jrt_opt_out_flag IS NULL) > ) sub0 > LEFT OUTER JOIN > + LATERAL > ( SELECT sub4.case_year_number, > sub4.judge_wld_id, > sub4.judge_id, > @@ -99,6 +100,7 @@ > jrtf1.higher_judge_id, > jrtf1.case_document_id > ) sub4 > + WHERE sub4.judge_id = sub0.judge_id > GROUP BY sub4.case_year_number, > sub4.judge_wld_id, > sub4.judge_id, > @@ -106,6 +108,7 @@ > ) sub1 > ON sub1.judge_id = sub0.judge_id > LEFT OUTER JOIN > + LATERAL > (SELECT sub5.case_year_number, > sub5.judge_wld_id, > sub5.judge_id, > @@ -129,6 +132,7 @@ > ), > jrtf2.case_document_id > ) sub5 > + WHERE sub5.judge_id = sub0.judge_id > GROUP BY sub5.case_year_number, > sub5.judge_wld_id, > sub5.judge_id, > > SELECT agg_sub.judge_id, > agg_sub.display_name, > agg_sub.active_flag, > agg_sub.judge_court_level, > agg_sub.jrt_fact_first_year_trial, > agg_sub.jrt_fact_last_year_trial, > agg_sub.jrt_fact_totalcount_trial, > agg_sub.filtered_first_year_trial, > agg_sub.filtered_last_year_trial, > agg_sub.jrt_fact_count_trial, > agg_sub.jrt_fact_first_year_appeal, > agg_sub.jrt_fact_last_year_appeal, > agg_sub.jrt_fact_totalcount_appeal, > agg_sub.filtered_first_year_appeal, > agg_sub.filtered_last_year_appeal, > agg_sub.jrt_fact_count_appeal, > appellate_flag_sub.appellate_flag > FROM (SELECT sub3.judge_id, > sub3.display_name, > sub3.active_flag, > sub3.judge_court_level, > (MIN(sub3.trial_unfilt_case_year_number)) AS > jrt_fact_first_year_trial, > (MAX(sub3.trial_unfilt_case_year_number)) AS > jrt_fact_last_year_trial, > (SUM(sub3.trial_unfilt_subcount)) AS > jrt_fact_totalcount_trial, > (MIN(sub3.trial_filt_case_year_number)) AS > filtered_first_year_trial, > (MAX(sub3.trial_filt_case_year_number)) AS > filtered_last_year_trial, > (SUM(sub3.trial_filt_subcount)) AS jrt_fact_count_trial, > (MIN(sub3.appeal_unfilt_case_year_number)) AS > jrt_fact_first_year_appeal, > (MAX(sub3.appeal_unfilt_case_year_number)) AS > jrt_fact_last_year_appeal, > (SUM(sub3.appeal_unfilt_subcount)) AS > jrt_fact_totalcount_appeal, > (MIN(sub3.appeal_filt_case_year_number)) AS > filtered_first_year_appeal, > (MAX(sub3.appeal_filt_case_year_number)) AS > filtered_last_year_appeal, > (SUM(sub3.appeal_filt_subcount)) AS jrt_fact_count_appeal > FROM (SELECT sub0.judge_id, > sub0.display_name, > sub0.active_flag, > sub0.judge_court_level, > (CASE WHEN sub2.grouping_flg = 'T' AND > sub2.judge_wld_id = sub0.judge_wld_id THEN sub2.case_year_number ELSE > NULL END) AS trial_unfilt_case_year_number, > (CASE WHEN sub2.grouping_flg = 'T'AND > sub2.judge_wld_id = sub0.judge_wld_id THEN sub2.subcount ELSE NULL END > ) AS trial_unfilt_subcount, > (CASE WHEN sub2.grouping_flg = 'T' AND > sub2.judge_wld_id = sub0.judge_wld_id THEN (CASE WHEN > sub2.case_year_number BETWEEN sub0.low_case_year_number AND > sub0.high_case_year_number > THEN sub2.case_year_number ELSE NULL END) > ELSE NULL END) AS trial_filt_case_year_number, > (CASE WHEN sub2.grouping_flg = 'T' AND > sub2.judge_wld_id = sub0.judge_wld_id THEN (CASE WHEN > sub2.case_year_number BETWEEN sub0.low_case_year_number AND > sub0.high_case_year_number > THEN sub2.subcount ELSE NULL END ) > ELSE NULL END ) AS trial_filt_subcount, > (CASE WHEN sub1.grouping_flg = 'A'AND > sub1.judge_wld_id = sub0.judge_wld_id THEN sub1.case_year_number WHEN > sub2.grouping_flg = 'A' AND sub2.judge_wld_id = sub0.judge_wld_id > THEN sub2.case_year_number ELSE NULL END > ) AS appeal_unfilt_case_year_number, > ( > CASE WHEN sub1.grouping_flg = 'A' > AND sub1.judge_wld_id = sub0.judge_wld_id THEN > sub1.subcount WHEN sub2.grouping_flg = 'A' > AND sub2.judge_wld_id = sub0.judge_wld_id THEN > sub2.subcount ELSE NULL END > ) AS appeal_unfilt_subcount, > ( > CASE WHEN sub1.grouping_flg = 'A' > AND sub1.judge_wld_id = sub0.judge_wld_id THEN ( > CASE WHEN sub1.case_year_number BETWEEN > sub0.low_case_year_number > AND sub0.high_case_year_number THEN > sub1.case_year_number ELSE NULL END > ) WHEN sub2.grouping_flg = 'A' > AND sub2.judge_wld_id = sub0.judge_wld_id THEN ( > CASE WHEN sub2.case_year_number BETWEEN > sub0.low_case_year_number > AND sub0.high_case_year_number THEN > sub2.case_year_number ELSE NULL END > ) ELSE NULL END > ) AS appeal_filt_case_year_number, > ( > CASE WHEN sub1.grouping_flg = 'A' > AND sub1.judge_wld_id = sub0.judge_wld_id THEN ( > CASE WHEN sub1.case_year_number BETWEEN > sub0.low_case_year_number > AND sub0.high_case_year_number THEN > sub1.subcount ELSE NULL END > ) WHEN sub2.grouping_flg = 'A' > AND sub2.judge_wld_id = sub0.judge_wld_id THEN ( > CASE WHEN sub2.case_year_number BETWEEN > sub0.low_case_year_number > AND sub0.high_case_year_number THEN > sub2.subcount ELSE NULL END > ) ELSE NULL END > ) AS appeal_filt_subcount > FROM ( SELECT 0104119201 AS judge_wld_id, > 2013 AS low_case_year_number, > 2023 AS high_case_year_number, > judg1.judge_id, > judg1.display_name, > judg1.active_flag, > judg1.judge_court_level > FROM wln_mart.judge judg1 > WHERE judg1.wld_id = 01041192 > AND judg1.profile_id = 01 > AND (judg1.jrt_opt_out_flag <> 'Y' OR > judg1.jrt_opt_out_flag IS NULL) > ) sub0 > LEFT OUTER JOIN > LATERAL > ( SELECT sub4.case_year_number, > sub4.judge_wld_id, > sub4.judge_id, > sub4.grouping_flg, > COUNT(*) AS subcount > FROM (SELECT jrtf1.case_year_number, > jrtf1.higher_judge_wld_id AS > judge_wld_id, > jrtf1.higher_judge_id AS judge_id, > 'A' AS grouping_flg, > jrtf1.case_document_id AS subcount > FROM wln_mart.jrt_fact jrtf1 > GROUP BY jrtf1.case_year_number, > jrtf1.higher_judge_wld_id, > jrtf1.higher_judge_id, > jrtf1.case_document_id > ) sub4 > WHERE sub4.judge_id = sub0.judge_id > GROUP BY sub4.case_year_number, > sub4.judge_wld_id, > sub4.judge_id, > sub4.grouping_flg > ) sub1 > ON sub1.judge_id = sub0.judge_id > LEFT OUTER JOIN > LATERAL > (SELECT sub5.case_year_number, > sub5.judge_wld_id, > sub5.judge_id, > sub5.grouping_flg, > COUNT(*) AS subcount > FROM (SELECT jrtf2.case_year_number, > jrtf2.lower_judge_wld_id AS judge_wld_id, > jrtf2.lower_judge_id AS judge_id, > ( > CASE WHEN > jrtf2.lower_judge_court_level_id = 1004 THEN 'T' ELSE 'A' END > ) AS grouping_flg, > jrtf2.case_document_id AS subcount > FROM wln_mart.jrt_fact jrtf2 > WHERE jrtf2.lower_judge_court_level_id > 1000 > AND jrtf2.lower_judge_court_level_id <= 1004 > GROUP BY jrtf2.case_year_number, > jrtf2.lower_judge_wld_id, > jrtf2.lower_judge_id, > ( > CASE WHEN > jrtf2.lower_judge_court_level_id = 1004 THEN 'T' ELSE 'A' END > ), > jrtf2.case_document_id > ) sub5 > WHERE sub5.judge_id = sub0.judge_id > GROUP BY sub5.case_year_number, > sub5.judge_wld_id, > sub5.judge_id, > sub5.grouping_flg > ) sub2 > ON sub2.judge_id = sub0.judge_id > ) sub3 > GROUP BY sub3.judge_id, > sub3.display_name, > sub3.active_flag, > sub3.judge_court_level > ) agg_sub, > (SELECT judge_id, > (CASE WHEN (SUM (appellate_flag) < (COUNT(*) / 2)) THEN 0 ELSE > 1 END ) AS appellate_flag > FROM (SELECT DISTINCT jrtf.case_year_number, > jrtf.case_document_id, > jrtf.lower_judge_id, > jrtf.higher_judge_id, > ( > CASE WHEN ( > lower_judge_wld_id = 0104119201 > AND lower_judge_court_level_id = 1004 > ) THEN 0 ELSE 1 END ) AS appellate_flag, > ( > CASE WHEN lower_judge_wld_id = 0104119201 THEN > lower_judge_id ELSE higher_judge_id END > ) AS judge_id > FROM wln_mart.JRT_FACT jrtf > WHERE LOWER_JUDGE_WLD_ID = 0104119201 > OR HIGHER_JUDGE_WLD_ID = 0104119201 > ORDER BY case_year_number DESC > ) sub0 > GROUP BY judge_id > LIMIT 11 > ) appellate_flag_sub > WHERE > Agg_sub.judge_id = appellate_flag_sub.judge_id > > > Vladimir >