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>
Cc: <pgsql-hackers(at)postgresql(dot)org>,<pgsql-patches(at)postgresql(dot)org>
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Date: 2008-01-07 15:01:50
Message-ID: 4781EA7E.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
>>> On Sun, Jan 6, 2008 at  7:20 PM, in message <29913(dot)1199668810(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> 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


In response to

Responses

pgsql-hackers by date

Next:From: Gregory StarkDate: 2008-01-07 15:18:46
Subject: Re: Index trouble with 8.3b4
Previous:From: Usama DarDate: 2008-01-07 15:00:05
Subject: Re: VACUUM FULL out of memory

pgsql-patches by date

Next:From: Kevin GrittnerDate: 2008-01-07 16:07:24
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Previous:From: Tom LaneDate: 2008-01-07 01:20:10
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4

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