>>> On Sun, Jan 6, 2008 at 7:20 PM, in message <[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote:
> "Kevin Grittner" <[EMAIL PROTECTED]> writes:
>> There was a serious performance regression in OUTER JOIN planning
>> going from 8.2.4 to 8.2.5. I know Tom came up with some patches to
>> mitigate the issues in 8.2.5, but my testing shows that problems
>> remain in 8.3beta4.
>
> Please try the attached proposed patch. It seems to fix my simplified
> test case, but I'm not sure if there are any additional considerations
> involved in your real queries.
Applied and built cleanly. Check found no errors. Startup clean.
Query returns expected rows. Plan looks good. Thanks!
-Kevin
Sort (cost=1789.74..1789.75 rows=5 width=226) (actual time=308.768..308.772
rows=4 loops=1)
Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
Sort Method: quicksort Memory: 18kB
-> Hash Left Join (cost=1643.49..1789.68 rows=5 width=226) (actual
time=308.630..308.723 rows=4 loops=1)
Hash Cond: (("CH"."sevClsCode")::bpchar = ("S"."sevClsCode")::bpchar)
-> Hash Left Join (cost=1641.95..1788.07 rows=5 width=195) (actual
time=308.522..308.601 rows=4 loops=1)
Hash Cond: (("CH"."modSevClsCode")::bpchar =
("M"."sevClsCode")::bpchar)
-> Hash Left Join (cost=1640.41..1786.50 rows=5 width=164)
(actual time=308.397..308.466 rows=4 loops=1)
Hash Cond: (("CH"."pleaCode")::bpchar =
("PC"."pleaCode")::bpchar)
-> Hash Left Join (cost=1639.19..1785.23 rows=5
width=128) (actual time=308.312..308.369 rows=4 loops=1)
Hash Cond: ((("CHST"."countyNo")::smallint =
("CTHE"."countyNo")::smallint) AND (("CHST"."eventType")::bpchar =
("CTHE"."eventType")::bpchar) AND (("CHST"."caseType")::bpchar =
("CTHE"."caseType")::bpchar))
-> Nested Loop Left Join (cost=0.00..116.14 rows=5
width=107) (actual time=0.049..0.093 rows=4 loops=1)
-> Index Scan using "Charge_pkey" on "Charge"
"CH" (cost=0.00..12.01 rows=5 width=94) (actual time=0.037..0.047 rows=4
loops=1)
Index Cond: ((("countyNo")::smallint =
53) AND (("caseNo")::bpchar = '2007CM003476'::bpchar))
-> Index Scan using "CaseHist_pkey" on
"CaseHist" "CHST" (cost=0.00..20.79 rows=3 width=32) (actual time=0.002..0.002
rows=0 loops=4)
Index Cond:
((("CHST"."countyNo")::smallint = 53) AND (("CHST"."caseNo")::bpchar =
'2007CM003476'::bpchar) AND (("CHST"."histSeqNo")::smallint =
("CH"."reopHistSeqNo")::smallint))
-> Hash (cost=1360.64..1360.64 rows=15917
width=98) (actual time=308.227..308.227 rows=15917 loops=1)
-> Subquery Scan "CTHE"
(cost=148.89..1360.64 rows=15917 width=98) (actual time=10.499..263.746
rows=15917 loops=1)
-> Merge Left Join
(cost=148.89..1201.47 rows=15917 width=77) (actual time=10.497..225.505
rows=15917 loops=1)
Merge Cond:
(((b."caseType")::bpchar = (d."caseType")::bpchar) AND ((b."eventType")::bpchar
= (d."eventType")::bpchar))
Join Filter:
((d."countyNo")::smallint = (c."countyNo")::smallint)
-> Nested Loop
(cost=2.90..953.87 rows=15917 width=67) (actual time=0.071..150.104 rows=15917
loops=1)
-> Index Scan using
"CaseTypeHistEventB_pkey" on "CaseTypeHistEventB" b (cost=0.00..632.63
rows=15917 width=65) (actual time=0.029..30.370 rows=15917 loops=1)
-> Materialize
(cost=2.90..2.91 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=15917)
-> Seq Scan on
"ControlRecord" c (cost=0.00..2.90 rows=1 width=2) (actual time=0.029..0.049
rows=1 loops=1)
Filter:
(("countyNo")::smallint = 53)
-> Sort (cost=145.99..151.14
rows=2060 width=15) (actual time=10.416..12.879 rows=2060 loops=1)
Sort Key: d."caseType",
d."eventType"
Sort Method: quicksort
Memory: 145kB
-> Seq Scan on
"CaseTypeHistEventD" d (cost=0.00..32.60 rows=2060 width=15) (actual
time=0.023..3.177 rows=2060 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=41) (actual
time=0.048..0.048 rows=10 loops=1)
-> Seq Scan on "PleaCode" "PC" (cost=0.00..1.10
rows=10 width=41) (actual time=0.008..0.024 rows=10 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=34) (actual
time=0.106..0.106 rows=24 loops=1)
-> Seq Scan on "SevClsCode" "M" (cost=0.00..1.24 rows=24
width=34) (actual time=0.008..0.044 rows=24 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=34) (actual time=0.089..0.089
rows=24 loops=1)
-> Seq Scan on "SevClsCode" "S" (cost=0.00..1.24 rows=24
width=34) (actual time=0.005..0.041 rows=24 loops=1)
Total runtime: 309.717 ms
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
