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

["Wong, Kam Fook (TR Technology)" <kamfook.wong@xxxxxxxxxxxxxxxxxx>]

Exceptional.  Thank you Vladimir.  Salute!  Here is the explain plan.  Run time is now 134 ms. 

 

Hash Join  (cost=85280.37..85280.70 rows=1 width=345) (actual time=23.160..23.220 rows=1 loops=1)

  Output: judg1.judge_id, judg1.display_name, judg1.active_flag, judg1.judge_court_level, (min(CASE WHEN ((sub2.grouping_flg = 'T'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN sub2.case_year_number ELSE NULL::bigint END)), (max(CASE WHEN ((sub2.grouping_flg = 'T'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN sub2.case_year_number ELSE NULL::bigint END)), (sum(CASE WHEN ((sub2.grouping_flg = 'T'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN sub2.subcount ELSE NULL::bigint END)), (min(CASE WHEN ((sub2.grouping_flg = 'T'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub2.case_year_number >= 2013) AND (sub2.case_year_number <= 2023)) THEN sub2.case_year_number ELSE NULL::bigint END ELSE NULL::bigint END)), (max(CASE WHEN ((sub2.grouping_flg = 'T'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub2.case_year_number >= 2013) AND (sub2.case_year_number <= 2023)) THEN sub2.case_year_number ELSE NULL::bigint END ELSE NULL::bigint END)), (sum(CASE WHEN ((sub2.grouping_flg = 'T'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub2.case_year_number >= 2013) AND (sub2.case_year_number <= 2023)) THEN sub2.subcount ELSE NULL::bigint END ELSE NULL::bigint END)), (min(CASE WHEN ((sub1.grouping_flg = 'A'::text) AND (sub1.judge_wld_id = '104119201'::numeric)) THEN sub1.case_year_number WHEN ((sub2.grouping_flg = 'A'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN sub2.case_year_number ELSE NULL::bigint END)), (max(CASE WHEN ((sub1.grouping_flg = 'A'::text) AND (sub1.judge_wld_id = '104119201'::numeric)) THEN sub1.case_year_number WHEN ((sub2.grouping_flg = 'A'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN sub2.case_year_number ELSE NULL::bigint END)), (sum(CASE WHEN ((sub1.grouping_flg = 'A'::text) AND (sub1.judge_wld_id = '104119201'::numeric)) THEN sub1.subcount WHEN ((sub2.grouping_flg = 'A'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN sub2.subcount ELSE NULL::bigint END)), (min(CASE WHEN ((sub1.grouping_flg = 'A'::text) AND (sub1.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub1.case_year_number >= 2013) AND (sub1.case_year_number <= 2023)) THEN sub1.case_year_number ELSE NULL::bigint END WHEN ((sub2.grouping_flg = 'A'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub2.case_year_number >= 2013) AND (sub2.case_year_number <= 2023)) THEN sub2.case_year_number ELSE NULL::bigint END ELSE NULL::bigint END)), (max(CASE WHEN ((sub1.grouping_flg = 'A'::text) AND (sub1.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub1.case_year_number >= 2013) AND (sub1.case_year_number <= 2023)) THEN sub1.case_year_number ELSE NULL::bigint END WHEN ((sub2.grouping_flg = 'A'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub2.case_year_number >= 2013) AND (sub2.case_year_number <= 2023)) THEN sub2.case_year_number ELSE NULL::bigint END ELSE NULL::bigint END)), (sum(CASE WHEN ((sub1.grouping_flg = 'A'::text) AND (sub1.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub1.case_year_number >= 2013) AND (sub1.case_year_number <= 2023)) THEN sub1.subcount ELSE NULL::bigint END WHEN ((sub2.grouping_flg = 'A'::text) AND (sub2.judge_wld_id = '104119201'::numeric)) THEN CASE WHEN ((sub2.case_year_number >= 2013) AND (sub2.case_year_number <= 2023)) THEN sub2.subcount ELSE NULL::bigint END ELSE NULL::bigint END)), (CASE WHEN (sum((CASE WHEN ((jrtf.lower_judge_wld_id = '104119201'::numeric) AND (jrtf.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END)) < (count(*) / 2)) THEN 0 ELSE 1 END)

  Hash Cond: ((CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END) = judg1.judge_id)

  Buffers: shared hit=5514

  ->  Limit  (cost=41718.13..41718.30 rows=11 width=12) (actual time=7.579..7.611 rows=1 loops=1)

        Output: (CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END), (CASE WHEN (sum((CASE WHEN ((jrtf.lower_judge_wld_id = '104119201'::numeric) AND (jrtf.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END)) < (count(*) / 2)) THEN 0 ELSE 1 END)

        Buffers: shared hit=1573

        ->  HashAggregate  (cost=41718.13..41721.13 rows=200 width=12) (actual time=7.578..7.609 rows=1 loops=1)

              Output: (CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END), CASE WHEN (sum((CASE WHEN ((jrtf.lower_judge_wld_id = '104119201'::numeric) AND (jrtf.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END)) < (count(*) / 2)) THEN 0 ELSE 1 END

              Group Key: (CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END)

              Batches: 1  Memory Usage: 40kB

              Buffers: shared hit=1573

              ->  Unique  (cost=41305.59..41511.86 rows=11787 width=44) (actual time=6.613..7.471 rows=763 loops=1)

                    Output: jrtf.case_year_number, jrtf.case_document_id, jrtf.lower_judge_id, jrtf.higher_judge_id, (CASE WHEN ((jrtf.lower_judge_wld_id = '104119201'::numeric) AND (jrtf.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END), (CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END)

                    Buffers: shared hit=1573

                    ->  Sort  (cost=41305.59..41335.06 rows=11787 width=44) (actual time=6.612..6.860 rows=4564 loops=1)

                          Output: jrtf.case_year_number, jrtf.case_document_id, jrtf.lower_judge_id, jrtf.higher_judge_id, (CASE WHEN ((jrtf.lower_judge_wld_id = '104119201'::numeric) AND (jrtf.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END), (CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END)

                          Sort Key: jrtf.case_year_number DESC, jrtf.case_document_id, jrtf.lower_judge_id, jrtf.higher_judge_id, (CASE WHEN ((jrtf.lower_judge_wld_id = '104119201'::numeric) AND (jrtf.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END), (CASE WHEN (jrtf.lower_judge_wld_id = '104119201'::numeric) THEN jrtf.lower_judge_id ELSE jrtf.higher_judge_id END)

                          Sort Method: quicksort  Memory: 549kB

                          Buffers: shared hit=1573

                          ->  Append  (cost=8.57..40508.50 rows=11787 width=44) (actual time=0.390..4.760 rows=4564 loops=1)

                                Buffers: shared hit=1573

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_1990 jrtf_1  (cost=8.57..12.59 rows=1 width=44) (actual time=0.018..0.020 rows=0 loops=1)

                                      Output: jrtf_1.case_year_number, jrtf_1.case_document_id, jrtf_1.lower_judge_id, jrtf_1.higher_judge_id, CASE WHEN ((jrtf_1.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_1.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_1.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_1.lower_judge_id ELSE jrtf_1.higher_judge_id END

                                      Recheck Cond: ((jrtf_1.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_1.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=4

                                      ->  BitmapOr  (cost=8.57..8.57 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)

                                            Buffers: shared hit=4

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_1990_lower_judge_wld_id_idx  (cost=0.00..4.29 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)

                                                  Index Cond: (jrtf_1.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=2

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_1990_higher_judge_wld_id_idx  (cost=0.00..4.29 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)

                                                  Index Cond: (jrtf_1.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=2

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1991 jrtf_2  (cost=0.00..1.29 rows=1 width=44) (actual time=0.011..0.011 rows=0 loops=1)

                                      Output: jrtf_2.case_year_number, jrtf_2.case_document_id, jrtf_2.lower_judge_id, jrtf_2.higher_judge_id, CASE WHEN ((jrtf_2.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_2.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_2.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_2.lower_judge_id ELSE jrtf_2.higher_judge_id END

                                      Filter: ((jrtf_2.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_2.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 19

                                      Buffers: shared hit=1

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1992 jrtf_3  (cost=0.00..1.31 rows=1 width=44) (actual time=0.007..0.007 rows=0 loops=1)

                                      Output: jrtf_3.case_year_number, jrtf_3.case_document_id, jrtf_3.lower_judge_id, jrtf_3.higher_judge_id, CASE WHEN ((jrtf_3.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_3.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_3.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_3.lower_judge_id ELSE jrtf_3.higher_judge_id END

                                      Filter: ((jrtf_3.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_3.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 20

                                      Buffers: shared hit=1

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1993 jrtf_4  (cost=0.00..1.02 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=1)

                                      Output: jrtf_4.case_year_number, jrtf_4.case_document_id, jrtf_4.lower_judge_id, jrtf_4.higher_judge_id, CASE WHEN ((jrtf_4.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_4.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_4.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_4.lower_judge_id ELSE jrtf_4.higher_judge_id END

                                      Filter: ((jrtf_4.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_4.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 1

                                      Buffers: shared hit=1

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1994 jrtf_5  (cost=0.00..1.31 rows=1 width=44) (actual time=0.009..0.010 rows=0 loops=1)

                                      Output: jrtf_5.case_year_number, jrtf_5.case_document_id, jrtf_5.lower_judge_id, jrtf_5.higher_judge_id, CASE WHEN ((jrtf_5.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_5.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_5.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_5.lower_judge_id ELSE jrtf_5.higher_judge_id END

                                      Filter: ((jrtf_5.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_5.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 20

                                      Buffers: shared hit=1

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1995 jrtf_6  (cost=0.00..4.97 rows=1 width=44) (actual time=0.040..0.040 rows=0 loops=1)

                                      Output: jrtf_6.case_year_number, jrtf_6.case_document_id, jrtf_6.lower_judge_id, jrtf_6.higher_judge_id, CASE WHEN ((jrtf_6.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_6.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_6.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_6.lower_judge_id ELSE jrtf_6.higher_judge_id END

                                      Filter: ((jrtf_6.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_6.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 131

                                      Buffers: shared hit=3

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1996 jrtf_7  (cost=0.00..3.03 rows=1 width=44) (actual time=0.022..0.022 rows=0 loops=1)

                                      Output: jrtf_7.case_year_number, jrtf_7.case_document_id, jrtf_7.lower_judge_id, jrtf_7.higher_judge_id, CASE WHEN ((jrtf_7.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_7.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_7.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_7.lower_judge_id ELSE jrtf_7.higher_judge_id END

                                      Filter: ((jrtf_7.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_7.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 68

                                      Buffers: shared hit=2

                                ->  Seq Scan on wln_mart.jrt_fact_jrt_data_1997 jrtf_8  (cost=0.00..8.07 rows=1 width=44) (actual time=0.056..0.056 rows=0 loops=1)

                                      Output: jrtf_8.case_year_number, jrtf_8.case_document_id, jrtf_8.lower_judge_id, jrtf_8.higher_judge_id, CASE WHEN ((jrtf_8.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_8.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_8.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_8.lower_judge_id ELSE jrtf_8.higher_judge_id END

                                      Filter: ((jrtf_8.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_8.higher_judge_wld_id = '104119201'::numeric))

                                      Rows Removed by Filter: 204

                                      Buffers: shared hit=5

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_1998 jrtf_9  (cost=8.57..14.62 rows=2 width=44) (actual time=0.019..0.020 rows=0 loops=1)

                                      Output: jrtf_9.case_year_number, jrtf_9.case_document_id, jrtf_9.lower_judge_id, jrtf_9.higher_judge_id, CASE WHEN ((jrtf_9.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_9.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_9.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_9.lower_judge_id ELSE jrtf_9.higher_judge_id END

                                      Recheck Cond: ((jrtf_9.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_9.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=4

                                      ->  BitmapOr  (cost=8.57..8.57 rows=2 width=0) (actual time=0.018..0.019 rows=0 loops=1)

                                            Buffers: shared hit=4

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_1998_lower_judge_wld_id_idx  (cost=0.00..4.28 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)

                                                  Index Cond: (jrtf_9.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=2

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_1998_higher_judge_wld_id_idx  (cost=0.00..4.29 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)

                                                  Index Cond: (jrtf_9.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=2

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_1999 jrtf_10  (cost=8.68..51.80 rows=14 width=44) (actual time=0.010..0.011 rows=0 loops=1)

                                      Output: jrtf_10.case_year_number, jrtf_10.case_document_id, jrtf_10.lower_judge_id, jrtf_10.higher_judge_id, CASE WHEN ((jrtf_10.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_10.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_10.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_10.lower_judge_id ELSE jrtf_10.higher_judge_id END

                                      Recheck Cond: ((jrtf_10.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_10.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=4

                                      ->  BitmapOr  (cost=8.68..8.68 rows=14 width=0) (actual time=0.010..0.011 rows=0 loops=1)

                                            Buffers: shared hit=4

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_1999_lower_judge_wld_id_idx  (cost=0.00..4.33 rows=7 width=0) (actual time=0.005..0.005 rows=0 loops=1)

                                                  Index Cond: (jrtf_10.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=2

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_1999_higher_judge_wld_id_idx  (cost=0.00..4.33 rows=7 width=0) (actual time=0.005..0.005 rows=0 loops=1)

                                                  Index Cond: (jrtf_10.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=2

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2000 jrtf_11  (cost=10.74..839.39 rows=237 width=44) (actual time=0.015..0.016 rows=0 loops=1)

                                      Output: jrtf_11.case_year_number, jrtf_11.case_document_id, jrtf_11.lower_judge_id, jrtf_11.higher_judge_id, CASE WHEN ((jrtf_11.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_11.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_11.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_11.lower_judge_id ELSE jrtf_11.higher_judge_id END

                                      Recheck Cond: ((jrtf_11.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_11.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=10.74..10.74 rows=237 width=0) (actual time=0.014..0.015 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2000_lower_judge_wld_id_idx  (cost=0.00..4.83 rows=55 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_11.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2000_higher_judge_wld_id_idx  (cost=0.00..5.79 rows=182 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_11.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2001 jrtf_12  (cost=10.87..898.62 rows=253 width=44) (actual time=0.016..0.018 rows=0 loops=1)

                                      Output: jrtf_12.case_year_number, jrtf_12.case_document_id, jrtf_12.lower_judge_id, jrtf_12.higher_judge_id, CASE WHEN ((jrtf_12.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_12.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_12.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_12.lower_judge_id ELSE jrtf_12.higher_judge_id END

                                      Recheck Cond: ((jrtf_12.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_12.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=10.87..10.87 rows=253 width=0) (actual time=0.016..0.016 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2001_lower_judge_wld_id_idx  (cost=0.00..4.85 rows=57 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_12.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2001_higher_judge_wld_id_idx  (cost=0.00..5.89 rows=196 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_12.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2002 jrtf_13  (cost=15.27..1074.10 rows=301 width=44) (actual time=0.015..0.016 rows=0 loops=1)

                                      Output: jrtf_13.case_year_number, jrtf_13.case_document_id, jrtf_13.lower_judge_id, jrtf_13.higher_judge_id, CASE WHEN ((jrtf_13.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_13.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_13.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_13.lower_judge_id ELSE jrtf_13.higher_judge_id END

                                      Recheck Cond: ((jrtf_13.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_13.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=15.27..15.27 rows=301 width=0) (actual time=0.015..0.015 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2002_lower_judge_wld_id_idx  (cost=0.00..4.92 rows=66 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_13.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2002_higher_judge_wld_id_idx  (cost=0.00..10.20 rows=236 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_13.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2003 jrtf_14  (cost=15.46..1158.27 rows=326 width=44) (actual time=0.013..0.014 rows=0 loops=1)

                                      Output: jrtf_14.case_year_number, jrtf_14.case_document_id, jrtf_14.lower_judge_id, jrtf_14.higher_judge_id, CASE WHEN ((jrtf_14.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_14.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_14.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_14.lower_judge_id ELSE jrtf_14.higher_judge_id END

                                      Recheck Cond: ((jrtf_14.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_14.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=15.46..15.46 rows=326 width=0) (actual time=0.013..0.014 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2003_lower_judge_wld_id_idx  (cost=0.00..4.95 rows=70 width=0) (actual time=0.006..0.006 rows=0 loops=1)

                                                  Index Cond: (jrtf_14.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2003_higher_judge_wld_id_idx  (cost=0.00..10.35 rows=256 width=0) (actual time=0.006..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_14.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2004 jrtf_15  (cost=15.55..1196.85 rows=337 width=44) (actual time=0.017..0.017 rows=0 loops=1)

                                      Output: jrtf_15.case_year_number, jrtf_15.case_document_id, jrtf_15.lower_judge_id, jrtf_15.higher_judge_id, CASE WHEN ((jrtf_15.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_15.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_15.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_15.lower_judge_id ELSE jrtf_15.higher_judge_id END

                                      Recheck Cond: ((jrtf_15.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_15.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=15.55..15.55 rows=337 width=0) (actual time=0.016..0.017 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2004_lower_judge_wld_id_idx  (cost=0.00..4.96 rows=72 width=0) (actual time=0.006..0.006 rows=0 loops=1)

                                                  Index Cond: (jrtf_15.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2004_higher_judge_wld_id_idx  (cost=0.00..10.41 rows=265 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_15.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2005 jrtf_16  (cost=15.85..1329.50 rows=375 width=44) (actual time=0.014..0.015 rows=0 loops=1)

                                      Output: jrtf_16.case_year_number, jrtf_16.case_document_id, jrtf_16.lower_judge_id, jrtf_16.higher_judge_id, CASE WHEN ((jrtf_16.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_16.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_16.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_16.lower_judge_id ELSE jrtf_16.higher_judge_id END

                                      Recheck Cond: ((jrtf_16.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_16.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=15.85..15.85 rows=375 width=0) (actual time=0.013..0.014 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2005_lower_judge_wld_id_idx  (cost=0.00..5.02 rows=80 width=0) (actual time=0.006..0.006 rows=0 loops=1)

                                                  Index Cond: (jrtf_16.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2005_higher_judge_wld_id_idx  (cost=0.00..10.64 rows=295 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_16.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2006 jrtf_17  (cost=15.98..1387.33 rows=391 width=44) (actual time=0.015..0.016 rows=0 loops=1)

                                      Output: jrtf_17.case_year_number, jrtf_17.case_document_id, jrtf_17.lower_judge_id, jrtf_17.higher_judge_id, CASE WHEN ((jrtf_17.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_17.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_17.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_17.lower_judge_id ELSE jrtf_17.higher_judge_id END

                                      Recheck Cond: ((jrtf_17.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_17.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=15.98..15.98 rows=391 width=0) (actual time=0.015..0.015 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2006_lower_judge_wld_id_idx  (cost=0.00..5.04 rows=82 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_17.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2006_higher_judge_wld_id_idx  (cost=0.00..10.74 rows=309 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_17.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2007 jrtf_18  (cost=16.09..1434.30 rows=405 width=44) (actual time=0.017..0.018 rows=0 loops=1)

                                      Output: jrtf_18.case_year_number, jrtf_18.case_document_id, jrtf_18.lower_judge_id, jrtf_18.higher_judge_id, CASE WHEN ((jrtf_18.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_18.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_18.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_18.lower_judge_id ELSE jrtf_18.higher_judge_id END

                                      Recheck Cond: ((jrtf_18.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_18.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=16.09..16.09 rows=405 width=0) (actual time=0.016..0.017 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2007_lower_judge_wld_id_idx  (cost=0.00..5.05 rows=83 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_18.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2007_higher_judge_wld_id_idx  (cost=0.00..10.84 rows=322 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_18.higher_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                ->  Bitmap Heap Scan on wln_mart.jrt_fact_jrt_data_2008 jrtf_19  (cost=16.54..1633.45 rows=461 width=44) (actual time=0.014..0.015 rows=0 loops=1)

                                      Output: jrtf_19.case_year_number, jrtf_19.case_document_id, jrtf_19.lower_judge_id, jrtf_19.higher_judge_id, CASE WHEN ((jrtf_19.lower_judge_wld_id = '104119201'::numeric) AND (jrtf_19.lower_judge_court_level_id = 1004)) THEN 0 ELSE 1 END, CASE WHEN (jrtf_19.lower_judge_wld_id = '104119201'::numeric) THEN jrtf_19.lower_judge_id ELSE jrtf_19.higher_judge_id END

                                      Recheck Cond: ((jrtf_19.lower_judge_wld_id = '104119201'::numeric) OR (jrtf_19.higher_judge_wld_id = '104119201'::numeric))

                                      Buffers: shared hit=6

                                      ->  BitmapOr  (cost=16.54..16.54 rows=461 width=0) (actual time=0.013..0.014 rows=0 loops=1)

                                            Buffers: shared hit=6

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2008_lower_judge_wld_id_idx  (cost=0.00..5.12 rows=93 width=0) (actual time=0.007..0.007 rows=0 loops=1)

                                                  Index Cond: (jrtf_19.lower_judge_wld_id = '104119201'::numeric)

                                                  Buffers: shared hit=3

                                            ->  Bitmap Index Scan on jrt_fact_jrt_data_2008_higher_judge_wld_id_idx  (cost=0.00..11.19 rows=368 width=0) (actual time=0.006..0.006 rows=0 loops=1)

                                                  Index Cond: (jrtf_19.higher_judge_wld_id = '104119201'::numeric)

 

Thank you

Kam

From: Vladimir Sitnikov <sitnikov.vladimir@xxxxxxxxx>
Sent: Friday, September 29, 2023 1:47 AM
To: Dirschel, Steve <steve.dirschel@xxxxxxxxxxxxxxxxxx>
Cc: Mehmet Çakoğlu <mehmetcakoglu@xxxxxxxxx>; PostgreSQL General <pgsql-general@xxxxxxxxxxxxxxxxxxxx>; Wong, Kam Fook (TR Technology) <kamfook.wong@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXT] YNT: Need help tuning a query

 

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