Need help tuning a query - Mailing list pgsql-general
| From | Dirschel, Steve |
|---|---|
| Subject | Need help tuning a query |
| Date | |
| Msg-id | DM6PR03MB433218DEED175335E5710C54FAC2A@DM6PR03MB4332.namprd03.prod.outlook.com Whole thread Raw |
| In response to | [Beginner question]How to solve multiple definition of `yylval'? (Wen Yi <chuxuec@outlook.com>) |
| Responses |
YNT: Need help tuning a query
|
| List | pgsql-general |
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)
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')
pgsql-general by date: