From: Amn Ojee Uw <amnojeeuw@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: [EXT] YNT: Need help tuning a query
Date: Sat, 30 Sep 2023 05:58:27 -0400
Message-ID: <bfd25a98-b57f-7930-eb2b-2e8519c7ebf7@gmail.com> (raw)
In-Reply-To: <CAB=Je-EaWeJLG39ZRmqDcjVFAQKNoUH+PvWWHKpb1=C3LECicw@mail.gmail.com>
References: <TYAP286MB063665A17431CD4D243315D7A9749@TYAP286MB0636.JPNP286.PROD.OUTLOOK.COM>
<DM6PR03MB433218DEED175335E5710C54FAC2A@DM6PR03MB4332.namprd03.prod.outlook.com>
<07fc01d9f21f$30e48190$92ad84b0$@gmail.com>
<CH0PR03MB61008CBE82754947D50B8A1EFEC1A@CH0PR03MB6100.namprd03.prod.outlook.com>
<CAB=Je-G7j5yaXdAOe6pxzMsVf-Mcxg77tfWBQM=9eV=rDJ6yRQ@mail.gmail.com>
<CAB=Je-EaWeJLG39ZRmqDcjVFAQKNoUH+PvWWHKpb1=C3LECicw@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: amnojeeuw@gmail.com
Subject: Re: [EXT] YNT: Need help tuning a query
In-Reply-To: <bfd25a98-b57f-7930-eb2b-2e8519c7ebf7@gmail.com>
References: <TYAP286MB063665A17431CD4D243315D7A9749@TYAP286MB0636.JPNP286.PROD.OUTLOOK.COM> <DM6PR03MB433218DEED175335E5710C54FAC2A@DM6PR03MB4332.namprd03.prod.outlook.com> <07fc01d9f21f$30e48190$92ad84b0$@gmail.com> <CH0PR03MB61008CBE82754947D50B8A1EFEC1A@CH0PR03MB6100.namprd03.prod.outlook.com> <CAB=Je-G7j5yaXdAOe6pxzMsVf-Mcxg77tfWBQM=9eV=rDJ6yRQ@mail.gmail.com> <CAB=Je-EaWeJLG39ZRmqDcjVFAQKNoUH+PvWWHKpb1=C3LECicw@mail.gmail.com> <bfd25a98-b57f-7930-eb2b-2e8519c7ebf7@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox