Re: OUTER JOIN performance regression remains in 8.3beta4 - Mailing list pgsql-hackers

>>> On Fri, Jan 4, 2008 at  5:45 PM, in message
<477E70B1.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:
>>>> On Fri, Jan 4, 2008 at  4:51 PM, in message
> <477E640F.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
> <Kevin.Grittner@wicourts.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.616rows=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)
JoinFilter: (("PC"."pleaCode")::bpchar = ("CH"."pleaCode")::bpchar)        ->  Merge Left Join
(cost=200531.04..211991.33rows=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.89rows=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.111rows=4 loops=1)                                            Merge Cond: (("CH"."modSevClsCode")::bpchar
=("M"."sevClsCode")::bpchar)                                            ->  Sort  (cost=10.36..10.37 rows=4 width=94)
(actualtime=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.24rows=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.002rows=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)
(neverexecuted)                    ->  Sort  (cost=200439.15..203304.21 rows=1146024 width=98) (never executed)
                Sort Key: "CTHE"."eventType", "CTHE"."caseType", "CTHE"."countyNo"                          ->
SubqueryScan "CTHE"  (cost=148.78..41732.12 rows=1146024 width=98) (never executed)                                ->
MergeLeft Join  (cost=148.78..30271.88 rows=1146024 width=77) (never executed)
MergeCond: (((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=15917width=65) (never executed)                                            ->  Materialize  (cost=2.79..3.51
rows=72width=2) (never executed)                                                  ->  Seq Scan on "ControlRecord" c
(cost=0.00..2.72rows=72 width=2) (never executed)                                      ->  Sort  (cost=145.99..151.14
rows=2060width=15) (never executed)                                            Sort Key: d."caseType", d."eventType"
                                       ->  Seq Scan on "CaseTypeHistEventD" d  (cost=0.00..32.60 rows=2060 width=15)
(neverexecuted)        ->  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)Totalruntime: 1.070 ms 
-Kevin