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

Поиск

Список

Период

Сортировка

От	Wong, Kam Fook (TR Technology)
Тема	RE: [EXT] YNT: Need help tuning a query
Дата	28 сентября 2023 г. 21:57:23
Msg-id	CH0PR03MB61008CBE82754947D50B8A1EFEC1A@CH0PR03MB6100.namprd03.prod.outlook.com обсуждение исходный текст
Ответ на	YNT: Need help tuning a query (Mehmet Çakoğlu <mehmetcakoglu@gmail.com>)
Ответы	Re: [EXT] YNT: Need help tuning a query (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>)
Список	pgsql-general

Дерево обсуждения

CREATE INDEX jrt_fact_idx01 ON ONLY wln_mart.jrt_fact USING btree (case_document_id);

CREATE INDEX jrt_fact_idx02 ON ONLY wln_mart.jrt_fact USING btree (lower_judge_id);

CREATE INDEX jrt_fact_idx04 ON ONLY wln_mart.jrt_fact USING btree (appealed_case_ruling_id);

CREATE INDEX jrt_fact_idx05 ON ONLY wln_mart.jrt_fact USING btree (higher_judge_id);

CREATE INDEX jrt_fact_idx06 ON ONLY wln_mart.jrt_fact USING btree (lower_judge_court_level_id);

CREATE INDEX jrt_fact_idx07 ON ONLY wln_mart.jrt_fact USING btree (majority_judge_flag);

CREATE INDEX jrt_fact_idx08 ON ONLY wln_mart.jrt_fact USING btree (case_date_id);

CREATE INDEX jrt_fact_idx09 ON ONLY wln_mart.jrt_fact USING btree (attorney_id);

CREATE INDEX jrt_fact_idx10 ON ONLY wln_mart.jrt_fact USING btree (firm_id);

CREATE INDEX jrt_fact_idx11 ON ONLY wln_mart.jrt_fact USING btree (case_year_number);

CREATE INDEX jrt_fact_idx12 ON ONLY wln_mart.jrt_fact USING btree (lower_judge_wld_id);

CREATE INDEX jrt_fact_idx13 ON ONLY wln_mart.jrt_fact USING btree (higher_judge_wld_id);

CREATE INDEX idx_jdg_low_fname ON wln_mart.judge USING btree (lower((first_name)::text));

CREATE INDEX idx_jdg_low_surname ON wln_mart.judge USING btree (lower((sur_name)::text));

CREATE INDEX idx_judge_first_name ON wln_mart.judge USING btree (first_name);

CREATE INDEX idx_judge_search_name ON wln_mart.judge USING btree (search_name);

CREATE INDEX idx_judge_sur_name ON wln_mart.judge USING btree (sur_name);

CREATE INDEX judge_st_id ON wln_mart.judge USING btree (state_id);

CREATE INDEX judge_wld_prof_idx ON wln_mart.judge USING btree (wld_id, profile_id);

Thank you

Kam

From: Mehmet Çakoğlu <mehmetcakoglu@gmail.com>
Sent: Thursday, September 28, 2023 10:20 AM
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; 'PostgreSQL General' <pgsql-general@lists.postgresql.org>
Cc: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Subject: [EXT] YNT: Need help tuning a query

External Email: Use caution with links and attachments.

Could you send the the table’s Index scripts?

Kimden: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
Tarih: 27 Eylül 2023 Çarşamba 20:45
Kime: PostgreSQL General <pgsql-general@lists.postgresql.org>
Bilgi: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Konu: Need help tuning a query

We are migrating from Oracle to Postgres. I have a ugly query that performs great in Oracle- it does 5259 logical reads and completes in < 1 second. In Postgres it’s taking 35+ seconds. I can see 2 parts in the plan that are the problem with Postgres but I’m not sure how to change the query to optimize optimally. I believe the query is generated based on user selection criteria so I can’t really hint the query because there can be a lot of variations of it (nor do I see a push_subq type of hint in pg_hint_plan). The query is long and ugly, the plan is long and ugly in Postgres. The query is below. The problem is in 2 spots:

Inline view sub4. There is no WHERE clause so it’s full scanning table JRT_FACT. Inline view sub4 is inside inline view sub1. The join to sub1 is on sub1.judge_id. Sub1.judge_id comes from jrt_fact jrt1. Higher_judge_id. There is an index on that column. If you look at the Oracle plan it’s using a PUSHED PREDICATE operation where it’s pushing the join on sub1.judge_id = sub0.judge_id into sub4. That is the optimal way to access jrt_fact in that part of the query.
Inline view sub5. There is filtering in the WHERE clause- where lower_judge_court_level_id > 1000 and lower_judge_court_level_id <= 1004 but that filtering alone is not very good. It’s a similar problem as sub4- in this case sub2 has sub4 in it and the join to sub2 is on judge_id. That judge_id is coming from jrt_fact jrtf2.lower_judge_id. That column is indexed. Oracle will use a PUSHED PREDICATE when accessing jrtf2 which is the optimal way to access that part of the query.
When it accesses table judge judg1 it is full scanning that table even though there is an index on wld_id. It only does 3445 shared buffer hits so that is not the main reason the query is slow but I don’t see why it won’t use the index when accessing that table.

Any ideas on how I can change this query to optimize as Oracle is doing? Thanks in advance.

Steve

QUERY:

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

( 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

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

(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

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

Postgres execution plan (again very ugly and somewhat difficult to read):

Nested Loop (cost=3465035.45..4564601.26 rows=1 width=345) (actual time=35125.205..35125.631 rows=1 loops=1)

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

Buffers: shared hit=734231, temp read=340089 written=629776

-> GroupAggregate (cost=3423317.32..4522882.70 rows=1 width=341) (actual time=35117.620..35117.989 rows=1 loops=1)

Group Key: judg1.judge_id

Buffers: shared hit=732655, temp read=340089 written=629776

-> Nested Loop Left Join (cost=3423317.32..4522882.01 rows=4 width=261) (actual time=30839.132..35117.682 rows=39 loops=1)

Join Filter: (sub2.judge_id = judg1.judge_id)

Rows Removed by Join Filter: 858300

Buffers: shared hit=732655, temp read=340089 written=629776

-> Nested Loop Left Join (cost=1504656.29..2084303.10 rows=2 width=205) (actual time=19156.871..21775.159 rows=3 loops=1)

Join Filter: (sub4.judge_id = judg1.judge_id)

Rows Removed by Join Filter: 55263

Buffers: shared hit=368082, temp read=183477 written=318607

-> Gather Merge (cost=5103.64..5103.76 rows=1 width=149) (actual time=12.582..12.688 rows=1 loops=1)

Workers Planned: 2

Workers Launched: 2

Buffers: shared hit=3525

-> Sort (cost=4103.62..4103.62 rows=1 width=149) (actual time=7.197..7.198 rows=0 loops=3)

Sort Key: judg1.judge_id

Sort Method: quicksort Memory: 25kB

Buffers: shared hit=3525

Worker 0: Sort Method: quicksort Memory: 25kB

Worker 1: Sort Method: quicksort Memory: 25kB

-> Parallel Seq Scan on judge judg1 (cost=0.00..4103.61 rows=1 width=149) (actual time=3.680..7.123 rows=0 loops=3)

Filter: (((jrt_opt_out_flag <> 'Y'::bpchar) OR (jrt_opt_out_flag IS NULL)) AND (wld_id = 1041192) AND (profile_id = 1))

Rows Removed by Filter: 30107

Buffers: shared hit=3445

-> GroupAggregate (cost=1499552.65..2075175.87 rows=178821 width=64) (actual time=16338.330..21757.982 rows=55266 loops=1)

Group Key: sub4.case_year_number, sub4.judge_wld_id, sub4.judge_id, sub4.grouping_flg

Buffers: shared hit=364557, temp read=183477 written=318607

-> Incremental Sort (cost=1499552.65..2051035.07 rows=1788207 width=56) (actual time=16338.158..20599.168 rows=4241689 loops=1)

Sort Key: sub4.case_year_number, sub4.judge_wld_id, sub4.judge_id, sub4.grouping_flg

Presorted Key: sub4.case_year_number, sub4.judge_wld_id, sub4.judge_id

Full-sort Groups: 27027 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB

Pre-sorted Groups: 35815 Sort Method: quicksort Average Memory: 367kB Peak Memory: 580kB

Buffers: shared hit=364557, temp read=183477 written=318607

-> Subquery Scan on sub4 (cost=1499549.72..1970473.37 rows=1788207 width=56) (actual time=16337.913..18343.845 rows=4241689 loops=1)

Buffers: shared hit=364557, temp read=183477 written=318607

-> Group (cost=1499549.72..1952591.30 rows=1788207 width=64) (actual time=16337.912..17955.054 rows=4241689 loops=1)

Group Key: jrtf1.case_year_number, jrtf1.higher_judge_wld_id, jrtf1.higher_judge_id, jrtf1.case_document_id

Buffers: shared hit=364557, temp read=183477 written=318607

-> Gather Merge (cost=1499549.72..1916827.16 rows=3576414 width=32) (actual time=16337.908..17161.246 rows=4281068 loops=1)

Workers Planned: 2

Workers Launched: 2

Buffers: shared hit=364557, temp read=183477 written=318607

-> Sort (cost=1498549.70..1503020.22 rows=1788207 width=32) (actual time=15566.878..16546.347 rows=1427023 loops=3)

Sort Key: jrtf1.case_year_number, jrtf1.higher_judge_wld_id, jrtf1.higher_judge_id, jrtf1.case_document_id

Sort Method: external sort Disk: 84832kB

Buffers: shared hit=364557, temp read=183477 written=318607

Worker 0: Sort Method: external merge Disk: 66904kB

Worker 1: Sort Method: external merge Disk: 63288kB

-> Partial HashAggregate (cost=1107519.33..1227268.67 rows=1788207 width=32) (actual time=5553.846..8719.426 rows=1427023 loops=3)

Group Key: jrtf1.case_year_number, jrtf1.higher_judge_wld_id, jrtf1.higher_judge_id, jrtf1.case_document_id

Planned Partitions: 64 Batches: 65 Memory Usage: 4129kB Disk Usage: 389200kB

Buffers: shared hit=364511, temp read=128224 written=263245

Worker 0: Batches: 65 Memory Usage: 4385kB Disk Usage: 379912kB

Worker 1: Batches: 65 Memory Usage: 4129kB Disk Usage: 331784kB

-> Parallel Append (cost=0.00..476524.37 rows=7450863 width=32) (actual time=0.005..1685.119 rows=5960690 loops=3)

Buffers: shared hit=364511

-> Parallel Seq Scan on jrt_fact_jrt_data_2012 jrtf1_23 (cost=0.00..23580.92 rows=400092 width=32) (actual time=0.003..189.362 rows=960222 loops=1)

Buffers: shared hit=19580

-> Parallel Seq Scan on jrt_fact_jrt_data_2011 jrtf1_22 (cost=0.00..22933.88 rows=389088 width=32) (actual time=0.003..182.503 rows=933812 loops=1)

Buffers: shared hit=19043

-> Parallel Seq Scan on jrt_fact_jrt_data_2009 jrtf1_20 (cost=0.00..22423.17 rows=380418 width=32) (actual time=0.005..175.891 rows=913002 loops=1)

Buffers: shared hit=18619

-> Parallel Seq Scan on jrt_fact_jrt_data_2010 jrtf1_21 (cost=0.00..22416.58 rows=380358 width=32) (actual time=0.005..178.698 rows=912858 loops=1)

Buffers: shared hit=18613

-> Parallel Seq Scan on jrt_fact_jrt_data_2014 jrtf1_25 (cost=0.00..22183.48 rows=376348 width=32) (actual time=0.004..175.463 rows=903235 loops=1)

Buffers: shared hit=18420

-> Parallel Seq Scan on jrt_fact_jrt_data_2013 jrtf1_24 (cost=0.00..22162.62 rows=376062 width=32) (actual time=0.003..176.993 rows=902548 loops=1)

Buffers: shared hit=18402

-> Parallel Seq Scan on jrt_fact_jrt_data_2008 jrtf1_19 (cost=0.00..21732.14 rows=368714 width=32) (actual time=0.003..171.364 rows=884913 loops=1)

Buffers: shared hit=18045

-> Parallel Seq Scan on jrt_fact_jrt_data_2016 jrtf1_27 (cost=0.00..21579.48 rows=366148 width=32) (actual time=0.004..172.011 rows=878756 loops=1)

Buffers: shared hit=17918

-> Parallel Seq Scan on jrt_fact_jrt_data_2015 jrtf1_26 (cost=0.00..20912.87 rows=354787 width=32) (actual time=0.004..166.035 rows=851489 loops=1)

Buffers: shared hit=17365

-> Parallel Seq Scan on jrt_fact_jrt_data_2020 jrtf1_31 (cost=0.00..20362.25 rows=345825 width=32) (actual time=0.004..163.649 rows=829980 loops=1)

Buffers: shared hit=16904

-> Parallel Seq Scan on jrt_fact_jrt_data_2018 jrtf1_29 (cost=0.00..19945.36 rows=338736 width=32) (actual time=0.006..158.181 rows=812966 loops=1)

Buffers: shared hit=16558

-> Parallel Seq Scan on jrt_fact_jrt_data_2017 jrtf1_28 (cost=0.00..19603.33 rows=332833 width=32) (actual time=0.004..148.015 rows=798799 loops=1)

Buffers: shared hit=16275

-> Parallel Seq Scan on jrt_fact_jrt_data_2007 jrtf1_18 (cost=0.00..19034.05 rows=322905 width=32) (actual time=0.004..50.705 rows=258324 loops=3)

Buffers: shared hit=15805

-> Parallel Seq Scan on jrt_fact_jrt_data_2021 jrtf1_32 (cost=0.00..18341.26 rows=311526 width=32) (actual time=0.012..75.774 rows=373832 loops=2)

Buffers: shared hit=15226

-> Parallel Seq Scan on jrt_fact_jrt_data_2006 jrtf1_17 (cost=0.00..18268.52 rows=309952 width=32) (actual time=0.003..147.744 rows=743884 loops=1)

Buffers: shared hit=15169

-> Parallel Seq Scan on jrt_fact_jrt_data_2005 jrtf1_16 (cost=0.00..17316.05 rows=293805 width=32) (actual time=0.003..140.591 rows=705132 loops=1)

Buffers: shared hit=14378

-> Parallel Seq Scan on jrt_fact_jrt_data_2019 jrtf1_30 (cost=0.00..17285.60 rows=293560 width=32) (actual time=0.003..139.968 rows=704543 loops=1)

Buffers: shared hit=14350

-> Parallel Seq Scan on jrt_fact_jrt_data_2022 jrtf1_33 (cost=0.00..16051.71 rows=272771 width=32) (actual time=0.003..131.590 rows=654650 loops=1)

Buffers: shared hit=13324

-> Parallel Seq Scan on jrt_fact_jrt_data_2004 jrtf1_15 (cost=0.00..15976.81 rows=271081 width=32) (actual time=0.004..128.956 rows=650595 loops=1)

Buffers: shared hit=13266

-> Parallel Seq Scan on jrt_fact_jrt_data_2003 jrtf1_14 (cost=0.00..15556.01 rows=264001 width=32) (actual time=0.003..124.530 rows=633603 loops=1)

Buffers: shared hit=12916

-> Parallel Seq Scan on jrt_fact_jrt_data_2002 jrtf1_13 (cost=0.00..14649.39 rows=248639 width=32) (actual time=0.003..120.979 rows=596733 loops=1)

Buffers: shared hit=12163

-> Parallel Seq Scan on jrt_fact_jrt_data_2001 jrtf1_12 (cost=0.00..12176.08 rows=206708 width=32) (actual time=0.004..96.162 rows=496100 loops=1)

Buffers: shared hit=10109

-> Parallel Seq Scan on jrt_fact_jrt_data_2000 jrtf1_11 (cost=0.00..10938.86 rows=185686 width=32) (actual time=0.004..87.876 rows=445646 loops=1)

Buffers: shared hit=9082

-> Parallel Seq Scan on jrt_fact_jrt_data_2023 jrtf1_34 (cost=0.00..3574.86 rows=80086 width=32) (actual time=0.002..27.656 rows=136147 loops=1)

Buffers: shared hit=2774

-> Parallel Seq Scan on jrt_fact_jrt_data_1999 jrtf1_10 (cost=0.00..182.73 rows=4073 width=32) (actual time=0.002..1.380 rows=6924 loops=1)

Buffers: shared hit=142

-> Parallel Seq Scan on jrt_fact_jrt_data_1990 jrtf1_1 (cost=0.00..42.31 rows=931 width=33) (actual time=0.002..0.292 rows=1583 loops=1)

Buffers: shared hit=33

-> Parallel Seq Scan on jrt_fact_jrt_data_1998 jrtf1_9 (cost=0.00..23.00 rows=500 width=33) (actual time=0.002..0.176 rows=850 loops=1)

Buffers: shared hit=18

-> Parallel Seq Scan on jrt_fact_jrt_data_1997 jrtf1_8 (cost=0.00..6.20 rows=120 width=32) (actual time=0.002..0.042 rows=204 loops=1)

Buffers: shared hit=5

-> Parallel Seq Scan on jrt_fact_jrt_data_1995 jrtf1_6 (cost=0.00..3.77 rows=77 width=33) (actual time=0.001..0.025 rows=131 loops=1)

Buffers: shared hit=3

-> Parallel Seq Scan on jrt_fact_jrt_data_1996 jrtf1_7 (cost=0.00..2.40 rows=40 width=33) (actual time=0.002..0.016 rows=68 loops=1)

Buffers: shared hit=2

-> Parallel Seq Scan on jrt_fact_jrt_data_1992 jrtf1_3 (cost=0.00..1.12 rows=12 width=33) (actual time=0.001..0.005 rows=20 loops=1)

Buffers: shared hit=1

-> Parallel Seq Scan on jrt_fact_jrt_data_1994 jrtf1_5 (cost=0.00..1.12 rows=12 width=33) (actual time=0.001..0.005 rows=20 loops=1)

Buffers: shared hit=1

-> Parallel Seq Scan on jrt_fact_jrt_data_1991 jrtf1_2 (cost=0.00..1.11 rows=11 width=33) (actual time=0.003..0.006 rows=19 loops=1)

Buffers: shared hit=1

-> Parallel Seq Scan on jrt_fact_jrt_data_1993 jrtf1_4 (cost=0.00..1.01 rows=1 width=31) (actual time=0.004..0.005 rows=1 loops=1)

Buffers: shared hit=1

-> Materialize (cost=1918661.03..2431740.34 rows=178821 width=64) (actual time=3447.156..4418.543 rows=286113 loops=3)

Buffers: shared hit=364573, temp read=156612 written=311169

-> Subquery Scan on sub2 (cost=1918661.03..2428925.23 rows=178821 width=64) (actual time=10341.437..13089.234 rows=286113 loops=1)

Buffers: shared hit=364573, temp read=153118 written=309422

-> GroupAggregate (cost=1918661.03..2427137.02 rows=178821 width=64) (actual time=10341.436..13057.326 rows=286113 loops=1)

Group Key: jrtf2.case_year_number, jrtf2.lower_judge_wld_id, jrtf2.lower_judge_id, (CASE WHEN (jrtf2.lower_judge_court_level_id = 1004) THEN 'T'::text ELSE 'A'::text END)

Buffers: shared hit=364573, temp read=153118 written=309422

-> Group (cost=1918661.03..2385114.15 rows=1788207 width=64) (actual time=10341.423..12590.803 rows=1610264 loops=1)

Group Key: jrtf2.case_year_number, jrtf2.lower_judge_wld_id, jrtf2.lower_judge_id, (CASE WHEN (jrtf2.lower_judge_court_level_id = 1004) THEN 'T'::text ELSE 'A'::text END), jrtf2.case_document_id

Buffers: shared hit=364573, temp read=153118 written=309422

-> Gather Merge (cost=1918661.03..2335938.46 rows=3576414 width=64) (actual time=10341.420..12232.748 rows=1623917 loops=1)

Workers Planned: 2

Workers Launched: 2

Buffers: shared hit=364573, temp read=153118 written=309422

-> Sort (cost=1917661.00..1922131.52 rows=1788207 width=64) (actual time=10205.573..10833.131 rows=541306 loops=3)

Sort Key: jrtf2.case_year_number, jrtf2.lower_judge_wld_id, jrtf2.lower_judge_id, (CASE WHEN (jrtf2.lower_judge_court_level_id = 1004) THEN 'T'::text ELSE 'A'::text END), jrtf2.case_document_id

Sort Method: external merge Disk: 32608kB

Buffers: shared hit=364573, temp read=153118 written=309422

Worker 0: Sort Method: external merge Disk: 30520kB

Worker 1: Sort Method: external merge Disk: 29224kB

-> Partial HashAggregate (cost=1415055.25..1597484.97 rows=1788207 width=64) (actual time=6980.371..9061.054 rows=541306 loops=3)

Group Key: jrtf2.case_year_number, jrtf2.lower_judge_wld_id, jrtf2.lower_judge_id, (CASE WHEN (jrtf2.lower_judge_court_level_id = 1004) THEN 'T'::text ELSE 'A'::text END), jrtf2.case_document_id

Planned Partitions: 128 Batches: 129 Memory Usage: 4633kB Disk Usage: 462856kB

Buffers: shared hit=364511, temp read=141574 written=297838

Worker 0: Batches: 129 Memory Usage: 4129kB Disk Usage: 411656kB

Worker 1: Batches: 129 Memory Usage: 4641kB Disk Usage: 389128kB

-> Parallel Append (cost=0.00..532593.66 rows=7450863 width=64) (actual time=0.006..1784.406 rows=5960690 loops=3)

Buffers: shared hit=364511

-> Parallel Seq Scan on jrt_fact_jrt_data_2012 jrtf2_23 (cost=0.00..26581.62 rows=400092 width=64) (actual time=0.005..216.086 rows=960222 loops=1)