Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6830B9FB837 for ; Wed, 10 Oct 2007 17:12:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 97107-07 for ; Wed, 10 Oct 2007 17:11:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id EFBFB9FB82F for ; Wed, 10 Oct 2007 17:11:58 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 10 Oct 2007 15:11:57 -0500 Message-Id: <470CEBB4.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Wed, 10 Oct 2007 15:11:48 -0500 From: "Kevin Grittner" To: "Simon Riggs" Cc: ,"Tom Lane" Subject: Re: hashjoin chosen over 1000x faster plan References: <470B99BE.EE98.0025.0@wicourts.gov> <1191997904.4233.125.camel@ebony.site> <470C9813.EE98.0025.0@wicourts.gov> <1192035955.4233.306.camel@ebony.site> <1192035955.4233.306.camel@ebony.site> <20980.1192039650@sss.pgh.pa.us> <470CD3E3.EE98.0025.0@wicourts.gov> <1192042492.4233.334.camel@ebony.site> <470CE34D.EE98.0025.0@wicourts.gov> <1192045945.4233.351.camel@ebony.site> In-Reply-To: <1192045945.4233.351.camel@ebony.site> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/110 X-Sequence-Number: 27132 >>> On Wed, Oct 10, 2007 at 2:52 PM, in message <1192045945.4233.351.camel@ebony.site>, Simon Riggs = wrote:=20 >=20 > The fast plan is an all-or-nothing plan. It is *only* faster when the > number of matched rows is zero. You know it is zero, but currently the > planner doesn't, nor is it able to make use of the information when it > has it, half thru execution. Even if we could work out the high > probability of it being zero, we would still be left with the decision > of whether to optimise for the zero or for the non-zero. =20 For a different case number which has four charges, two reopened: =20 Sort (cost=3D2450.27..2450.28 rows=3D4 width=3D146) (actual time=3D463.04= 8..463.052 rows=3D4 loops=3D1) Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo" -> Hash Left Join (cost=3D2318.93..2450.23 rows=3D4 width=3D146) = (actual time=3D462.857..462.995 rows=3D4 loops=3D1) Hash Cond: ((("CHST"."eventType")::bpchar =3D ("CTHE"."eventType")= ::bpchar) AND (("CHST"."caseType")::bpchar =3D ("CTHE"."caseType")::bpchar)= ) -> Nested Loop Left Join (cost=3D0.00..115.67 rows=3D4 = width=3D131) (actual time=3D0.045..0.165 rows=3D4 loops=3D1) -> Index Scan using "Charge_pkey" on "Charge" "CH" = (cost=3D0.00..10.69 rows=3D4 width=3D112) (actual time=3D0.036..0.053 = rows=3D4 loops=3D1) Index Cond: ((("countyNo")::smallint =3D 13) AND = (("caseNo")::bpchar =3D '2004CF002575'::bpchar)) -> Index Scan using "CaseHist_pkey" on "CaseHist" "CHST" = (cost=3D0.00..26.18 rows=3D5 width=3D41) (actual time=3D0.018..0.019 = rows=3D0 loops=3D4) Index Cond: ((("CHST"."countyNo")::smallint =3D 13) = AND (("CHST"."caseNo")::bpchar =3D '2004CF002575'::bpchar) AND (("CHST"."hi= stSeqNo")::smallint =3D ("CH"."reopHistSeqNo")::smallint)) -> Hash (cost=3D2084.82..2084.82 rows=3D15607 width=3D98) = (actual time=3D462.780..462.780 rows=3D15607 loops=3D1) -> Subquery Scan "CTHE" (cost=3D1630.43..2084.82 = rows=3D15607 width=3D98) (actual time=3D355.962..433.081 rows=3D15607 = loops=3D1) -> Merge Right Join (cost=3D1630.43..1928.75 = rows=3D15607 width=3D89) (actual time=3D355.960..414.249 rows=3D15607 = loops=3D1) Merge Cond: (((d."countyNo")::smallint =3D = "inner"."?column9?") AND ((d."caseType")::bpchar =3D "inner"."?column10?") = AND ((d."eventType")::bpchar =3D "inner"."?column11?")) -> Index Scan using "CaseTypeHistEventD_pkey" = on "CaseTypeHistEventD" d (cost=3D0.00..87.77 rows=3D2051 width=3D21) = (actual time=3D0.025..0.713 rows=3D434 loops=3D1) -> Sort (cost=3D1630.43..1669.45 rows=3D15607 = width=3D76) (actual time=3D355.320..365.251 rows=3D15607 loops=3D1) Sort Key: (c."countyNo")::smallint, = (b."caseType")::bpchar, (b."eventType")::bpchar -> Nested Loop (cost=3D0.00..543.41 = rows=3D15607 width=3D76) (actual time=3D0.035..46.914 rows=3D15607 = loops=3D1) -> Index Scan using "ControlRecord_= pkey" on "ControlRecord" c (cost=3D0.00..4.27 rows=3D1 width=3D2) (actual = time=3D0.010..0.019 rows=3D1 loops=3D1) Index Cond: (("countyNo")::sma= llint =3D 13) -> Seq Scan on "CaseTypeHistEventB"= b (cost=3D0.00..383.07 rows=3D15607 width=3D74) (actual time=3D0.019..14.= 069 rows=3D15607 loops=3D1) Total runtime: 464.588 ms (21 rows) =20 With set enable_hashjoin =3D off: =20 Sort (cost=3D3404.68..3404.69 rows=3D4 width=3D146) (actual time=3D448.04= 9..448.053 rows=3D4 loops=3D1) Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo" -> Merge Left Join (cost=3D3287.55..3404.64 rows=3D4 width=3D146) = (actual time=3D447.986..448.005 rows=3D4 loops=3D1) Merge Cond: (("outer"."?column16?" =3D "inner"."?column5?") AND = ("outer"."?column17?" =3D "inner"."?column6?")) -> Sort (cost=3D115.71..115.72 rows=3D4 width=3D131) (actual = time=3D0.179..0.182 rows=3D4 loops=3D1) Sort Key: ("CHST"."caseType")::bpchar, ("CHST"."eventType"):= :bpchar -> Nested Loop Left Join (cost=3D0.00..115.67 rows=3D4 = width=3D131) (actual time=3D0.051..0.139 rows=3D4 loops=3D1) -> Index Scan using "Charge_pkey" on "Charge" "CH" = (cost=3D0.00..10.69 rows=3D4 width=3D112) (actual time=3D0.040..0.053 = rows=3D4 loops=3D1) Index Cond: ((("countyNo")::smallint =3D 13) = AND (("caseNo")::bpchar =3D '2004CF002575'::bpchar)) -> Index Scan using "CaseHist_pkey" on "CaseHist" = "CHST" (cost=3D0.00..26.18 rows=3D5 width=3D41) (actual time=3D0.013..0.01= 4 rows=3D0 loops=3D4) Index Cond: ((("CHST"."countyNo")::smallint =3D = 13) AND (("CHST"."caseNo")::bpchar =3D '2004CF002575'::bpchar) AND = (("CHST"."histSeqNo")::smallint =3D ("CH"."reopHistSeqNo")::smallint)) -> Sort (cost=3D3171.84..3210.86 rows=3D15607 width=3D98) = (actual time=3D446.459..446.936 rows=3D768 loops=3D1) Sort Key: ("CTHE"."caseType")::bpchar, ("CTHE"."eventType"):= :bpchar -> Subquery Scan "CTHE" (cost=3D1630.43..2084.82 = rows=3D15607 width=3D98) (actual time=3D322.928..405.654 rows=3D15607 = loops=3D1) -> Merge Right Join (cost=3D1630.43..1928.75 = rows=3D15607 width=3D89) (actual time=3D322.922..381.371 rows=3D15607 = loops=3D1) Merge Cond: (((d."countyNo")::smallint =3D = "inner"."?column9?") AND ((d."caseType")::bpchar =3D "inner"."?column10?") = AND ((d."eventType")::bpchar =3D "inner"."?column11?")) -> Index Scan using "CaseTypeHistEventD_pkey" = on "CaseTypeHistEventD" d (cost=3D0.00..87.77 rows=3D2051 width=3D21) = (actual time=3D0.024..0.734 rows=3D434 loops=3D1) -> Sort (cost=3D1630.43..1669.45 rows=3D15607 = width=3D76) (actual time=3D322.294..332.182 rows=3D15607 loops=3D1) Sort Key: (c."countyNo")::smallint, = (b."caseType")::bpchar, (b."eventType")::bpchar -> Nested Loop (cost=3D0.00..543.41 = rows=3D15607 width=3D76) (actual time=3D0.035..45.539 rows=3D15607 = loops=3D1) -> Index Scan using "ControlRecord_= pkey" on "ControlRecord" c (cost=3D0.00..4.27 rows=3D1 width=3D2) (actual = time=3D0.010..0.016 rows=3D1 loops=3D1) Index Cond: (("countyNo")::sma= llint =3D 13) -> Seq Scan on "CaseTypeHistEventB"= b (cost=3D0.00..383.07 rows=3D15607 width=3D74) (actual time=3D0.019..13.= 754 rows=3D15607 loops=3D1) Total runtime: 449.660 ms (24 rows) =20 So in all cases it is faster without the hashjoin; it's just a question of whether it is 4% faster or 1000 times faster, with a 99+% chance of being 1000 times faster. =20 This may get back to a question I've always had about the wisdom of rounding fractional reads to whole numbers. You lose information which might lead to better plan choices. You can't read half a row, but you can read one row half the time. =20 -Kevin =20