>>> Tom Lane <t...@sss.pgh.pa.us> wrote:
> After a lot of distractions, I've finished applying the planner
fixes
> that seem necessary in view of your report about poorer planning in
8.4
> than 8.3. When you have a chance, it would be useful to do a
thorough
> test of CVS HEAD on your data and query mix --- are there any other
> places where we have regressed relative to 8.3?
You probably already know this, but on the query referenced earlier in
the thread, a great plan is now generated! Even when not cached, this
executed in just under seven seconds. (I chose these values for
testing this query because it had been logged as exceeding 20 seconds
under 8.3.) Cached, EXPLAIN ANALYZE runs between 225 and 250 ms.
Running it without EXPLAIN the psql \timing is between 265 and 277 ms.
EXPLAIN gives a \timing averaging 80 ms.
I will see what kind of testing I can put together to try to shake out
any remaining issues.
-Kevin
Sort (cost=609318.69..609324.64 rows=2377 width=136) (actual
time=6993.549..6994.589 rows=2388 loops=1)
Sort Key: "C"."caseNo"
Sort Method: quicksort Memory: 692kB
-> HashAggregate (cost=609161.63..609185.40 rows=2377 width=136) (actual
time=6986.256..6989.144 rows=2388 loops=1)
-> Append (cost=0.00..609060.61 rows=2377 width=136) (actual
time=14.514..6977.019 rows=2396 loops=1)
-> Nested Loop (cost=0.00..580976.79 rows=2272 width=136)
(actual time=14.512..6505.233 rows=2315 loops=1)
-> Nested Loop Left Join (cost=0.00..371212.96 rows=2272
width=129) (actual time=14.441..6445.091 rows=2315 loops=1)
-> Nested Loop (cost=0.00..358250.51 rows=2221
width=121) (actual time=14.294..3821.576 rows=2315 loops=1)
-> Nested Loop Left Join
(cost=0.00..357628.46 rows=2221 width=115) (actual time=14.259..3809.529
rows=2315 loops=1)
-> Nested Loop Left Join
(cost=0.00..323081.09 rows=2221 width=115) (actual time=6.324..1322.972
rows=2315 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 Anti Join
(cost=0.00..322299.90 rows=2221 width=116) (actual time=6.211..1296.189
rows=2609 loops=1)
-> Nested Loop
(cost=0.00..317628.98 rows=3355 width=116) (actual time=6.109..1191.783
rows=3719 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 (alternative subplans))))))
-> Index Scan using
"Party_SearchName" on "Party" "P" (cost=0.00..14.38 rows=3073 width=60)
(actual time=5.918..935.944 rows=4132 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..11.25 rows=1 width=72) (actual
time=0.054..0.054 rows=1 loops=4132)
Index Cond:
((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("P"."caseNo")::text))
Filter:
("C"."isExpunge" <> true)
SubPlan
-> Seq Scan on
"CaseHist" "CHPET" (cost=0.00..5080459.80 rows=84980 width=15) (never executed)
Filter:
(("eventType")::text = ANY ('{FWBCA,CCTRO}'::text[]))
-> Index Scan using
"CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..92.03 rows=1 width=0)
(actual time=0.776..0.776 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
"HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..1.38 rows=1 width=15)
(actual time=0.026..0.026 rows=0 loops=3719)
Index Cond:
((("HCA"."countyNo")::smallint = ("C"."countyNo")::smallint) AND
(("HCA"."caseNo")::text = ("C"."caseNo")::text))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.33
rows=1 width=26) (actual time=0.007..0.008 rows=1 loops=2609)
Index Cond:
((("WPCT"."profileName")::text = 'PUBLIC'::text) AND (("C"."caseType")::text =
("WPCT"."caseType")::text) AND (("C"."countyNo")::smallint =
("WPCT"."countyNo")::smallint))
-> Index Scan using "CaseDispo_pkey" on
"CaseDispo" "CD" (cost=0.00..15.54 rows=1 width=15) (actual time=1.071..1.072
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..11.75 rows=1 width=0)
(actual time=0.008..0.008 rows=0 loops=2110)
Index Cond:
((("countyNo")::smallint = ($12)::smallint) AND (("caseNo")::text =
($11)::text) AND (("dispoDate")::date > ($13)::date))
-> 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=2315)
Index Cond: (("S"."statusCode")::text =
("C"."statusCode")::text)
-> Index Scan using "LastCaseMod_pkey" on
"LastCaseMod" "LCM" (cost=0.00..5.82 rows=1 width=23) (actual
time=1.128..1.131 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 "County_pkey" on "County" "CY"
(cost=0.00..0.27 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=2315)
Index Cond: (("CY"."countyNo")::smallint =
("C"."countyNo")::smallint)
SubPlan
-> Seq Scan on "CaseHist" "CH" (cost=0.00..5080459.80
rows=84980 width=15) (never executed)
Filter: (("eventType")::text = ANY
('{FWBCA,CCTRO}'::text[]))
-> Index Scan using "CaseHist_pkey" on "CaseHist" "CH"
(cost=0.00..92.03 rows=1 width=0) (actual time=1.038..1.038 rows=0 loops=35)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
('{FWBCA,CCTRO}'::text[]))
-> Nested Loop Left Join (cost=0.00..28060.05 rows=105
width=140) (actual time=4.393..469.526 rows=81 loops=1)
-> Nested Loop (cost=0.00..17794.50 rows=103 width=132)
(actual time=4.364..394.062 rows=81 loops=1)
-> Nested Loop (cost=0.00..17765.65 rows=103
width=126) (actual time=4.357..393.648 rows=81 loops=1)
-> Nested Loop Left Join
(cost=0.00..17728.88 rows=103 width=121) (actual time=4.350..393.184 rows=81
loops=1)
-> Nested Loop Left Join
(cost=0.00..16126.73 rows=103 width=121) (actual time=4.335..309.259 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..16090.50 rows=103 width=122) (actual time=4.317..308.165 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 (alternative subplans))))))
-> Nested Loop Anti Join
(cost=0.00..1879.23 rows=133 width=135) (actual time=0.168..9.332 rows=99
loops=1)
-> Nested Loop
(cost=0.00..1599.40 rows=201 width=135) (actual time=0.156..7.477 rows=113
loops=1)
-> Index Scan
using "Alias_SearchName" on "Alias" "AL" (cost=0.00..8.71 rows=140 width=63)
(actual time=0.136..2.888 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..11.35 rows=1 width=72) (actual
time=0.037..0.038 rows=1 loops=113)
Index
Cond: ((("C"."countyNo")::smallint = ("AL"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("AL"."caseNo")::text))
Filter:
("C"."isExpunge" <> true)
-> Index Scan using
"HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..1.38 rows=1 width=15)
(actual time=0.015..0.015 rows=0 loops=113)
Index Cond:
((("HCA"."countyNo")::smallint = ("C"."countyNo")::smallint) AND
(("HCA"."caseNo")::text = ("C"."caseNo")::text))
-> Index Scan using
"Party_pkey" on "Party" "P" (cost=0.00..14.75 rows=1 width=20) (actual
time=3.012..3.013 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
-> Seq Scan on "CaseHist"
"CHPET" (cost=0.00..5080459.80 rows=84980 width=15) (never executed)
Filter:
(("eventType")::text = ANY ('{FWBCA,CCTRO}'::text[]))
-> Index Scan using
"CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..92.03 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.33
rows=1 width=26) (actual time=0.009..0.009 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 "CaseDispo_pkey" on
"CaseDispo" "CD" (cost=0.00..15.54 rows=1 width=15) (actual time=1.033..1.034
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..11.75 rows=1 width=0)
(actual time=0.006..0.006 rows=0 loops=76)
Index Cond:
((("countyNo")::smallint = ($5)::smallint) AND (("caseNo")::text = ($4)::text)
AND (("dispoDate")::date > ($6)::date))
-> Index Scan using "County_pkey" on "County"
"CY" (cost=0.00..0.34 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)
-> Index Scan using "LastCaseMod_pkey" on "LastCaseMod"
"LCM" (cost=0.00..5.82 rows=1 width=23) (actual time=0.927..0.927 rows=1
loops=81)
Index Cond: ((("LCM"."countyNo")::smallint =
("C"."countyNo")::smallint) AND (("LCM"."caseNo")::text = ("C"."caseNo")::text)
AND (("LCM"."updateTypeId")::integer = 0))
SubPlan
-> Seq Scan on "CaseHist" "CH" (cost=0.00..5080459.80
rows=84980 width=15) (never executed)
Filter: (("eventType")::text = ANY
('{FWBCA,CCTRO}'::text[]))
-> Index Scan using "CaseHist_pkey" on "CaseHist" "CH"
(cost=0.00..92.03 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: 6997.559 ms
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
