QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=180410.55..180495.64 rows=5673 width=13) -> Hash Left Join (cost=52015.63..180382.18 rows=5673 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=27365.34..63496.22 rows=225 width=49) -> Hash Anti Join (cost=27365.34..63433.02 rows=225 width=81) Hash Cond: (("*SELECT* 1"."matterNo")::text = ("*SELECT* 1"."matterNo")::text) Join Filter: ((("*SELECT* 1".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (ROW(("*SELECT* 1".date)::date, "*SELECT* 1"."matterHistRowOrder") > ROW(("*SELECT* 1".date)::date, "*SELECT* 1"."matterHistRowOrder"))) -> Nested Loop (cost=530.51..34570.69 rows=253 width=87) Join Filter: ((("*SELECT* 1".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan))) -> Result (cost=0.00..0.02 rows=1 width=0) -> Hash Join (cost=530.51..26813.09 rows=1518 width=83) Hash Cond: (("*SELECT* 1"."matterNo")::text = (s."matterNo")::text) -> Append (cost=6.64..26033.70 rows=64091 width=70) -> Subquery Scan "*SELECT* 1" (cost=6.64..25383.06 rows=36955 width=70) -> Hash Join (cost=6.64..25013.51 rows=36955 width=135) Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text) -> Nested Loop (cost=0.57..23874.00 rows=105159 width=135) -> Seq Scan on "Matter" "M" (cost=0.00..379.28 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.63 rows=27136 width=70) -> Seq Scan on "Matter" "M" (cost=0.00..379.28 rows=27136 width=112) Filter: (("matterType")::text <> 'LT'::text) -> Hash (cost=515.82..515.82 rows=644 width=13) -> Nested Loop (cost=6.24..515.82 rows=644 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.69..49.14 rows=644 width=13) Recheck Cond: (("matterStatusCode")::text = ANY ('{OP,RO}'::text[])) -> Bitmap Index Scan on "Matter_MatterStatusCode" (cost=0.00..5.53 rows=644 width=0) Index Cond: (("matterStatusCode")::text = ANY ('{OP,RO}'::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=1.52..5.10 rows=1 width=0) -> Nested Loop Anti Join (cost=1.52..4.82 rows=1 width=4) 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((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)) -> Nested Loop (cost=0.76..2.25 rows=1 width=36) -> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26) Index Cond: (("matterNo")::text = ($4)::text) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=23) 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) -> Nested Loop (cost=0.76..2.53 rows=1 width=32) -> Nested Loop (cost=0.76..2.25 rows=1 width=36) -> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26) Index Cond: (("matterNo")::text = ($4)::text) Filter: (("matterType")::text <> 'LT'::text) -> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=23) 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=4) 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=4) Index Cond: (("MEC1"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text) Filter: (("MEC1"."newMaintCode")::text = 'INA'::text) -> Hash (cost=26033.70..26033.70 rows=64091 width=38) -> Append (cost=6.64..26033.70 rows=64091 width=38) -> Subquery Scan "*SELECT* 1" (cost=6.64..25383.06 rows=36955 width=38) -> Hash Join (cost=6.64..25013.51 rows=36955 width=135) Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text) -> Nested Loop (cost=0.57..23874.00 rows=105159 width=135) -> Seq Scan on "Matter" "M" (cost=0.00..379.28 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.63 rows=27136 width=38) -> Seq Scan on "Matter" "M" (cost=0.00..379.28 rows=27136 width=112) Filter: (("matterType")::text <> 'LT'::text) -> 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.86..24269.86 rows=30434 width=70) -> Nested Loop (cost=7.26..23965.52 rows=30434 width=35) -> Hash Join (cost=6.74..2199.88 rows=30395 width=22) Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text) -> Seq Scan on "MatterHist" "MH" (cost=0.00..1495.35 rows=105025 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.65 rows=1 width=0) -> Nested Loop (cost=0.76..2.37 rows=1 width=4) 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=26) 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=4) Index Cond: (("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text) Filter: (("MEC"."newStatusCode" IS NOT NULL) AND (("MEC"."newStatusCode")::text <> 'CT'::text)) (140 rows)