>>> On Fri, Jan 4, 2008 at 5:45 PM, in message
<[EMAIL PROTECTED]>, "Kevin Grittner"
<[EMAIL PROTECTED]> wrote:
>>>> On Fri, Jan 4, 2008 at 4:51 PM, in message
> <[EMAIL PROTECTED]>, "Kevin Grittner"
> <[EMAIL PROTECTED]> wrote:
>
>> keyEventSeqNo | integer |
>
>> COALESCE(
>> CASE
>> WHEN d."eventType" IS NOT NULL THEN d."keyEventSeqNo"::smallint
>> ELSE b."keyEventSeqNo"::smallint
>> END::integer, 0) AS "keyEventSeqNo",
>
> That seems like a potential problem. I should probably be casting
> the literal of zero to "HistSeqNoT".
The cast generated a marginally lower cost estimate for the same plan.
With set enable_hashjoin = off a good plan is still chosen:
Sort (cost=211993.38..211993.39 rows=4 width=226) (actual time=0.611..0.616
rows=4 loops=1)
Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
Sort Method: quicksort Memory: 18kB
-> Nested Loop Left Join (cost=200532.15..211993.34 rows=4 width=226)
(actual time=0.461..0.587 rows=4 loops=1)
Join Filter: (("PC"."pleaCode")::bpchar = ("CH"."pleaCode")::bpchar)
-> Merge Left Join (cost=200531.04..211991.33 rows=4 width=190)
(actual time=0.400..0.417 rows=4 loops=1)
Merge Cond: ((("CHST"."eventType")::bpchar =
("CTHE"."eventType")::bpchar) AND (("CHST"."caseType")::bpchar =
("CTHE"."caseType")::bpchar) AND (("CHST"."countyNo")::smallint =
("CTHE"."countyNo")::smallint))
-> Sort (cost=91.88..91.89 rows=4 width=169) (actual
time=0.395..0.399 rows=4 loops=1)
Sort Key: "CHST"."eventType", "CHST"."caseType",
"CHST"."countyNo"
Sort Method: quicksort Memory: 18kB
-> Nested Loop Left Join (cost=14.13..91.84 rows=4
width=169) (actual time=0.324..0.374 rows=4 loops=1)
-> Merge Left Join (cost=14.13..14.31 rows=4
width=156) (actual time=0.315..0.346 rows=4 loops=1)
Merge Cond: (("CH"."sevClsCode")::bpchar =
("S"."sevClsCode")::bpchar)
-> Sort (cost=12.34..12.35 rows=4 width=125)
(actual time=0.153..0.158 rows=4 loops=1)
Sort Key: "CH"."sevClsCode"
Sort Method: quicksort Memory: 17kB
-> Merge Left Join (cost=12.15..12.30
rows=4 width=125) (actual time=0.097..0.111 rows=4 loops=1)
Merge Cond:
(("CH"."modSevClsCode")::bpchar = ("M"."sevClsCode")::bpchar)
-> Sort (cost=10.36..10.37
rows=4 width=94) (actual time=0.092..0.096 rows=4 loops=1)
Sort Key:
"CH"."modSevClsCode"
Sort Method: quicksort
Memory: 17kB
-> Index Scan using
"Charge_pkey" on "Charge" "CH" (cost=0.00..10.32 rows=4 width=94) (actual
time=0.047..0.059 rows=4 loops=1)
Index Cond:
((("countyNo")::smallint = 53) AND (("caseNo")::bpchar =
'2007CM003476'::bpchar))
-> Sort (cost=1.79..1.85 rows=24
width=34) (never executed)
Sort Key: "M"."sevClsCode"
-> Seq Scan on "SevClsCode"
"M" (cost=0.00..1.24 rows=24 width=34) (never executed)
-> Sort (cost=1.79..1.85 rows=24 width=34)
(actual time=0.122..0.140 rows=18 loops=1)
Sort Key: "S"."sevClsCode"
Sort Method: quicksort Memory: 18kB
-> Seq Scan on "SevClsCode" "S"
(cost=0.00..1.24 rows=24 width=34) (actual time=0.009..0.043 rows=24 loops=1)
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CHST" (cost=0.00..19.36 rows=2 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))
-> Materialize (cost=200439.15..214764.45 rows=1146024
width=98) (never executed)
-> Sort (cost=200439.15..203304.21 rows=1146024
width=98) (never executed)
Sort Key: "CTHE"."eventType", "CTHE"."caseType",
"CTHE"."countyNo"
-> Subquery Scan "CTHE" (cost=148.78..41732.12
rows=1146024 width=98) (never executed)
-> Merge Left Join (cost=148.78..30271.88
rows=1146024 width=77) (never executed)
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.79..23557.55
rows=1146024 width=67) (never executed)
-> Index Scan using
"CaseTypeHistEventB_pkey" on "CaseTypeHistEventB" b (cost=0.00..634.28
rows=15917 width=65) (never executed)
-> Materialize (cost=2.79..3.51
rows=72 width=2) (never executed)
-> Seq Scan on
"ControlRecord" c (cost=0.00..2.72 rows=72 width=2) (never executed)
-> Sort (cost=145.99..151.14 rows=2060
width=15) (never executed)
Sort Key: d."caseType",
d."eventType"
-> Seq Scan on
"CaseTypeHistEventD" d (cost=0.00..32.60 rows=2060 width=15) (never executed)
-> Materialize (cost=1.11..1.21 rows=10 width=41) (actual
time=0.004..0.023 rows=10 loops=4)
-> Seq Scan on "PleaCode" "PC" (cost=0.00..1.10 rows=10
width=41) (actual time=0.007..0.021 rows=10 loops=1)
Total runtime: 1.070 ms
-Kevin
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
