|
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@xxxxxxxxx>
Could you send the the table’s Index scripts? Kimden: Dirschel, Steve <steve.dirschel@xxxxxxxxxxxxxxxxxx>
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:
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) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=19580 -> Parallel Seq Scan on jrt_fact_jrt_data_2011 jrtf2_22 (cost=0.00..25852.05 rows=389088 width=64) (actual time=0.004..205.289
rows=933812 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=19043 -> Parallel Seq Scan on jrt_fact_jrt_data_2009 jrtf2_20 (cost=0.00..25276.31 rows=380418 width=64) (actual time=0.006..195.529
rows=913002 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=18619 -> Parallel Seq Scan on jrt_fact_jrt_data_2010 jrtf2_21 (cost=0.00..25269.26 rows=380358 width=64) (actual time=0.007..198.928
rows=912858 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=18613 -> Parallel Seq Scan on jrt_fact_jrt_data_2014 jrtf2_25 (cost=0.00..25006.09 rows=376348 width=64) (actual time=0.005..193.307
rows=903235 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=18420 -> Parallel Seq Scan on jrt_fact_jrt_data_2013 jrtf2_24 (cost=0.00..24983.08 rows=376062 width=64) (actual time=0.005..199.091
rows=902548 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=18402 -> Parallel Seq Scan on jrt_fact_jrt_data_2008 jrtf2_19 (cost=0.00..24497.49 rows=368714 width=64) (actual time=0.006..189.346
rows=884913 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=18045 -> Parallel Seq Scan on jrt_fact_jrt_data_2016 jrtf2_27 (cost=0.00..24325.59 rows=366148 width=64) (actual time=0.006..195.816
rows=878756 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=17918 -> Parallel Seq Scan on jrt_fact_jrt_data_2015 jrtf2_26 (cost=0.00..23573.77 rows=354787 width=64) (actual time=0.006..186.719
rows=851489 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=17365 -> Parallel Seq Scan on jrt_fact_jrt_data_2020 jrtf2_31 (cost=0.00..22955.94 rows=345825 width=64) (actual time=0.005..184.122
rows=829980 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=16904 -> Parallel Seq Scan on jrt_fact_jrt_data_2018 jrtf2_29 (cost=0.00..22485.88 rows=338736 width=64) (actual time=0.006..173.993
rows=812966 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=16558 -> Parallel Seq Scan on jrt_fact_jrt_data_2017 jrtf2_28 (cost=0.00..22099.58 rows=332833 width=64) (actual time=0.006..176.179
rows=798799 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=16275 -> Parallel Seq Scan on jrt_fact_jrt_data_2007 jrtf2_18 (cost=0.00..21455.84 rows=322905 width=64) (actual time=0.007..82.994
rows=387486 loops=2) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=15805 -> Parallel Seq Scan on jrt_fact_jrt_data_2021 jrtf2_32 (cost=0.00..20677.71 rows=311526 width=64) (actual time=0.011..86.729
rows=373832 loops=2) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=15226 -> Parallel Seq Scan on jrt_fact_jrt_data_2006 jrtf2_17 (cost=0.00..20593.16 rows=309952 width=64) (actual time=0.005..160.160
rows=743884 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=15169 -> Parallel Seq Scan on jrt_fact_jrt_data_2005 jrtf2_16 (cost=0.00..19519.59 rows=293805 width=64) (actual time=0.006..153.430
rows=705132 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=14378 -> Parallel Seq Scan on jrt_fact_jrt_data_2019 jrtf2_30 (cost=0.00..19487.29 rows=293560 width=64) (actual time=0.004..156.767
rows=704543 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=14350 -> Parallel Seq Scan on jrt_fact_jrt_data_2022 jrtf2_33 (cost=0.00..18097.49 rows=272771 width=64) (actual time=0.006..147.439
rows=654650 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=13324 -> Parallel Seq Scan on jrt_fact_jrt_data_2004 jrtf2_15 (cost=0.00..18009.92 rows=271081 width=64) (actual time=0.005..143.933
rows=650595 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=13266 -> Parallel Seq Scan on jrt_fact_jrt_data_2003 jrtf2_14 (cost=0.00..17536.02 rows=264001 width=64) (actual time=0.006..140.594
rows=633603 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=12916 -> Parallel Seq Scan on jrt_fact_jrt_data_2002 jrtf2_13 (cost=0.00..16514.18 rows=248639 width=64) (actual time=0.005..132.336
rows=596733 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=12163 -> Parallel Seq Scan on jrt_fact_jrt_data_2001 jrtf2_12 (cost=0.00..13726.40 rows=206708 width=64) (actual time=0.005..109.974
rows=496100 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=10109 -> Parallel Seq Scan on jrt_fact_jrt_data_2000 jrtf2_11 (cost=0.00..12331.50 rows=185686 width=64) (actual time=0.005..98.570
rows=445646 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=9082 -> Parallel Seq Scan on jrt_fact_jrt_data_2023 jrtf2_34 (cost=0.00..4175.51 rows=80086 width=64) (actual time=0.003..31.980
rows=136147 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=2774 -> Parallel Seq Scan on jrt_fact_jrt_data_1999 jrtf2_10 (cost=0.00..213.28 rows=4073 width=64) (actual time=0.003..1.614
rows=6924 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=142 -> Parallel Seq Scan on jrt_fact_jrt_data_1990 jrtf2_1 (cost=0.00..49.30 rows=931 width=64) (actual time=0.003..0.370
rows=1583 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=33 -> Parallel Seq Scan on jrt_fact_jrt_data_1998 jrtf2_9 (cost=0.00..26.75 rows=500 width=64) (actual time=0.003..0.199
rows=850 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=18 -> Parallel Seq Scan on jrt_fact_jrt_data_1997 jrtf2_8 (cost=0.00..7.10 rows=120 width=64) (actual time=0.003..0.049
rows=204 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=5 -> Parallel Seq Scan on jrt_fact_jrt_data_1995 jrtf2_6 (cost=0.00..4.35 rows=77 width=64) (actual time=0.002..0.030
rows=131 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=3 -> Parallel Seq Scan on jrt_fact_jrt_data_1996 jrtf2_7 (cost=0.00..2.70 rows=40 width=64) (actual time=0.003..0.018
rows=68 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=2 -> Parallel Seq Scan on jrt_fact_jrt_data_1992 jrtf2_3 (cost=0.00..1.21 rows=12 width=64) (actual time=0.002..0.006
rows=20 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=1 -> Parallel Seq Scan on jrt_fact_jrt_data_1994 jrtf2_5 (cost=0.00..1.21 rows=12 width=64) (actual time=0.002..0.006
rows=20 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=1 -> Parallel Seq Scan on jrt_fact_jrt_data_1991 jrtf2_2 (cost=0.00..1.20 rows=11 width=64) (actual time=0.003..0.007
rows=19 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=1 -> Parallel Seq Scan on jrt_fact_jrt_data_1993 jrtf2_4 (cost=0.00..1.01 rows=1 width=64) (actual time=0.008..0.009
rows=1 loops=1) Filter: ((lower_judge_court_level_id > 1000) AND (lower_judge_court_level_id <= 1004)) Buffers: shared hit=1 -> Limit (cost=41718.13..41718.30 rows=11 width=12) (actual time=7.581..7.636 rows=1 loops=1) Buffers: shared hit=1576 -> HashAggregate (cost=41718.13..41721.13 rows=200 width=12) (actual time=7.580..7.635 rows=1 loops=1) 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=1576 -> Unique (cost=41305.59..41511.86 rows=11787 width=44) (actual time=6.612..7.496 rows=763 loops=1) Buffers: shared hit=1576 -> Sort (cost=41305.59..41335.06 rows=11787 width=44) (actual time=6.611..6.870 rows=4564 loops=1) 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 (j rtf.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=1576 -> Append (cost=8.57..40508.50 rows=11787 width=44) (actual time=0.450..4.946 rows=4564 loops=1) Buffers: shared hit=1573 -> Bitmap Heap Scan on jrt_fact_jrt_data_1990 jrtf_1 (cost=8.57..12.59 rows=1 width=44) (actual time=0.017..0.019 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (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.016 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.008..0.008 rows=0
loops=1) Index Cond: (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.006..0.006 rows=0
loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=2 -> Seq Scan on jrt_fact_jrt_data_1991 jrtf_2 (cost=0.00..1.29 rows=1 width=44) (actual time=0.012..0.013 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 19 Buffers: shared hit=1 -> Seq Scan on jrt_fact_jrt_data_1992 jrtf_3 (cost=0.00..1.31 rows=1 width=44) (actual time=0.009..0.009 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 20 Buffers: shared hit=1 -> Seq Scan on jrt_fact_jrt_data_1993 jrtf_4 (cost=0.00..1.02 rows=1 width=44) (actual time=0.003..0.004 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 1 Buffers: shared hit=1 -> Seq Scan on jrt_fact_jrt_data_1994 jrtf_5 (cost=0.00..1.31 rows=1 width=44) (actual time=0.009..0.009 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 20 Buffers: shared hit=1 -> Seq Scan on jrt_fact_jrt_data_1995 jrtf_6 (cost=0.00..4.97 rows=1 width=44) (actual time=0.058..0.058 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 131 Buffers: shared hit=3 -> Seq Scan on jrt_fact_jrt_data_1996 jrtf_7 (cost=0.00..3.03 rows=1 width=44) (actual time=0.026..0.026 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 68 Buffers: shared hit=2 -> Seq Scan on jrt_fact_jrt_data_1997 jrtf_8 (cost=0.00..8.07 rows=1 width=44) (actual time=0.059..0.059 rows=0 loops=1) Filter: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Rows Removed by Filter: 204 Buffers: shared hit=5 -> Bitmap Heap Scan on jrt_fact_jrt_data_1998 jrtf_9 (cost=8.57..14.62 rows=2 width=44) (actual time=0.012..0.013 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=4 -> BitmapOr (cost=8.57..8.57 rows=2 width=0) (actual time=0.011..0.012 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.005..0.005 rows=0
loops=1) Index Cond: (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.005..0.005 rows=0
loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=2 -> Bitmap Heap Scan on jrt_fact_jrt_data_1999 jrtf_10 (cost=8.68..51.80 rows=14 width=44) (actual time=0.014..0.016 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=4 -> BitmapOr (cost=8.68..8.68 rows=14 width=0) (actual time=0.013..0.015 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.006..0.006 rows=0
loops=1) Index Cond: (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.006..0.006 rows=0
loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=2 -> Bitmap Heap Scan on jrt_fact_jrt_data_2000 jrtf_11 (cost=10.74..839.39 rows=237 width=44) (actual time=0.018..0.019 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=10.74..10.74 rows=237 width=0) (actual time=0.017..0.018 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.009..0.009 rows=0
loops=1) Index Cond: (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.008 rows=0
loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2001 jrtf_12 (cost=10.87..898.62 rows=253 width=44) (actual time=0.017..0.018 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (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.008 rows=0
loops=1) Index Cond: (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.008 rows=0
loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2002 jrtf_13 (cost=15.27..1074.10 rows=301 width=44) (actual time=0.018..0.020 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=15.27..15.27 rows=301 width=0) (actual time=0.017..0.018 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.009..0.009 rows=0
loops=1) Index Cond: (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.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2003 jrtf_14 (cost=15.46..1158.27 rows=326 width=44) (actual time=0.018..0.020 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=15.46..15.46 rows=326 width=0) (actual time=0.017..0.018 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.008..0.009 rows=0
loops=1) Index Cond: (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.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2004 jrtf_15 (cost=15.55..1196.85 rows=337 width=44) (actual time=0.019..0.021 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=15.55..15.55 rows=337 width=0) (actual time=0.018..0.019 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.007..0.007 rows=0
loops=1) Index Cond: (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.010..0.010
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2005 jrtf_16 (cost=15.85..1329.50 rows=375 width=44) (actual time=0.017..0.019 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=15.85..15.85 rows=375 width=0) (actual time=0.016..0.017 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.007..0.008 rows=0
loops=1) Index Cond: (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.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2006 jrtf_17 (cost=15.98..1387.33 rows=391 width=44) (actual time=0.019..0.020 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=15.98..15.98 rows=391 width=0) (actual time=0.018..0.019 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.008..0.008 rows=0
loops=1) Index Cond: (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.009..0.009
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2007 jrtf_18 (cost=16.09..1434.30 rows=405 width=44) (actual time=0.017..0.019 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (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.008..0.008 rows=0
loops=1) Index Cond: (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.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2008 jrtf_19 (cost=16.54..1633.45 rows=461 width=44) (actual time=0.018..0.019 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=16.54..16.54 rows=461 width=0) (actual time=0.016..0.018 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.008..0.008 rows=0
loops=1) Index Cond: (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.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2009 jrtf_20 (cost=16.65..1683.32 rows=475 width=44) (actual time=0.018..0.019 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=16.65..16.65 rows=475 width=0) (actual time=0.017..0.018 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2009_lower_judge_wld_id_idx (cost=0.00..5.12 rows=93 width=0) (actual time=0.008..0.008 rows=0
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2009_higher_judge_wld_id_idx (cost=0.00..11.29 rows=382 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2010 jrtf_21 (cost=16.63..1673.33 rows=472 width=44) (actual time=0.018..0.020 rows=0 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Buffers: shared hit=6 -> BitmapOr (cost=16.63..16.63 rows=472 width=0) (actual time=0.017..0.018 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2010_lower_judge_wld_id_idx (cost=0.00..5.13 rows=94 width=0) (actual time=0.008..0.008 rows=0
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2010_higher_judge_wld_id_idx (cost=0.00..11.26 rows=378 width=0) (actual time=0.007..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2011 jrtf_22 (cost=16.91..1791.83 rows=508 width=44) (actual time=0.024..0.026 rows=2 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=1 Buffers: shared hit=7 -> BitmapOr (cost=16.91..16.91 rows=508 width=0) (actual time=0.017..0.018 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2011_lower_judge_wld_id_idx (cost=0.00..5.14 rows=95 width=0) (actual time=0.009..0.009 rows=2
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2011_higher_judge_wld_id_idx (cost=0.00..11.52 rows=413 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2012 jrtf_23 (cost=16.99..1828.27 rows=518 width=44) (actual time=0.052..0.142 rows=187 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=19 Buffers: shared hit=25 -> BitmapOr (cost=16.99..16.99 rows=518 width=0) (actual time=0.042..0.043 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2012_lower_judge_wld_id_idx (cost=0.00..5.16 rows=98 width=0) (actual time=0.028..0.028 rows=150
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2012_higher_judge_wld_id_idx (cost=0.00..11.58 rows=420 width=0) (actual time=0.013..0.013
rows=37 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2013 jrtf_24 (cost=16.81..1744.81 rows=495 width=44) (actual time=0.063..0.170 rows=210 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=31 Buffers: shared hit=37 -> BitmapOr (cost=16.81..16.81 rows=495 width=0) (actual time=0.052..0.053 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2013_lower_judge_wld_id_idx (cost=0.00..5.12 rows=93 width=0) (actual time=0.043..0.044 rows=210
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2013_higher_judge_wld_id_idx (cost=0.00..11.44 rows=402 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2014 jrtf_25 (cost=16.83..1754.81 rows=498 width=44) (actual time=0.047..0.138 rows=175 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=25 Buffers: shared hit=31 -> BitmapOr (cost=16.83..16.83 rows=498 width=0) (actual time=0.038..0.039 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2014_lower_judge_wld_id_idx (cost=0.00..5.13 rows=94 width=0) (actual time=0.029..0.030 rows=175
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2014_higher_judge_wld_id_idx (cost=0.00..11.46 rows=404 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2015 jrtf_26 (cost=16.70..1695.12 rows=482 width=44) (actual time=0.056..0.183 rows=268 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=28 Buffers: shared hit=34 -> BitmapOr (cost=16.70..16.70 rows=482 width=0) (actual time=0.047..0.048 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2015_lower_judge_wld_id_idx (cost=0.00..5.09 rows=89 width=0) (actual time=0.038..0.038 rows=268
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2015_higher_judge_wld_id_idx (cost=0.00..11.37 rows=392 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2016 jrtf_27 (cost=39.15..4057.93 rows=1286 width=44) (actual time=0.108..0.392 rows=589 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=69 Buffers: shared hit=78 -> BitmapOr (cost=39.15..39.15 rows=1287 width=0) (actual time=0.094..0.095 rows=0 loops=1) Buffers: shared hit=9 -> Bitmap Index Scan on jrt_fact_jrt_data_2016_lower_judge_wld_id_idx (cost=0.00..27.02 rows=879 width=0) (actual time=0.086..0.086 rows=589
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2016_higher_judge_wld_id_idx (cost=0.00..11.49 rows=408 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2017 jrtf_28 (cost=16.47..1592.43 rows=453 width=44) (actual time=0.073..0.237 rows=305 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=49 Buffers: shared hit=57 -> BitmapOr (cost=16.47..16.47 rows=453 width=0) (actual time=0.061..0.062 rows=0 loops=1) Buffers: shared hit=8 -> Bitmap Index Scan on jrt_fact_jrt_data_2017_lower_judge_wld_id_idx (cost=0.00..5.09 rows=89 width=0) (actual time=0.053..0.053 rows=305
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=5 -> Bitmap Index Scan on jrt_fact_jrt_data_2017_higher_judge_wld_id_idx (cost=0.00..11.16 rows=364 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2018 jrtf_29 (cost=16.52..1614.07 rows=459 width=44) (actual time=0.080..0.267 rows=379 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=45 Buffers: shared hit=53 -> BitmapOr (cost=16.52..16.52 rows=459 width=0) (actual time=0.069..0.070 rows=0 loops=1) Buffers: shared hit=8 -> Bitmap Index Scan on jrt_fact_jrt_data_2018_lower_judge_wld_id_idx (cost=0.00..5.09 rows=89 width=0) (actual time=0.060..0.060 rows=379
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=5 -> Bitmap Index Scan on jrt_fact_jrt_data_2018_higher_judge_wld_id_idx (cost=0.00..11.20 rows=370 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2019 jrtf_30 (cost=31.97..2869.06 rows=889 width=44) (actual time=0.118..0.360 rows=495 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=63 Buffers: shared hit=71 -> BitmapOr (cost=31.97..31.97 rows=889 width=0) (actual time=0.106..0.106 rows=0 loops=1) Buffers: shared hit=8 -> Bitmap Index Scan on jrt_fact_jrt_data_2019_lower_judge_wld_id_idx (cost=0.00..20.66 rows=564 width=0) (actual time=0.097..0.097 rows=495
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=5 -> Bitmap Index Scan on jrt_fact_jrt_data_2019_higher_judge_wld_id_idx (cost=0.00..10.87 rows=326 width=0) (actual time=0.008..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2020 jrtf_31 (cost=38.45..3791.01 rows=1200 width=44) (actual time=0.188..0.778 rows=937 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=239 Buffers: shared hit=249 -> BitmapOr (cost=38.45..38.45 rows=1200 width=0) (actual time=0.157..0.158 rows=0 loops=1) Buffers: shared hit=10 -> Bitmap Index Scan on jrt_fact_jrt_data_2020_lower_judge_wld_id_idx (cost=0.00..26.44 rows=802 width=0) (actual time=0.149..0.149 rows=937
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=7 -> Bitmap Index Scan on jrt_fact_jrt_data_2020_higher_judge_wld_id_idx (cost=0.00..11.41 rows=398 width=0) (actual time=0.007..0.008
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2021 jrtf_32 (cost=16.44..1571.85 rows=449 width=44) (actual time=0.161..0.698 rows=517 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=374 Buffers: shared hit=382 -> BitmapOr (cost=16.44..16.44 rows=449 width=0) (actual time=0.115..0.116 rows=0 loops=1) Buffers: shared hit=8 -> Bitmap Index Scan on jrt_fact_jrt_data_2021_lower_judge_wld_id_idx (cost=0.00..5.04 rows=82 width=0) (actual time=0.091..0.091 rows=517
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=5 -> Bitmap Index Scan on jrt_fact_jrt_data_2021_higher_judge_wld_id_idx (cost=0.00..11.18 rows=367 width=0) (actual time=0.023..0.023
rows=0 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2022 jrtf_33 (cost=15.99..1374.94 rows=392 width=44) (actual time=0.119..0.478 rows=319 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=284 Buffers: shared hit=290 -> BitmapOr (cost=15.99..15.99 rows=392 width=0) (actual time=0.083..0.084 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2022_lower_judge_wld_id_idx (cost=0.00..4.98 rows=74 width=0) (actual time=0.024..0.024 rows=129
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2022_higher_judge_wld_id_idx (cost=0.00..10.81 rows=318 width=0) (actual time=0.058..0.058
rows=190 loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Heap Scan on jrt_fact_jrt_data_2023 jrtf_34 (cost=9.66..354.97 rows=101 width=44) (actual time=0.065..0.266 rows=181 loops=1) Recheck Cond: ((lower_judge_wld_id = '104119201'::numeric) OR (higher_judge_wld_id = '104119201'::numeric)) Heap Blocks: exact=161 Buffers: shared hit=167 -> BitmapOr (cost=9.66..9.66 rows=101 width=0) (actual time=0.041..0.042 rows=0 loops=1) Buffers: shared hit=6 -> Bitmap Index Scan on jrt_fact_jrt_data_2023_lower_judge_wld_id_idx (cost=0.00..4.60 rows=24 width=0) (actual time=0.014..0.014 rows=37
loops=1) Index Cond: (lower_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 -> Bitmap Index Scan on jrt_fact_jrt_data_2023_higher_judge_wld_id_idx (cost=0.00..5.00 rows=78 width=0) (actual time=0.026..0.026 rows=144
loops=1) Index Cond: (higher_judge_wld_id = '104119201'::numeric) Buffers: shared hit=3 Planning: Buffers: shared hit=9400 Planning Time: 24.631 ms Execution Time: 35251.529 ms (614 rows) ORACLE PLAN: Plan hash value: 3488078505 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 5259 | | | | |* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.03 | 5259 | 872K| 872K| 455K (0)| | 2 | VIEW | | 1 | 1 | 1 |00:00:00.02 | 3693 | | | | | 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.02 | 3693 | 734K| 734K| 514K (0)| | 4 | NESTED LOOPS OUTER | | 1 | 29525 | 39 |00:00:00.02 | 3693 | | | | | 5 | NESTED LOOPS OUTER | | 1 | 150 | 3 |00:00:00.01 | 513 | | | | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | JUDGE | 1 | 1 | 1 |00:00:00.01 | 3 | | | | |* 7 | INDEX RANGE SCAN | JUDGE_WLD_PROF_IDX | 1 | 1 | 1 |00:00:00.01 | 2 | | | | | 8 | VIEW PUSHED PREDICATE | | 1 | 1 | 3 |00:00:00.01 | 510 | | | | | 9 | SORT GROUP BY | | 1 | 2228 | 3 |00:00:00.01 | 510 | 2048 | 2048 | 2048 (0)| | 10 | VIEW | VM_NWVW_1 | 1 | 2228 | 87 |00:00:00.01 | 510 | | | | | 11 | SORT GROUP BY | | 1 | 2228 | 87 |00:00:00.01 | 510 | 9216 | 9216 | 8192 (0)| | 12 | PARTITION RANGE ALL | | 1 | 2228 | 540 |00:00:00.01 | 510 | | | | | 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JRT_FACT | 34 | 2228 | 540 |00:00:00.01 | 510 | | | | |* 14 | INDEX RANGE SCAN | JRT_FACT_IDX05 | 34 | 2228 | 540 |00:00:00.01 | 86 | | | | | 15 | VIEW PUSHED PREDICATE | | 3 | 1 | 39 |00:00:00.02 | 3180 | | | | | 16 | SORT GROUP BY | | 3 | 481 | 39 |00:00:00.02 | 3180 | 2048 | 2048 | 2048 (0)| | 17 | VIEW | VM_NWVW_0 | 3 | 481 | 813 |00:00:00.02 | 3180 | | | | | 18 | SORT GROUP BY | | 3 | 481 | 813 |00:00:00.02 | 3180 | 24576 | 24576 |22528 (0)| |* 19 | FILTER | | 3 | | 12669 |00:00:00.01 | 3180 | | | | | 20 | PARTITION RANGE ALL | | 3 | 481 | 12669 |00:00:00.01 | 3180 | | | | |* 21 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JRT_FACT | 102 | 481 | 12669 |00:00:00.01 | 3180 | | | | |* 22 | INDEX RANGE SCAN | JRT_FACT_IDX02 | 102 | 481 | 12669 |00:00:00.01 | 288 | | | | | 23 | VIEW | | 1 | 11 | 1 |00:00:00.01 | 1566 | | | | | 24 | HASH GROUP BY | | 1 | 11 | 1 |00:00:00.01 | 1566 | 1097K| 1097K| 514K (0)| |* 25 | COUNT STOPKEY | | 1 | | 11 |00:00:00.01 | 1566 | | | | | 26 | VIEW | | 1 | 2689 | 11 |00:00:00.01 | 1566 | | | | |* 27 | SORT UNIQUE STOPKEY | | 1 | 2689 | 11 |00:00:00.01 | 1566 | 2048 | 2048 | 2048 (0)| | 28 | PARTITION RANGE ALL | | 1 | 2689 | 4763 |00:00:00.01 | 1566 | | | | | 29 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | JRT_FACT | 34 | 2689 | 4763 |00:00:00.01 | 1566 | | | | | 30 | BITMAP CONVERSION TO ROWIDS | | 34 | | 4763 |00:00:00.01 | 186 | | | | | 31 | BITMAP OR | | 34 | | 13 |00:00:00.01 | 186 | | | | | 32 | BITMAP CONVERSION FROM ROWIDS | | 34 | | 3 |00:00:00.01 | 88 | | | | |* 33 | INDEX RANGE SCAN | JRT_FACT_IDX13 | 34 | | 540 |00:00:00.01 | 88 | | | | | 34 | BITMAP CONVERSION FROM ROWIDS | | 34 | | 13 |00:00:00.01 | 98 | | | | |* 35 | INDEX RANGE SCAN | JRT_FACT_IDX12 | 34 | | 4223 |00:00:00.01 | 98 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("AGG_SUB"."JUDGE_ID"="APPELLATE_FLAG_SUB"."JUDGE_ID") 6 - filter(("JUDG1"."JRT_OPT_OUT_FLAG" IS NULL OR "JUDG1"."JRT_OPT_OUT_FLAG"<>:SYS_B_17)) 7 - access("JUDG1"."WLD_ID"=:SYS_B_15 AND "JUDG1"."PROFILE_ID"=:SYS_B_16) 14 - access("JRTF1"."HIGHER_JUDGE_ID"="JUDG1"."JUDGE_ID") 19 - filter(:SYS_B_23>:SYS_B_22) 21 - filter(("JRTF2"."LOWER_JUDGE_COURT_LEVEL_ID">:SYS_B_22 AND "JRTF2"."LOWER_JUDGE_COURT_LEVEL_ID"<=:SYS_B_23)) 22 - access("JRTF2"."LOWER_JUDGE_ID"="JUDG1"."JUDGE_ID") 25 - filter(ROWNUM<=:SYS_B_37) 27 - filter(ROWNUM<=:SYS_B_37) 33 - access("HIGHER_JUDGE_WLD_ID"=:SYS_B_36) 35 - access("LOWER_JUDGE_WLD_ID"=:SYS_B_35) Postgres table definitions: wldomart01a=> \d+ wln_mart.judge Table "wln_mart.judge" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------------------+--------------------------------+-----------+----------+-------------+----------+--------------+------------- judge_id | bigint | | not null | | plain | | display_name | character varying(255) | | not null | | extended | | first_name | character varying(50) | | | | extended | | middle_name | character varying(50) | | | | extended | | sur_name | character varying(50) | | | | extended | | title | character varying(10) | | | | extended | | prof_suffix | character varying(10) | | | | extended | | wld_id | bigint | | | | plain | | profile_id | bigint | | | | plain | | search_name | character varying(255) | | not null | | extended | | active_flag | character(1) | | not null | | extended | | legal_event_count | bigint | | | | plain | | profile_type | character(1) | | not null | 'L'::bpchar | extended | | court_name | character varying(255) | | | | extended | | address | character varying(255) | | | | extended | | legal_event_total_count | bigint | | | | plain | | legal_event_first_year | bigint | | | | plain | | legal_event_last_year | bigint | | | | plain | | state_id | bigint | | | | plain | | ec_fact_first_year | bigint | | | | plain | | judge_padb_code | character varying(33) | | | | extended | | ec_fact_count | bigint | | | | plain | | ec_fact_totalcount | bigint | | | | plain | | ee_fact_totalcount | bigint | | | | plain | | ec_fact_last_year | bigint | | | | plain | | jmt_total_count | bigint | | | | plain | | jrt_total_count | bigint | | | | plain | | jrt_opt_out_flag | character(1) | | | | extended | | jmt_opt_out_flag | character(1) | | | | extended | | judge_court_level | character varying(50) | | | | extended | | jrt_trial_count | bigint | | | | plain | | jrt_appellate_count | bigint | | | | plain | | city_name | character varying(255) | | | | extended | | zip_code | character varying(20) | | | | extended | | created_on | timestamp(0) without time zone | | | | plain | | changed_on | timestamp(0) without time zone | | | | plain | | Indexes: "pk_judge" PRIMARY KEY, btree (judge_id) "idx_jdg_low_fname" btree (lower(first_name::text)) "idx_jdg_low_surname" btree (lower(sur_name::text)) "idx_judge_first_name" btree (first_name) "idx_judge_search_name" btree (search_name) "idx_judge_sur_name" btree (sur_name) "judge_st_id" btree (state_id) "judge_wld_prof_idx" btree (wld_id, profile_id) Foreign-key constraints: "judge_state_id_fk" FOREIGN KEY (state_id) REFERENCES wln_mart.state_code(state_id) Referenced by: TABLE "wln_mart.ec_fact" CONSTRAINT "ec_fact_judge_fk" FOREIGN KEY (judge_id) REFERENCES wln_mart.judge(judge_id) TABLE "wln_mart.ee_fact" CONSTRAINT "ee_judge_fk" FOREIGN KEY (judge_id) REFERENCES wln_mart.judge(judge_id) TABLE "wln_mart.ea_judge_doc_fact" CONSTRAINT "fk_judge_doc_judge_id" FOREIGN KEY (judge_id) REFERENCES wln_mart.judge(judge_id) TABLE "wln_mart.jmt_fact" CONSTRAINT "jmt_fact_fk01" FOREIGN KEY (judge_id) REFERENCES wln_mart.judge(judge_id) TABLE "wln_mart.jrt_fact" CONSTRAINT "jrt_fact_fk02" FOREIGN KEY (lower_judge_id) REFERENCES wln_mart.judge(judge_id) TABLE "wln_mart.jrt_fact" CONSTRAINT "jrt_fact_fk04" FOREIGN KEY (higher_judge_id) REFERENCES wln_mart.judge(judge_id) TABLE "wln_mart.lhr_fact" CONSTRAINT "lhr_judge_id_fk01" FOREIGN KEY (judge_id) REFERENCES wln_mart.judge(judge_id) Access method: heap wldomart01a=> \d+ wln_mart.jrt_fact Partitioned table "wln_mart.jrt_fact" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------------------+--------------------------------+-----------+----------+---------+----------+--------------+------------- jrt_fact_id | bigint | | not null | | plain | | case_document_id | double precision | | not null | | plain | | lower_judge_id | bigint | | not null | | plain | | appealed_case_ruling_id | bigint | | not null | | plain | | higher_judge_id | bigint | | not null | | plain | | lower_judge_court_level_id | bigint | | not null | | plain | | majority_judge_flag | character(1) | | not null | | extended | | attorney_id | bigint | | not null | | plain | | firm_id | bigint | | not null | | plain | | case_date_id | bigint | | not null | | plain | | case_year_number | bigint | | not null | | plain | | lower_judge_wld_id | numeric(10,0) | | | | main | | higher_judge_wld_id | numeric(10,0) | | | | main | | created_on | timestamp(0) without time zone | | | | plain | | changed_on | timestamp(0) without time zone | | | | plain | | court_id | double precision | | not null | 1 | plain | | Partition key: RANGE (case_year_number) Indexes: "pk_jrt_fact" PRIMARY KEY, btree (jrt_fact_id, case_year_number) "jrt_fact_idx01" btree (case_document_id) "jrt_fact_idx02" btree (lower_judge_id) "jrt_fact_idx04" btree (appealed_case_ruling_id) "jrt_fact_idx05" btree (higher_judge_id) "jrt_fact_idx06" btree (lower_judge_court_level_id) "jrt_fact_idx07" btree (majority_judge_flag) "jrt_fact_idx08" btree (case_date_id) "jrt_fact_idx09" btree (attorney_id) "jrt_fact_idx10" btree (firm_id) "jrt_fact_idx11" btree (case_year_number) "jrt_fact_idx12" btree (lower_judge_wld_id) "jrt_fact_idx13" btree (higher_judge_wld_id) Foreign-key constraints: "jrt_fact_fk02" FOREIGN KEY (lower_judge_id) REFERENCES wln_mart.judge(judge_id) "jrt_fact_fk03" FOREIGN KEY (appealed_case_ruling_id) REFERENCES wln_mart.appealed_case_ruling(appealed_case_ruling_id) "jrt_fact_fk04" FOREIGN KEY (higher_judge_id) REFERENCES wln_mart.judge(judge_id) "jrt_fact_fk05" FOREIGN KEY (lower_judge_court_level_id) REFERENCES wln_mart.lower_judge_court_level(lower_judge_court_level_id) "jrt_fact_fk06" FOREIGN KEY (attorney_id) REFERENCES wln_mart.attorney(attorney_id) "jrt_fact_fk07" FOREIGN KEY (firm_id) REFERENCES wln_mart.firm(firm_id) "jrt_fact_fk08" FOREIGN KEY (case_date_id) REFERENCES wln_mart.date_code(date_id) Partitions: wln_mart.jrt_fact_jrt_data_1990 FOR VALUES FROM (MINVALUE) TO ('1991'), wln_mart.jrt_fact_jrt_data_1991 FOR VALUES FROM ('1991') TO ('1992'), wln_mart.jrt_fact_jrt_data_1992 FOR VALUES FROM ('1992') TO ('1993'), wln_mart.jrt_fact_jrt_data_1993 FOR VALUES FROM ('1993') TO ('1994'), wln_mart.jrt_fact_jrt_data_1994 FOR VALUES FROM ('1994') TO ('1995'), wln_mart.jrt_fact_jrt_data_1995 FOR VALUES FROM ('1995') TO ('1996'), wln_mart.jrt_fact_jrt_data_1996 FOR VALUES FROM ('1996') TO ('1997'), wln_mart.jrt_fact_jrt_data_1997 FOR VALUES FROM ('1997') TO ('1998'), wln_mart.jrt_fact_jrt_data_1998 FOR VALUES FROM ('1998') TO ('1999'), wln_mart.jrt_fact_jrt_data_1999 FOR VALUES FROM ('1999') TO ('2000'), wln_mart.jrt_fact_jrt_data_2000 FOR VALUES FROM ('2000') TO ('2001'), wln_mart.jrt_fact_jrt_data_2001 FOR VALUES FROM ('2001') TO ('2002'), wln_mart.jrt_fact_jrt_data_2002 FOR VALUES FROM ('2002') TO ('2003'), wln_mart.jrt_fact_jrt_data_2003 FOR VALUES FROM ('2003') TO ('2004'), wln_mart.jrt_fact_jrt_data_2004 FOR VALUES FROM ('2004') TO ('2005'), wln_mart.jrt_fact_jrt_data_2005 FOR VALUES FROM ('2005') TO ('2006'), wln_mart.jrt_fact_jrt_data_2006 FOR VALUES FROM ('2006') TO ('2007'), wln_mart.jrt_fact_jrt_data_2007 FOR VALUES FROM ('2007') TO ('2008'), wln_mart.jrt_fact_jrt_data_2008 FOR VALUES FROM ('2008') TO ('2009'), wln_mart.jrt_fact_jrt_data_2009 FOR VALUES FROM ('2009') TO ('2010'), wln_mart.jrt_fact_jrt_data_2010 FOR VALUES FROM ('2010') TO ('2011'), wln_mart.jrt_fact_jrt_data_2011 FOR VALUES FROM ('2011') TO ('2012'), wln_mart.jrt_fact_jrt_data_2012 FOR VALUES FROM ('2012') TO ('2013'), wln_mart.jrt_fact_jrt_data_2013 FOR VALUES FROM ('2013') TO ('2014'), wln_mart.jrt_fact_jrt_data_2014 FOR VALUES FROM ('2014') TO ('2015'), wln_mart.jrt_fact_jrt_data_2015 FOR VALUES FROM ('2015') TO ('2016'), wln_mart.jrt_fact_jrt_data_2016 FOR VALUES FROM ('2016') TO ('2017'), wln_mart.jrt_fact_jrt_data_2017 FOR VALUES FROM ('2017') TO ('2018'), wln_mart.jrt_fact_jrt_data_2018 FOR VALUES FROM ('2018') TO ('2019'), wln_mart.jrt_fact_jrt_data_2019 FOR VALUES FROM ('2019') TO ('2020'), wln_mart.jrt_fact_jrt_data_2020 FOR VALUES FROM ('2020') TO ('2021'), wln_mart.jrt_fact_jrt_data_2021 FOR VALUES FROM ('2021') TO ('2022'), wln_mart.jrt_fact_jrt_data_2022 FOR VALUES FROM ('2022') TO ('2023'), wln_mart.jrt_fact_jrt_data_2023 FOR VALUES FROM ('2023') TO ('2024') This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any
attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
|
