QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=142884.33..142932.05 rows=3181 width=13) -> Hash Left Join (cost=25179.44..142868.43 rows=3181 width=13) Hash Cond: (("*SELECT* 1"."matterNo")::text = ("M"."matterNo")::text) Join Filter: ((("MH".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan))) Filter: ((COALESCE(("MEC"."newStatusCode")::character varying, 'OP'::character varying))::text <> 'CL'::text) -> Nested Loop (cost=529.05..66375.07 rows=126 width=49) -> Nested Loop (cost=529.05..66339.68 rows=126 width=81) Join Filter: ((("*SELECT* 1".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan)) AND (NOT (subplan))) -> Result (cost=0.00..0.02 rows=1 width=0) -> Hash Join (cost=529.05..26811.51 rows=1513 width=83) Hash Cond: (("*SELECT* 1"."matterNo")::text = (s."matterNo")::text) -> Append (cost=6.64..26033.63 rows=64090 width=70) -> Subquery Scan "*SELECT* 1" (cost=6.64..25383.01 rows=36954 width=70) -> Hash Join (cost=6.64..25013.47 rows=36954 width=135) Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text) -> Nested Loop (cost=0.57..23873.98 rows=105156 width=135) -> Seq Scan on "Matter" "M" (cost=0.00..379.26 rows=27136 width=112) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23) Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)) -> BitmapOr (cost=0.57..0.57 rows=8 width=0) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text) -> Hash (cost=4.37..4.37 rows=136 width=8) -> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8) Filter: ("newStageCode" IS NOT NULL) -> Subquery Scan "*SELECT* 2" (cost=0.00..650.62 rows=27136 width=70) -> Seq Scan on "Matter" "M" (cost=0.00..379.26 rows=27136 width=112) Filter: (("matterType")::text <> 'LT'::text) -> Hash (cost=514.39..514.39 rows=642 width=13) -> Nested Loop (cost=6.23..514.39 rows=642 width=13) Join Filter: (((d."matterNo")::text = (s."litigationMatterNo")::text) OR ((s."litigationMatterNo" IS NULL) AND ((d."matterNo")::text = (s."matterNo")::text))) -> Bitmap Heap Scan on "Matter" d (cost=5.68..49.10 rows=642 width=13) Recheck Cond: (("matterStatusCode")::text = ANY (('{OP,RO}'::character varying[])::text[])) -> Bitmap Index Scan on "Matter_MatterStatusCode" (cost=0.00..5.52 rows=642 width=0) Index Cond: (("matterStatusCode")::text = ANY (('{OP,RO}'::character varying[])::text[])) -> Bitmap Heap Scan on "Matter" s (cost=0.55..0.68 rows=3 width=26) Recheck Cond: (((d."matterNo")::text = (s."litigationMatterNo")::text) OR ((d."matterNo")::text = (s."matterNo")::text)) -> BitmapOr (cost=0.55..0.55 rows=3 width=0) -> Bitmap Index Scan on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0) Index Cond: ((d."matterNo")::text = (s."litigationMatterNo")::text) -> Bitmap Index Scan on "Matter_pkey" (cost=0.00..0.27 rows=1 width=0) Index Cond: ((d."matterNo")::text = (s."matterNo")::text) SubPlan -> Nested Loop (cost=0.76..24.15 rows=1 width=722) -> Nested Loop (cost=0.76..23.86 rows=1 width=563) Join Filter: (NOT (subplan)) -> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26) Index Cond: (("matterNo")::text = ($0)::text) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=550) Recheck Cond: (((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) OR ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text)) Filter: ((public."MatterHist".date)::date <= $1) -> BitmapOr (cost=0.76..0.76 rows=8 width=0) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text) SubPlan -> Nested Loop (cost=0.76..2.70 rows=1 width=722) -> Nested Loop (cost=0.76..2.41 rows=1 width=563) Join Filter: (ROW((public."MatterHist".date)::date, CASE WHEN ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE ((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END) > ROW(($31)::date, CASE WHEN (($32)::text = ($33)::text) THEN ($34)::integer ELSE (($34)::smallint + 10000) END)) -> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26) Index Cond: (("matterNo")::text = ($0)::text) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=550) Recheck Cond: (((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) OR ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text)) Filter: ((public."MatterHist".date)::date <= $1) -> BitmapOr (cost=0.76..0.76 rows=8 width=0) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text) -> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC2" (cost=0.00..0.27 rows=1 width=159) Index Cond: (("MEC2"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text) Filter: (("MEC2"."removeMaintCode")::text = 'INA'::text) -> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC1" (cost=0.00..0.27 rows=1 width=159) Index Cond: (("MEC1"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text) Filter: (("MEC1"."newMaintCode")::text = 'INA'::text) -> Result (cost=0.76..3.16 rows=2 width=359) -> Append (cost=0.76..3.16 rows=2 width=359) -> Nested Loop (cost=0.76..2.66 rows=1 width=135) -> Nested Loop (cost=0.76..2.37 rows=1 width=135) Join Filter: (ROW(("MH".date)::date, (CASE WHEN (("MH"."matterNo")::text = ("M"."matterNo")::text) THEN ("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint + 10000) END)::smallint) > ROW(($2)::date, $3)) -> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.47 rows=1 width=112) Index Cond: (("matterNo")::text = ($0)::text) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.76..1.66 rows=8 width=23) Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)) Filter: (("MH".date)::date <= $1) -> BitmapOr (cost=0.76..0.76 rows=8 width=0) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text) -> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=8) Index Cond: (("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text) Filter: ("MEC"."newStageCode" IS NOT NULL) -> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.48 rows=1 width=112) Index Cond: (("matterNo")::text = ($0)::text) Filter: ((("matterType")::text <> 'LT'::text) AND (("filedDate")::date <= $1) AND (ROW(("filedDate")::date, 0::smallint) > ROW(($2)::date, $3))) -> Index Scan using "Matter_pkey" on "Matter" "L" (cost=0.00..0.27 rows=1 width=13) Index Cond: (("L"."matterNo")::text = (COALESCE("*SELECT* 1"."litigationMatterNo", "*SELECT* 1"."matterNo"))::text) -> Hash (cost=24269.98..24269.98 rows=30433 width=70) -> Nested Loop (cost=7.26..23965.65 rows=30433 width=35) -> Hash Join (cost=6.74..2200.73 rows=30394 width=22) Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text) -> Seq Scan on "MatterHist" "MH" (cost=0.00..1496.22 rows=105022 width=23) -> Hash (cost=5.34..5.34 rows=112 width=7) -> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..5.34 rows=112 width=7) Filter: (("newStatusCode" IS NOT NULL) AND (("newStatusCode")::text <> 'CT'::text)) -> Bitmap Heap Scan on "Matter" "M" (cost=0.52..0.66 rows=3 width=26) Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)) Filter: (("M"."matterType")::text <> 'LT'::text) -> BitmapOr (cost=0.52..0.52 rows=3 width=0) -> Bitmap Index Scan on "Matter_pkey" (cost=0.00..0.26 rows=1 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text) -> Bitmap Index Scan on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text) SubPlan -> Nested Loop (cost=0.76..2.66 rows=1 width=35) -> Nested Loop (cost=0.76..2.37 rows=1 width=36) Join Filter: (ROW(("MH".date)::date, (CASE WHEN (("MH"."matterNo")::text = ("M"."matterNo")::text) THEN ("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint + 10000) END)::smallint) > ROW(($25)::date, $26)) -> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.47 rows=1 width=26) Index Cond: (("matterNo")::text = ($24)::text) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.76..1.66 rows=8 width=23) Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)) Filter: (("MH".date)::date <= $1) -> BitmapOr (cost=0.76..0.76 rows=8 width=0) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text) -> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0) Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text) -> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=7) Index Cond: (("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text) Filter: (("MEC"."newStatusCode" IS NOT NULL) AND (("MEC"."newStatusCode")::text <> 'CT'::text)) (139 rows)