Skip site navigation (1) Skip section navigation (2)

Re: OUTER JOIN performance regression remains in8.3beta4

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"Kevin Grittner" <Kgrittn(dot)CCAP(dot)Courts(at)wicourts(dot)gov>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Date: 2008-01-05 00:01:12
Message-ID: 477E7468.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
>>> 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
 


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-01-05 00:46:15
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4
Previous:From: Kevin GrittnerDate: 2008-01-04 23:45:21
Subject: Re: OUTER JOIN performance regression remains in8.3beta4

pgsql-patches by date

Next:From: Tom LaneDate: 2008-01-05 00:46:15
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4
Previous:From: Kevin GrittnerDate: 2008-01-04 23:45:21
Subject: Re: OUTER JOIN performance regression remains in8.3beta4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group