>>> "Kevin Grittner" <kevin.gritt...@wicourts.gov> wrote:
> Attached is EXPLAIN
> ANALYZE output from the 8.3.5 database I dumped from for my 8.4devel
> tests.
Actually, that one is from the sibling machine which is in production.
Attached is the one on standby where I've been running the rest of
this. Apparently, differences in the random samples lead to different
plans, so maybe having both will be of some use.
Apologies, but I'm having to work this in among other work.
-Kevin
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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
