QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=65.25..65.34 rows=2 width=140) (actual time=6032.575..6037.655 rows=2376 loops=1) -> Sort (cost=65.25..65.26 rows=2 width=140) (actual time=6032.573..6033.709 rows=2384 loops=1) Sort Key: "C"."caseNo", "C"."filingDate", "CY"."countyName", "S".descr, "P"."nameF", "P"."nameM", "P"."nameL", "P".suffix, "P".dob, "C".caption, "CY"."countyNo", "C"."caseType", "C"."isSeal", "C"."isPartySeal", "LCM"."lastModified", "P"."searchName", (CASE WHEN (((("C"."filingDate")::date >= '2008-11-01'::date) AND (("C"."wcisClsCode")::text = '30709'::text)) OR ((("C"."caseType")::text = ANY ('{CV,FA}'::text[])) AND (("C"."wcisClsCode")::text = '30711'::text) AND (subplan))) THEN true ELSE false END) Sort Method: quicksort Memory: 691kB -> Append (cost=0.00..65.24 rows=2 width=140) (actual time=0.716..6025.731 rows=2384 loops=1) -> Nested Loop (cost=0.00..32.35 rows=1 width=136) (actual time=0.714..3306.325 rows=2303 loops=1) -> Nested Loop (cost=0.00..17.98 rows=1 width=130) (actual time=0.672..3292.866 rows=2303 loops=1) -> Nested Loop Left Join (cost=0.00..17.50 rows=1 width=123) (actual time=0.627..3282.441 rows=2303 loops=1) -> Nested Loop Left Join (cost=0.00..16.57 rows=1 width=123) (actual time=0.504..2375.071 rows=2303 loops=1) -> Nested Loop Left Join (cost=0.00..16.12 rows=1 width=115) (actual time=0.408..1966.859 rows=2303 loops=1) Filter: ((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY ('{PA,JD}'::text[])) AND (NOT "C"."isConfidential"))) AND ((("WPCT"."profileName")::text <> 'PUBLIC'::text) OR (("C"."caseType")::text <> 'FA'::text) OR (("C"."wcisClsCode")::text <> '40501'::text))) -> Nested Loop (cost=0.00..15.82 rows=1 width=116) (actual time=0.351..1944.528 rows=2594 loops=1) Join Filter: ((((("P"."partyType")::text = ANY ('{JV,CH}'::text[])) AND (("C"."caseType")::text = 'ZZ'::text)) OR (("P"."partyType")::text <> ALL ('{JV,CH}'::text[]))) AND ((("C"."caseType")::text <> ALL ('{CF,CI,CM,CT,FO,TR}'::text[])) OR (("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY ('{JA,JC,JG,JM,JO,JV,JI,TP}'::text[])) AND (("P"."partyType")::text = ANY ('{CH,JV}'::text[]))) OR ((("C"."caseType")::text <> ALL ('{JA,JC,JG,JM,JO,JV,JI,TP}'::text[])) AND (("P"."partyType")::text <> ALL ('{CH,JV}'::text[])))) AND ((("P"."partyType")::text <> ALL ('{PE,PL,JP}'::text[])) OR (((("C"."filingDate")::date < '2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND ((("C"."caseType")::text <> ALL ('{CV,FA}'::text[])) OR (("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan)))))) -> Index Scan using "Party_SearchName" on "Party" "P" (cost=0.00..0.62 rows=1 width=60) (actual time=0.153..434.288 rows=4113 loops=1) Index Cond: ((("searchName")::text >= 'HILL,J'::text) AND (("searchName")::text < 'HILL,K'::text)) Filter: ((NOT "isSeal") AND (("searchName")::text ~~ 'HILL,J%'::text)) -> Index Scan using "Case_pkey" on "Case" "C" (cost=0.00..1.05 rows=1 width=72) (actual time=0.363..0.364 rows=1 loops=4113) Index Cond: ((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND (("C"."caseNo")::text = ("P"."caseNo")::text)) Filter: (("C"."isExpunge" <> true) AND (NOT (subplan))) SubPlan -> Index Scan using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..0.50 rows=1 width=0) (actual time=0.068..0.068 rows=0 loops=4113) Index Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text = ($1)::text)) SubPlan -> Index Scan using "CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..14.08 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=16) Index Cond: ((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text)) Filter: (("eventType")::text = ANY ('{FWBCA,CCTRO}'::text[])) -> Index Scan using "WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.28 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=2594) Index Cond: ((("WPCT"."profileName")::text = 'PUBLIC'::text) AND (("C"."caseType")::text = ("WPCT"."caseType")::text) AND (("C"."countyNo")::smallint = ("WPCT"."countyNo")::smallint)) -> Index Scan using "LastCaseMod_pkey" on "LastCaseMod" "LCM" (cost=0.00..0.43 rows=1 width=23) (actual time=0.175..0.175 rows=1 loops=2303) Index Cond: ((("LCM"."countyNo")::smallint = ("C"."countyNo")::smallint) AND (("LCM"."caseNo")::text = ("C"."caseNo")::text) AND (("LCM"."updateTypeId")::integer = 0)) -> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD" (cost=0.00..0.92 rows=1 width=15) (actual time=0.391..0.392 rows=1 loops=2303) Index Cond: ((("CD"."countyNo")::smallint = ("C"."countyNo")::smallint) AND (("CD"."caseNo")::text = ("C"."caseNo")::text)) Filter: (NOT (subplan)) SubPlan -> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD2" (cost=0.00..0.56 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=2105) Index Cond: ((("countyNo")::smallint = ($6)::smallint) AND (("caseNo")::text = ($5)::text) AND (("dispoDate")::date > ($7)::date)) -> Index Scan using "County_pkey" on "County" "CY" (cost=0.00..0.47 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=2303) Index Cond: (("CY"."countyNo")::smallint = ("C"."countyNo")::smallint) -> Index Scan using "StatusCode_pkey" on "StatusCode" "S" (cost=0.00..0.27 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=2303) Index Cond: (("S"."statusCode")::text = ("C"."statusCode")::text) SubPlan -> Index Scan using "CaseHist_pkey" on "CaseHist" "CH" (cost=0.00..14.08 rows=1 width=0) (actual time=0.031..0.031 rows=0 loops=34) Index Cond: ((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text)) Filter: (("eventType")::text = ANY ('{FWBCA,CCTRO}'::text[])) -> Nested Loop (cost=0.00..32.87 rows=1 width=140) (actual time=17.623..2717.182 rows=81 loops=1) Join Filter: ((((("P"."partyType")::text = ANY ('{JV,CH}'::text[])) AND (("C"."caseType")::text = 'ZZ'::text)) OR (("P"."partyType")::text <> ALL ('{JV,CH}'::text[]))) AND ((("C"."caseType")::text <> ALL ('{CF,CI,CM,CT,FO,TR}'::text[])) OR (("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY ('{JA,JC,JG,JM,JO,JV,JI,TP}'::text[])) AND (("P"."partyType")::text = ANY ('{CH,JV}'::text[]))) OR ((("C"."caseType")::text <> ALL ('{JA,JC,JG,JM,JO,JV,JI,TP}'::text[])) AND (("P"."partyType")::text <> ALL ('{CH,JV}'::text[])))) AND ((("P"."partyType")::text <> ALL ('{PE,PL,JP}'::text[])) OR (((("C"."filingDate")::date < '2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND ((("C"."caseType")::text <> ALL ('{CV,FA}'::text[])) OR (("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan)))))) -> Nested Loop (cost=0.00..3.98 rows=1 width=157) (actual time=17.481..2189.407 rows=82 loops=1) -> Nested Loop (cost=0.00..3.70 rows=1 width=151) (actual time=17.472..2188.765 rows=82 loops=1) -> Nested Loop Left Join (cost=0.00..3.22 rows=1 width=142) (actual time=17.462..2186.916 rows=82 loops=1) -> Nested Loop Left Join (cost=0.00..2.78 rows=1 width=134) (actual time=17.430..1895.873 rows=82 loops=1) Filter: ((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY ('{PA,JD}'::text[])) AND (NOT "C"."isConfidential"))) AND ((("WPCT"."profileName")::text <> 'PUBLIC'::text) OR (("C"."caseType")::text <> 'FA'::text) OR (("C"."wcisClsCode")::text <> '40501'::text))) -> Nested Loop Left Join (cost=0.00..2.48 rows=1 width=135) (actual time=17.397..1893.971 rows=99 loops=1) -> Nested Loop (cost=0.00..1.54 rows=1 width=135) (actual time=17.369..1194.783 rows=99 loops=1) -> Index Scan using "Alias_SearchName" on "Alias" "AL" (cost=0.00..0.48 rows=1 width=63) (actual time=17.307..541.485 rows=113 loops=1) Index Cond: ((("searchName")::text >= 'HILL,J'::text) AND (("searchName")::text < 'HILL,K'::text)) Filter: (("searchName")::text ~~ 'HILL,J%'::text) -> Index Scan using "Case_pkey" on "Case" "C" (cost=0.00..1.05 rows=1 width=72) (actual time=5.776..5.777 rows=1 loops=113) Index Cond: ((("C"."countyNo")::smallint = ("AL"."countyNo")::smallint) AND (("C"."caseNo")::text = ("AL"."caseNo")::text)) Filter: (("C"."isExpunge" <> true) AND (NOT (subplan))) SubPlan -> Index Scan using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..0.50 rows=1 width=0) (actual time=0.725..0.725 rows=0 loops=113) Index Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text = ($1)::text)) -> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD" (cost=0.00..0.92 rows=1 width=15) (actual time=7.058..7.059 rows=1 loops=99) Index Cond: ((("CD"."countyNo")::smallint = ("C"."countyNo")::smallint) AND (("CD"."caseNo")::text = ("C"."caseNo")::text)) Filter: (NOT (subplan)) SubPlan -> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD2" (cost=0.00..0.56 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=95) Index Cond: ((("countyNo")::smallint = ($3)::smallint) AND (("caseNo")::text = ($2)::text) AND (("dispoDate")::date > ($4)::date)) -> Index Scan using "WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.28 rows=1 width=26) (actual time=0.013..0.014 rows=1 loops=99) Index Cond: ((("WPCT"."profileName")::text = 'PUBLIC'::text) AND (("C"."caseType")::text = ("WPCT"."caseType")::text) AND (("C"."countyNo")::smallint = ("WPCT"."countyNo")::smallint)) -> Index Scan using "LastCaseMod_pkey" on "LastCaseMod" "LCM" (cost=0.00..0.43 rows=1 width=23) (actual time=3.545..3.546 rows=1 loops=82) Index Cond: ((("LCM"."countyNo")::smallint = ("C"."countyNo")::smallint) AND (("LCM"."caseNo")::text = ("C"."caseNo")::text) AND (("LCM"."updateTypeId")::integer = 0)) -> Index Scan using "County_pkey" on "County" "CY" (cost=0.00..0.47 rows=1 width=9) (actual time=0.005..0.006 rows=1 loops=82) Index Cond: (("CY"."countyNo")::smallint = ("C"."countyNo")::smallint) -> Index Scan using "StatusCode_pkey" on "StatusCode" "S" (cost=0.00..0.27 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=82) Index Cond: (("S"."statusCode")::text = ("C"."statusCode")::text) -> Index Scan using "Party_pkey" on "Party" "P" (cost=0.00..0.64 rows=1 width=20) (actual time=6.420..6.422 rows=1 loops=82) Index Cond: ((("P"."countyNo")::smallint = ("C"."countyNo")::smallint) AND (("P"."caseNo")::text = ("C"."caseNo")::text) AND (("P"."partyNo")::smallint = ("AL"."partyNo")::smallint)) Filter: (NOT "P"."isSeal") SubPlan -> Index Scan using "CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..14.08 rows=1 width=0) (never executed) Index Cond: ((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text)) Filter: (("eventType")::text = ANY ('{FWBCA,CCTRO}'::text[])) -> Index Scan using "CaseHist_pkey" on "CaseHist" "CH" (cost=0.00..14.08 rows=1 width=0) (never executed) Index Cond: ((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text)) Filter: (("eventType")::text = ANY ('{FWBCA,CCTRO}'::text[])) Total runtime: 6040.094 ms (87 rows)