Message-Id: <470D074D.EE98.0025.0@wicourts.gov>
Date: Wed, 10 Oct 2007 17:09:34 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Tom Lane" <tgl@sss.pgh.pa.us>,
	"Kevin Grittner" <Kgrittn.CCAP.Courts@wicourts.gov>
Cc: "Simon Riggs" <simon@2ndquadrant.com>, <pgsql-performance@postgresql.org>
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><470CE34D.EE98.0025.0@wicourts.gov>
	<23650.1192048377@sss.pgh.pa.us> <470CF450.EE98.0025.0@wicourts.gov>
In-Reply-To: <470CF450.EE98.0025.0@wicourts.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Wed, Oct 10, 2007 at  3:48 PM, in message
<470CF450.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:=20
> I'm not sure why it looks at the slow option at all; it seems like a=20
> remaining weakness in the OUTER JOIN optimizations.  If I change the =
query to=20
> use an inner join between the CaseHist table and the view, I get more of =
what=20
> I was expecting for the "slow" option.
=20
Just to wrap this up (from my perspective), it looks like we're
headed to a workaround of using the underlying "base" table instead
of the view.  We ignore any county override of our description, but
performance is good, and they were reluctant to change it to an inner
join.
=20
-Kevin
=20
SELECT
    "CH"."caseNo",
    "CH"."countyNo",
    "CH"."chargeNo",
    "CH"."statuteCite",
    "CH"."sevClsCode",
    "CH"."modSevClsCode",
    "CH"."descr",
    "CH"."offenseDate",
    "CH"."pleaCode",
    "CH"."pleaDate",
    "CH"."chargeSeqNo",
    "CHST"."eventDate" AS "reopEventDate",
    "CTHE"."descr" AS "reopEventDescr"
  FROM "Charge" "CH"
  LEFT OUTER JOIN "CaseHist" "CHST"
      ON ( "CHST"."countyNo" =3D "CH"."countyNo"
       AND "CHST"."caseNo" =3D "CH"."caseNo"
       AND "CHST"."histSeqNo" =3D "CH"."reopHistSeqNo"
         )
  LEFT OUTER JOIN "CaseTypeHistEventB" "CTHE"
      ON ( "CHST"."eventType" =3D "CTHE"."eventType"
       AND "CHST"."caseType" =3D "CTHE"."caseType"
         )
  WHERE (
        ("CH"."caseNo" =3D '2004CF002575')
    AND ("CH"."countyNo" =3D 13))
  ORDER BY
    "chargeNo",
    "chargeSeqNo"
;
=20
 Sort  (cost=3D129.70..129.71 rows=3D4 width=3D168) (actual time=3D0.218..0=
.220 rows=3D4 loops=3D1)
   Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
   ->  Nested Loop Left Join  (cost=3D0.00..129.66 rows=3D4 width=3D168) =
(actual time=3D0.059..0.190 rows=3D4 loops=3D1)
         ->  Nested Loop Left Join  (cost=3D0.00..115.67 rows=3D4 =
width=3D129) (actual time=3D0.055..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.046..0.059 =
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.014 =
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))
         ->  Index Scan using "CaseTypeHistEventB_pkey" on "CaseTypeHistEve=
ntB" "CTHE"  (cost=3D0.00..3.48 rows=3D1 width=3D69) (actual time=3D0.008..=
0.009 rows=3D0 loops=3D4)
               Index Cond: ((("CHST"."caseType")::bpchar =3D ("CTHE"."caseT=
ype")::bpchar) AND (("CHST"."eventType")::bpchar =3D ("CTHE"."eventType")::=
bpchar))
 Total runtime: 0.410 ms
(11 rows)