[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [EXT] YNT: Need help tuning a query
From: Amn Ojee Uw <amnojeeuw () gmail ! com>
Date: 2023-09-30 9:58:27
Message-ID: bfd25a98-b57f-7930-eb2b-2e8519c7ebf7 () gmail ! com
[Download RAW message or body]
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
>
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Wow!! This is what I call cryptic!! <br>
</p>
<div class="moz-cite-prefix">On 9/29/23 2:46 a.m., Vladimir Sitnikov
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAB=Je-EaWeJLG39ZRmqDcjVFAQKNoUH+PvWWHKpb1=C3LECicw@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div dir="ltr">Oh, I misplaced the added where conditions.
<div>It should have been as follows, however, the overall idea
is the same</div>
<div><br>
</div>
<div><font face="monospace">--- orignial.sql<br>
+++ tuned_v2.sql<br>
@@ -83,6 +83,7 @@<br>
AND
(judg1.jrt_opt_out_flag <> 'Y' OR
judg1.jrt_opt_out_flag IS NULL)<br>
) sub0<br>
LEFT OUTER JOIN<br>
+ LATERAL<br>
( SELECT sub4.case_year_number,<br>
sub4.judge_wld_id,<br>
sub4.judge_id,<br>
@@ -99,6 +100,7 @@<br>
jrtf1.higher_judge_id,<br>
jrtf1.case_document_id<br>
) sub4<br>
+ WHERE sub4.judge_id = sub0.judge_id<br>
GROUP BY sub4.case_year_number,<br>
sub4.judge_wld_id,<br>
sub4.judge_id,<br>
@@ -106,6 +108,7 @@<br>
) sub1<br>
ON sub1.judge_id = sub0.judge_id<br>
LEFT OUTER JOIN<br>
+ LATERAL<br>
(SELECT sub5.case_year_number,<br>
sub5.judge_wld_id,<br>
sub5.judge_id,<br>
@@ -129,6 +132,7 @@<br>
),<br>
jrtf2.case_document_id<br>
) sub5<br>
+ WHERE sub5.judge_id = sub0.judge_id<br>
GROUP BY sub5.case_year_number,<br>
sub5.judge_wld_id,<br>
sub5.judge_id,</font><br>
</div>
<div><br>
</div>
<div><font face="monospace">SELECT agg_sub.judge_id,<br>
agg_sub.display_name,<br>
agg_sub.active_flag,<br>
agg_sub.judge_court_level,<br>
agg_sub.jrt_fact_first_year_trial,<br>
agg_sub.jrt_fact_last_year_trial,<br>
agg_sub.jrt_fact_totalcount_trial,<br>
agg_sub.filtered_first_year_trial,<br>
agg_sub.filtered_last_year_trial,<br>
agg_sub.jrt_fact_count_trial,<br>
agg_sub.jrt_fact_first_year_appeal,<br>
agg_sub.jrt_fact_last_year_appeal,<br>
agg_sub.jrt_fact_totalcount_appeal,<br>
agg_sub.filtered_first_year_appeal,<br>
agg_sub.filtered_last_year_appeal,<br>
agg_sub.jrt_fact_count_appeal,<br>
appellate_flag_sub.appellate_flag<br>
FROM (SELECT sub3.judge_id,<br>
sub3.display_name,<br>
sub3.active_flag,<br>
sub3.judge_court_level,<br>
(MIN(sub3.trial_unfilt_case_year_number)) AS
jrt_fact_first_year_trial,<br>
(MAX(sub3.trial_unfilt_case_year_number)) AS
jrt_fact_last_year_trial,<br>
(SUM(sub3.trial_unfilt_subcount)) AS
jrt_fact_totalcount_trial,<br>
(MIN(sub3.trial_filt_case_year_number)) AS
filtered_first_year_trial,<br>
(MAX(sub3.trial_filt_case_year_number)) AS
filtered_last_year_trial,<br>
(SUM(sub3.trial_filt_subcount)) AS
jrt_fact_count_trial,<br>
(MIN(sub3.appeal_unfilt_case_year_number)) AS
jrt_fact_first_year_appeal,<br>
(MAX(sub3.appeal_unfilt_case_year_number)) AS
jrt_fact_last_year_appeal,<br>
(SUM(sub3.appeal_unfilt_subcount)) AS
jrt_fact_totalcount_appeal,<br>
(MIN(sub3.appeal_filt_case_year_number)) AS
filtered_first_year_appeal,<br>
(MAX(sub3.appeal_filt_case_year_number)) AS
filtered_last_year_appeal,<br>
(SUM(sub3.appeal_filt_subcount)) AS
jrt_fact_count_appeal<br>
FROM (SELECT sub0.judge_id,<br>
sub0.display_name,<br>
sub0.active_flag,<br>
sub0.judge_court_level,<br>
(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,<br>
(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,<br>
(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<br>
THEN sub2.case_year_number ELSE
NULL END) ELSE NULL END) AS trial_filt_case_year_number,<br>
(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<br>
THEN sub2.subcount ELSE NULL
END ) ELSE NULL END ) AS trial_filt_subcount,<br>
(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<br>
THEN sub2.case_year_number ELSE
NULL END ) AS appeal_unfilt_case_year_number,<br>
(<br>
CASE WHEN sub1.grouping_flg = 'A'<br>
AND sub1.judge_wld_id =
sub0.judge_wld_id THEN sub1.subcount WHEN
sub2.grouping_flg = 'A'<br>
AND sub2.judge_wld_id =
sub0.judge_wld_id THEN sub2.subcount ELSE NULL END<br>
) AS appeal_unfilt_subcount,<br>
(<br>
CASE WHEN sub1.grouping_flg = 'A'<br>
AND sub1.judge_wld_id =
sub0.judge_wld_id THEN (<br>
CASE WHEN sub1.case_year_number
BETWEEN sub0.low_case_year_number<br>
AND sub0.high_case_year_number
THEN sub1.case_year_number ELSE NULL END<br>
) WHEN sub2.grouping_flg = 'A'<br>
AND sub2.judge_wld_id =
sub0.judge_wld_id THEN (<br>
CASE WHEN sub2.case_year_number
BETWEEN sub0.low_case_year_number<br>
AND sub0.high_case_year_number
THEN sub2.case_year_number ELSE NULL END<br>
) ELSE NULL END<br>
) AS appeal_filt_case_year_number,<br>
(<br>
CASE WHEN sub1.grouping_flg = 'A'<br>
AND sub1.judge_wld_id =
sub0.judge_wld_id THEN (<br>
CASE WHEN sub1.case_year_number
BETWEEN sub0.low_case_year_number<br>
AND sub0.high_case_year_number
THEN sub1.subcount ELSE NULL END<br>
) WHEN sub2.grouping_flg = 'A'<br>
AND sub2.judge_wld_id =
sub0.judge_wld_id THEN (<br>
CASE WHEN sub2.case_year_number
BETWEEN sub0.low_case_year_number<br>
AND sub0.high_case_year_number
THEN sub2.subcount ELSE NULL END<br>
) ELSE NULL END<br>
) AS appeal_filt_subcount<br>
FROM ( SELECT 0104119201 AS judge_wld_id,<br>
2013 AS
low_case_year_number,<br>
2023 AS
high_case_year_number,<br>
judg1.judge_id,<br>
judg1.display_name,<br>
judg1.active_flag,<br>
judg1.judge_court_level<br>
FROM wln_mart.judge judg1<br>
WHERE judg1.wld_id = 01041192<br>
AND judg1.profile_id = 01<br>
AND (judg1.jrt_opt_out_flag
<> 'Y' OR judg1.jrt_opt_out_flag IS NULL)<br>
) sub0<br>
LEFT OUTER JOIN<br>
LATERAL<br>
( SELECT sub4.case_year_number,<br>
sub4.judge_wld_id,<br>
sub4.judge_id,<br>
sub4.grouping_flg,<br>
COUNT(*) AS subcount<br>
FROM (SELECT
jrtf1.case_year_number,<br>
jrtf1.higher_judge_wld_id
AS judge_wld_id,<br>
jrtf1.higher_judge_id AS
judge_id,<br>
'A' AS grouping_flg,<br>
jrtf1.case_document_id AS
subcount<br>
FROM wln_mart.jrt_fact jrtf1<br>
GROUP BY
jrtf1.case_year_number,<br>
jrtf1.higher_judge_wld_id,<br>
jrtf1.higher_judge_id,<br>
jrtf1.case_document_id<br>
) sub4<br>
WHERE sub4.judge_id = sub0.judge_id<br>
GROUP BY sub4.case_year_number,<br>
sub4.judge_wld_id,<br>
sub4.judge_id,<br>
sub4.grouping_flg<br>
) sub1<br>
ON sub1.judge_id = sub0.judge_id<br>
LEFT OUTER JOIN<br>
LATERAL<br>
(SELECT sub5.case_year_number,<br>
sub5.judge_wld_id,<br>
sub5.judge_id,<br>
sub5.grouping_flg,<br>
COUNT(*) AS subcount<br>
FROM (SELECT jrtf2.case_year_number,<br>
jrtf2.lower_judge_wld_id AS
judge_wld_id,<br>
jrtf2.lower_judge_id AS
judge_id,<br>
(<br>
CASE WHEN
jrtf2.lower_judge_court_level_id = 1004 THEN 'T' ELSE 'A'
END<br>
) AS grouping_flg,<br>
jrtf2.case_document_id AS
subcount<br>
FROM wln_mart.jrt_fact jrtf2<br>
WHERE
jrtf2.lower_judge_court_level_id > 1000<br>
AND
jrtf2.lower_judge_court_level_id <= 1004<br>
GROUP BY jrtf2.case_year_number,
<br>
jrtf2.lower_judge_wld_id,<br>
jrtf2.lower_judge_id,<br>
(<br>
CASE WHEN
jrtf2.lower_judge_court_level_id = 1004 THEN 'T' ELSE 'A'
END<br>
),<br>
jrtf2.case_document_id<br>
) sub5<br>
WHERE sub5.judge_id = sub0.judge_id<br>
GROUP BY sub5.case_year_number,<br>
sub5.judge_wld_id,<br>
sub5.judge_id,<br>
sub5.grouping_flg<br>
) sub2<br>
ON sub2.judge_id = sub0.judge_id<br>
) sub3<br>
GROUP BY sub3.judge_id,<br>
sub3.display_name,<br>
sub3.active_flag,<br>
sub3.judge_court_level<br>
) agg_sub,<br>
(SELECT judge_id,<br>
(CASE WHEN (SUM (appellate_flag) < (COUNT(*) /
2)) THEN 0 ELSE 1 END ) AS appellate_flag<br>
FROM (SELECT DISTINCT jrtf.case_year_number,<br>
jrtf.case_document_id,<br>
jrtf.lower_judge_id,<br>
jrtf.higher_judge_id,<br>
(<br>
CASE WHEN (<br>
lower_judge_wld_id = 0104119201<br>
AND lower_judge_court_level_id
= 1004<br>
) THEN 0 ELSE 1 END ) AS
appellate_flag,<br>
(<br>
CASE WHEN lower_judge_wld_id =
0104119201 THEN lower_judge_id ELSE higher_judge_id END<br>
) AS judge_id<br>
FROM wln_mart.JRT_FACT jrtf<br>
WHERE LOWER_JUDGE_WLD_ID = 0104119201<br>
OR HIGHER_JUDGE_WLD_ID = 0104119201<br>
ORDER BY case_year_number DESC<br>
) sub0<br>
GROUP BY judge_id<br>
LIMIT 11<br>
) appellate_flag_sub<br>
WHERE<br>
Agg_sub.judge_id = appellate_flag_sub.judge_id</font><br>
</div>
<div><br>
</div>
<div><br clear="all">
<div>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">
<div>Vladimir</div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic