>>> Gregory Stark <[EMAIL PROTECTED]> wrote:
> And log the explain plans to a
> file so we can look for at what statistics targets the plan changed?
Well, I can give you explain analyze output for
default_statistics_target 10 and 50, for whatever that's worth.
Unfortunately I blew my save from the first run with target 50, but it
ran much faster than the first run of target 10 (attached), I think it
was about 2.8 seconds. That may or may not be due to the right pages
being cached by coincidence. The machine with the target 50 run was
serving the web app at the time, so there was significant other load,
while the other was idle except as a replicaton target at the time of
the run.
-Kevin
Unique (cost=457.72..457.81 rows=2 width=171) (actual
time=94927.370..94932.613 rows=2388 loops=1)
-> Sort (cost=457.72..457.73 rows=2 width=171) (actual
time=94927.366..94928.548 rows=2396 loops=1)
Sort Key: "caseNo", "filingDate", "countyName", "statusCodeDescr",
"nameF", "nameM", "nameL", suffix, dob, caption, "countyNo", "caseType",
"isSeal", "isPartySeal", "lastModified", "searchName", "isPetitionerSeal"
-> Append (cost=0.00..457.71 rows=2 width=171) (actual
time=108.847..94913.855 rows=2396 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..227.86 rows=1
width=169) (actual time=108.846..93393.253 rows=2315 loops=1)
-> Nested Loop (cost=0.00..227.85 rows=1 width=169)
(actual time=108.843..93383.339 rows=2315 loops=1)
-> Nested Loop (cost=0.00..122.30 rows=1
width=163) (actual time=97.389..93174.181 rows=2315 loops=1)
-> Nested Loop Left Join (cost=0.00..120.42
rows=1 width=153) (actual time=86.182..93144.680 rows=2315 loops=1)
-> Nested Loop Left Join
(cost=0.00..116.82 rows=1 width=153) (actual time=75.841..73181.110 rows=2315
loops=1)
-> Nested Loop Left Join
(cost=0.00..113.88 rows=1 width=145) (actual time=60.527..52107.799 rows=2315
loops=1)
Filter:
((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY
(('{PA,JD}'::character varying[])::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..113.56 rows=1 width=146) (actual time=53.104..52045.586 rows=2601
loops=1)
Join Filter:
((((("P"."partyType")::text = ANY (('{JV,CH}'::character varying[])::text[]))
AND (("C"."caseType")::text = 'ZZ'::text)) OR (("P"."partyType")::text <> ALL
(('{JV,CH}'::character varying[])::text[]))) AND ((("C"."caseType")::text <>
ALL (('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
(("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
(("P"."partyType")::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
((("C"."caseType")::text <> ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND (("P"."partyType")::text <> ALL (('{CH,JV}'::character
varying[])::text[])))) AND ((("P"."partyType")::text <> ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR (((("C"."filingDate")::date <
'2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND
((("C"."caseType")::text <> ALL (('{CV,FA}'::character varying[])::text[])) OR
(("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan))))))
-> Index Scan using
"Party_SearchName" on "Party" "P" (cost=0.00..3.21 rows=1 width=81) (actual
time=6.878..159.418 rows=4097 loops=1)
Index Cond:
((("searchName")::text >= 'HILL,J'::character varying) AND
(("searchName")::text < 'HILL,K'::character varying))
Filter: ((NOT
"isSeal") AND (("searchName")::text ~~ 'HILL,J%'::text))
-> Index Scan using
"Case_pkey" on "Case" "C" (cost=0.00..5.01 rows=1 width=87) (actual
time=12.607..12.625 rows=1 loops=4097)
Index Cond:
((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("P"."caseNo")::text))
Filter:
(("isExpunge" <> true) AND (NOT (subplan)))
SubPlan
-> Index Scan
using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..2.14 rows=1 width=0)
(actual time=4.487..4.487 rows=0 loops=4097)
Index
Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
SubPlan
-> Index Scan using
"CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..105.25 rows=1 width=0)
(actual time=7.667..7.667 rows=0 loops=16)
Index Cond:
((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter:
(("eventType")::text = ANY (('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.29
rows=1 width=32) (actual time=0.017..0.019 rows=1 loops=2601)
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..2.92 rows=1 width=26)
(actual time=9.035..9.098 rows=1 loops=2315)
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..3.59 rows=1 width=18) (actual time=8.618..8.619
rows=1 loops=2315)
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..2.58 rows=1 width=0) (actual
time=0.034..0.034 rows=0 loops=2106)
Index Cond:
((("countyNo")::smallint = ($11)::smallint) AND (("caseNo")::text =
($10)::text) AND (("dispoDate")::date > ($12)::date))
-> Index Scan using "County_pkey" on "County"
"CY" (cost=0.00..1.87 rows=1 width=12) (actual time=0.009..0.010 rows=1
loops=2315)
Index Cond: (("C"."countyNo")::smallint
= ("CY"."countyNo")::smallint)
-> Index Scan using "StatusCode_pkey" on
"StatusCode" "S" (cost=0.00..0.27 rows=1 width=18) (actual time=0.008..0.010
rows=1 loops=2315)
Index Cond: (("C"."statusCode")::text =
("S"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CH" (cost=0.00..105.25 rows=1 width=0) (actual time=4.836..4.836 rows=0
loops=34)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Subquery Scan "*SELECT* 2" (cost=0.00..229.85 rows=1
width=171) (actual time=7.588..1517.825 rows=81 loops=1)
-> Nested Loop (cost=0.00..229.84 rows=1 width=171)
(actual time=7.575..1517.426 rows=81 loops=1)
-> Nested Loop (cost=0.00..122.70 rows=1
width=163) (actual time=7.547..1516.429 rows=81 loops=1)
Join Filter: ((((("P"."partyType")::text = ANY
(('{JV,CH}'::character varying[])::text[])) AND (("C"."caseType")::text =
'ZZ'::text)) OR (("P"."partyType")::text <> ALL (('{JV,CH}'::character
varying[])::text[]))) AND ((("C"."caseType")::text <> ALL
(('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
(("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
(("P"."partyType")::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
((("C"."caseType")::text <> ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND (("P"."partyType")::text <> ALL (('{CH,JV}'::character
varying[])::text[])))) AND ((("P"."partyType")::text <> ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR (((("C"."filingDate")::date <
'2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND
((("C"."caseType")::text <> ALL (('{CV,FA}'::character varying[])::text[])) OR
(("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan))))))
-> Nested Loop (cost=0.00..14.15 rows=1
width=179) (actual time=7.390..1508.204 rows=82 loops=1)
-> Nested Loop Left Join
(cost=0.00..13.87 rows=1 width=173) (actual time=7.372..1506.136 rows=82
loops=1)
-> Nested Loop Left Join
(cost=0.00..10.27 rows=1 width=173) (actual time=7.351..1101.569 rows=82
loops=1)
-> Nested Loop Left Join
(cost=0.00..7.33 rows=1 width=165) (actual time=7.325..653.664 rows=82 loops=1)
Filter:
((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY
(('{PA,JD}'::character varying[])::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..7.01 rows=1 width=166) (actual time=7.299..651.909 rows=99 loops=1)
-> Index Scan
using "Alias_SearchName" on "Alias" "AL" (cost=0.00..1.99 rows=1 width=79)
(actual time=7.251..15.048 rows=113 loops=1)
Index
Cond: ((("searchName")::text >= 'HILL,J'::character varying) AND
(("searchName")::text < 'HILL,K'::character varying))
Filter:
(("searchName")::text ~~ 'HILL,J%'::text)
-> Index Scan
using "Case_pkey" on "Case" "C" (cost=0.00..5.01 rows=1 width=87) (actual
time=5.630..5.631 rows=1 loops=113)
Index
Cond: ((("C"."countyNo")::smallint = ("AL"."countyNo")::smallint) AND
(("AL"."caseNo")::text = ("C"."caseNo")::text))
Filter:
(("isExpunge" <> true) AND (NOT (subplan)))
SubPlan
->
Index Scan using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..2.14
rows=1 width=0) (actual time=1.565..1.565 rows=0 loops=113)
Index Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.29
rows=1 width=32) (actual time=0.012..0.013 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..2.92 rows=1 width=26)
(actual time=5.458..5.459 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
"CaseDispo_pkey" on "CaseDispo" "CD" (cost=0.00..3.59 rows=1 width=18) (actual
time=4.929..4.930 rows=1 loops=82)
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..2.58 rows=1 width=0) (actual
time=0.025..0.025 rows=0 loops=77)
Index Cond:
((("countyNo")::smallint = ($4)::smallint) AND (("caseNo")::text = ($3)::text)
AND (("dispoDate")::date > ($5)::date))
-> Index Scan using "StatusCode_pkey"
on "StatusCode" "S" (cost=0.00..0.27 rows=1 width=18) (actual
time=0.021..0.022 rows=1 loops=82)
Index Cond:
(("C"."statusCode")::text = ("S"."statusCode")::text)
-> Index Scan using "Party_pkey" on "Party"
"P" (cost=0.00..3.20 rows=1 width=26) (actual time=0.085..0.086 rows=1
loops=82)
Index Cond: ((("C"."countyNo")::smallint
= ("P"."countyNo")::smallint) AND (("C"."caseNo")::text = ("P"."caseNo")::text)
AND (("P"."partyNo")::smallint = ("AL"."partyNo")::smallint))
Filter: (NOT "isSeal")
SubPlan
-> Index Scan using "CaseHist_pkey" on
"CaseHist" "CHPET" (cost=0.00..105.25 rows=1 width=0) (never executed)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Index Scan using "County_pkey" on "County" "CY"
(cost=0.00..1.87 rows=1 width=12) (actual time=0.004..0.006 rows=1 loops=81)
Index Cond: (("C"."countyNo")::smallint =
("CY"."countyNo")::smallint)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CH" (cost=0.00..105.25 rows=1 width=0) (never executed)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
Total runtime: 94936.297 ms
(89 rows)
Unique (cost=473.49..473.58 rows=2 width=173) (actual time=1042.203..1047.587
rows=2388 loops=1)
-> Sort (cost=473.49..473.49 rows=2 width=173) (actual
time=1042.200..1043.316 rows=2396 loops=1)
Sort Key: "caseNo", "filingDate", "countyName", "statusCodeDescr",
"nameF", "nameM", "nameL", suffix, dob, caption, "countyNo", "caseType",
"isSeal", "isPartySeal", "lastModified", "searchName", "isPetitionerSeal"
-> Append (cost=0.00..473.48 rows=2 width=173) (actual
time=1.017..1021.141 rows=2396 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..235.74 rows=1
width=169) (actual time=1.017..991.591 rows=2315 loops=1)
-> Nested Loop (cost=0.00..235.73 rows=1 width=169)
(actual time=1.013..984.969 rows=2315 loops=1)
-> Nested Loop (cost=0.00..126.19 rows=1
width=163) (actual time=0.944..958.580 rows=2315 loops=1)
-> Nested Loop Left Join (cost=0.00..124.31
rows=1 width=153) (actual time=0.881..942.923 rows=2315 loops=1)
-> Nested Loop Left Join
(cost=0.00..120.71 rows=1 width=153) (actual time=0.723..775.487 rows=2315
loops=1)
-> Nested Loop Left Join
(cost=0.00..117.82 rows=1 width=145) (actual time=0.592..601.307 rows=2315
loops=1)
Filter:
((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY
(('{PA,JD}'::character varying[])::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..117.51 rows=1 width=146) (actual time=0.489..556.384 rows=2601
loops=1)
Join Filter:
((((("P"."partyType")::text = ANY (('{JV,CH}'::character varying[])::text[]))
AND (("C"."caseType")::text = 'ZZ'::text)) OR (("P"."partyType")::text <> ALL
(('{JV,CH}'::character varying[])::text[]))) AND ((("C"."caseType")::text <>
ALL (('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
(("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
(("P"."partyType")::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
((("C"."caseType")::text <> ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND (("P"."partyType")::text <> ALL (('{CH,JV}'::character
varying[])::text[])))) AND ((("P"."partyType")::text <> ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR (((("C"."filingDate")::date <
'2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND
((("C"."caseType")::text <> ALL (('{CV,FA}'::character varying[])::text[])) OR
(("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan))))))
-> Index Scan using
"Party_SearchName" on "Party" "P" (cost=0.00..3.21 rows=1 width=81) (actual
time=0.178..118.021 rows=4097 loops=1)
Index Cond:
((("searchName")::text >= 'HILL,J'::character varying) AND
(("searchName")::text < 'HILL,K'::character varying))
Filter: ((NOT
"isSeal") AND (("searchName")::text ~~ 'HILL,J%'::text))
-> Index Scan using
"Case_pkey" on "Case" "C" (cost=0.00..4.96 rows=1 width=87) (actual
time=0.099..0.100 rows=1 loops=4097)
Index Cond:
((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("P"."caseNo")::text))
Filter:
(("isExpunge" <> true) AND (NOT (subplan)))
SubPlan
-> Index Scan
using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..2.14 rows=1 width=0)
(actual time=0.032..0.032 rows=0 loops=4097)
Index
Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
SubPlan
-> Index Scan using
"CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..109.25 rows=1 width=0)
(actual time=0.183..0.183 rows=0 loops=16)
Index Cond:
((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter:
(("eventType")::text = ANY (('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.29
rows=1 width=32) (actual time=0.012..0.013 rows=1 loops=2601)
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..2.87 rows=1 width=26)
(actual time=0.069..0.070 rows=1 loops=2315)
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..3.59 rows=1 width=18) (actual time=0.068..0.069
rows=1 loops=2315)
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..2.58 rows=1 width=0) (actual
time=0.009..0.009 rows=0 loops=2106)
Index Cond:
((("countyNo")::smallint = ($11)::smallint) AND (("caseNo")::text =
($10)::text) AND (("dispoDate")::date > ($12)::date))
-> Index Scan using "County_pkey" on "County"
"CY" (cost=0.00..1.87 rows=1 width=12) (actual time=0.003..0.004 rows=1
loops=2315)
Index Cond: (("C"."countyNo")::smallint
= ("CY"."countyNo")::smallint)
-> Index Scan using "StatusCode_pkey" on
"StatusCode" "S" (cost=0.00..0.27 rows=1 width=18) (actual time=0.003..0.005
rows=1 loops=2315)
Index Cond: (("C"."statusCode")::text =
("S"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CH" (cost=0.00..109.25 rows=1 width=0) (actual time=0.078..0.078 rows=0
loops=34)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Subquery Scan "*SELECT* 2" (cost=0.00..237.73 rows=1
width=173) (actual time=0.479..27.192 rows=81 loops=1)
-> Nested Loop (cost=0.00..237.72 rows=1 width=173)
(actual time=0.465..27.006 rows=81 loops=1)
-> Nested Loop (cost=0.00..128.18 rows=1
width=167) (actual time=0.427..26.299 rows=81 loops=1)
-> Nested Loop Left Join (cost=0.00..126.30
rows=1 width=159) (actual time=0.417..25.777 rows=81 loops=1)
-> Nested Loop Left Join
(cost=0.00..122.70 rows=1 width=159) (actual time=0.394..20.727 rows=81 loops=1)
-> Nested Loop
(cost=0.00..119.81 rows=1 width=151) (actual time=0.371..16.876 rows=81 loops=1)
Join Filter:
((((("P"."partyType")::text = ANY (('{JV,CH}'::character varying[])::text[]))
AND (("C"."caseType")::text = 'ZZ'::text)) OR (("P"."partyType")::text <> ALL
(('{JV,CH}'::character varying[])::text[]))) AND ((("C"."caseType")::text <>
ALL (('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
(("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
(("P"."partyType")::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
((("C"."caseType")::text <> ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND (("P"."partyType")::text <> ALL (('{CH,JV}'::character
varying[])::text[])))) AND ((("P"."partyType")::text <> ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR (((("C"."filingDate")::date <
'2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND
((("C"."caseType")::text <> ALL (('{CV,FA}'::character varying[])::text[])) OR
(("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan))))))
-> Nested Loop Left Join
(cost=0.00..7.28 rows=1 width=167) (actual time=0.211..11.077 rows=82 loops=1)
Filter:
((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY
(('{PA,JD}'::character varying[])::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..6.96 rows=1 width=168) (actual time=0.185..9.780 rows=99 loops=1)
-> Index Scan
using "Alias_SearchName" on "Alias" "AL" (cost=0.00..1.99 rows=1 width=81)
(actual time=0.135..2.619 rows=113 loops=1)
Index
Cond: ((("searchName")::text >= 'HILL,J'::character varying) AND
(("searchName")::text < 'HILL,K'::character varying))
Filter:
(("searchName")::text ~~ 'HILL,J%'::text)
-> Index Scan
using "Case_pkey" on "Case" "C" (cost=0.00..4.96 rows=1 width=87) (actual
time=0.059..0.060 rows=1 loops=113)
Index
Cond: ((("C"."countyNo")::smallint = ("AL"."countyNo")::smallint) AND
(("AL"."caseNo")::text = ("C"."caseNo")::text))
Filter:
(("isExpunge" <> true) AND (NOT (subplan)))
SubPlan
->
Index Scan using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..2.14
rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=113)
Index Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.29
rows=1 width=32) (actual time=0.008..0.009 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
"Party_pkey" on "Party" "P" (cost=0.00..3.19 rows=1 width=26) (actual
time=0.061..0.062 rows=1 loops=82)
Index Cond:
((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("P"."caseNo")::text) AND (("P"."partyNo")::smallint =
("AL"."partyNo")::smallint))
Filter: (NOT "isSeal")
SubPlan
-> Index Scan using
"CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..109.25 rows=1 width=0)
(never executed)
Index Cond:
((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter:
(("eventType")::text = ANY (('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Index Scan using
"LastCaseMod_pkey" on "LastCaseMod" "LCM" (cost=0.00..2.87 rows=1 width=26)
(actual time=0.044..0.044 rows=1 loops=81)
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..3.59 rows=1 width=18) (actual time=0.059..0.060
rows=1 loops=81)
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..2.58 rows=1 width=0) (actual
time=0.007..0.007 rows=0 loops=76)
Index Cond:
((("countyNo")::smallint = ($4)::smallint) AND (("caseNo")::text = ($3)::text)
AND (("dispoDate")::date > ($5)::date))
-> Index Scan using "County_pkey" on "County"
"CY" (cost=0.00..1.87 rows=1 width=12) (actual time=0.003..0.004 rows=1
loops=81)
Index Cond: (("C"."countyNo")::smallint
= ("CY"."countyNo")::smallint)
-> Index Scan using "StatusCode_pkey" on
"StatusCode" "S" (cost=0.00..0.27 rows=1 width=18) (actual time=0.002..0.004
rows=1 loops=81)
Index Cond: (("C"."statusCode")::text =
("S"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CH" (cost=0.00..109.25 rows=1 width=0) (never executed)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
Total runtime: 1051.482 ms
(89 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
