>>> Robert Haas <[email protected]> wrote:
> I had a suspicion we were going to see something like this. You're
> using several NOT EXISTS clauses and 8.4devel is converting those
into
> Anti Joins. Aside from the longer planning time, the resulting plan
> appears to have a much higher estimated cost, so I'm suspicious of a
> bug in the new Semi/Anti join stuff, but I don't have time to track
it
> down right now (and I suspect Tom will figure it out faster than I
can
> anyway).
The 8.4 plan also don't seem to run as fast. Attached is EXPLAIN
ANALYZE output from the 8.3.5 database I dumped from for my 8.4devel
tests. I tried the same run on 8.4devel and it is still running after
20 minutes. I will let it cook for a while.
-Kevin
Unique (cost=72.00..72.09 rows=2 width=139) (actual time=3618.628..3623.643
rows=2376 loops=1)
-> Sort (cost=72.00..72.01 rows=2 width=139) (actual
time=3618.625..3619.761 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..71.99 rows=2 width=139) (actual
time=7.606..3610.407 rows=2384 loops=1)
-> Nested Loop (cost=0.00..35.73 rows=1 width=135) (actual
time=7.604..3532.471 rows=2303 loops=1)
-> Nested Loop (cost=0.00..19.67 rows=1 width=129)
(actual time=7.551..3489.175 rows=2303 loops=1)
-> Nested Loop Left Join (cost=0.00..19.19 rows=1
width=122) (actual time=7.504..3476.881 rows=2303 loops=1)
-> Nested Loop Left Join (cost=0.00..18.26
rows=1 width=122) (actual time=7.381..2856.871 rows=2303 loops=1)
-> Nested Loop Left Join
(cost=0.00..17.81 rows=1 width=114) (actual time=7.238..2265.604 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..17.51
rows=1 width=115) (actual time=7.137..2235.401 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=59) (actual
time=6.877..450.143 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.427..0.427 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.049..0.049 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..15.77 rows=1 width=0)
(actual time=0.715..0.715 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.007..0.008 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.254..0.254 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.266..0.267
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.008..0.008 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.003 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.003 rows=1 loops=2303)
Index Cond: (("S"."statusCode")::text =
("C"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist" "CH"
(cost=0.00..15.77 rows=1 width=0) (actual time=0.810..0.810 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..36.24 rows=1 width=139) (actual
time=0.354..74.668 rows=81 loops=1)
-> Nested Loop (cost=0.00..20.18 rows=1 width=133)
(actual time=0.327..74.191 rows=81 loops=1)
-> Nested Loop Left Join (cost=0.00..19.70 rows=1
width=128) (actual time=0.311..73.761 rows=81 loops=1)
-> Nested Loop Left Join (cost=0.00..18.77
rows=1 width=128) (actual time=0.294..57.050 rows=81 loops=1)
-> Nested Loop Left Join
(cost=0.00..18.32 rows=1 width=120) (actual time=0.276..54.400 rows=81 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..18.02
rows=1 width=121) (actual time=0.244..53.425 rows=85 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..1.54 rows=1 width=134) (actual time=0.142..32.752 rows=99 loops=1)
-> Index Scan using
"Alias_SearchName" on "Alias" "AL" (cost=0.00..0.48 rows=1 width=62) (actual
time=0.103..20.954 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=0.101..0.102 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.011..0.011 rows=0 loops=113)
Index
Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
-> Index Scan using
"Party_pkey" on "Party" "P" (cost=0.00..0.64 rows=1 width=20) (actual
time=0.203..0.204 rows=1 loops=99)
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..15.77 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
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.28
rows=1 width=26) (actual time=0.008..0.008 rows=1 loops=85)
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.030..0.030 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..0.92 rows=1 width=15) (actual time=0.203..0.204
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..0.56 rows=1 width=0) (actual
time=0.005..0.005 rows=0 loops=76)
Index Cond:
((("countyNo")::smallint = ($3)::smallint) AND (("caseNo")::text = ($2)::text)
AND (("dispoDate")::date > ($4)::date))
-> Index Scan using "County_pkey" on "County" "CY"
(cost=0.00..0.47 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=81)
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.003 rows=1 loops=81)
Index Cond: (("S"."statusCode")::text =
("C"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist" "CH"
(cost=0.00..15.77 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: 3626.249 ms
(88 rows)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
