Re: [EXT] YNT: Need help tuning a query

[Vladimir Sitnikov <sitnikov.vladimir@xxxxxxxxx>]

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