>>> On Fri, Jan 4, 2008 at 5:45 PM, in message
<477E70B1(dot)EE98(dot)0025(dot)0(at)wicourts(dot)gov>, "Kevin Grittner"
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>>> On Fri, Jan 4, 2008 at 4:51 PM, in message
> <477E640F(dot)EE98(dot)0025(dot)0(at)wicourts(dot)gov>, "Kevin Grittner"
> <Kevin(dot)Grittner(at)wicourts(dot)gov> 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