RE: [EXT] YNT: Need help tuning a query - Mailing list pgsql-general
| From | Wong, Kam Fook (TR Technology) |
|---|---|
| Subject | RE: [EXT] YNT: Need help tuning a query |
| Date | |
| Msg-id | CH0PR03MB61009FA053371E4D168D8D55FEC0A@CH0PR03MB6100.namprd03.prod.outlook.com Whole thread Raw |
| In response to | Re: [EXT] YNT: Need help tuning a query (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>) |
| List | pgsql-general |
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@gmail.com>
Sent: Friday, September 29, 2023 1:47 AM
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
Cc: Mehmet Çakoğlu <mehmetcakoglu@gmail.com>; PostgreSQL General <pgsql-general@lists.postgresql.org>; Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
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
pgsql-general by date: