Re: hashjoin chosen over 1000x faster plan - Mailing list pgsql-performance

>>> On Wed, Oct 10, 2007 at  3:48 PM, in message
<470CF450.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:
> I'm not sure why it looks at the slow option at all; it seems like a
> remaining weakness in the OUTER JOIN optimizations.  If I change the query to
> use an inner join between the CaseHist table and the view, I get more of what
> I was expecting for the "slow" option.

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.

-Kevin

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" = "CH"."countyNo"
       AND "CHST"."caseNo" = "CH"."caseNo"
       AND "CHST"."histSeqNo" = "CH"."reopHistSeqNo"
         )
  LEFT OUTER JOIN "CaseTypeHistEventB" "CTHE"
      ON ( "CHST"."eventType" = "CTHE"."eventType"
       AND "CHST"."caseType" = "CTHE"."caseType"
         )
  WHERE (
        ("CH"."caseNo" = '2004CF002575')
    AND ("CH"."countyNo" = 13))
  ORDER BY
    "chargeNo",
    "chargeSeqNo"
;

 Sort  (cost=129.70..129.71 rows=4 width=168) (actual time=0.218..0.220 rows=4 loops=1)
   Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
   ->  Nested Loop Left Join  (cost=0.00..129.66 rows=4 width=168) (actual time=0.059..0.190 rows=4 loops=1)
         ->  Nested Loop Left Join  (cost=0.00..115.67 rows=4 width=129) (actual time=0.055..0.139 rows=4 loops=1)
               ->  Index Scan using "Charge_pkey" on "Charge" "CH"  (cost=0.00..10.69 rows=4 width=112) (actual
time=0.046..0.059rows=4 loops=1) 
                     Index Cond: ((("countyNo")::smallint = 13) AND (("caseNo")::bpchar = '2004CF002575'::bpchar))
               ->  Index Scan using "CaseHist_pkey" on "CaseHist" "CHST"  (cost=0.00..26.18 rows=5 width=41) (actual
time=0.013..0.014rows=0 loops=4) 
                     Index Cond: ((("CHST"."countyNo")::smallint = 13) AND (("CHST"."caseNo")::bpchar =
'2004CF002575'::bpchar)AND (("CHST"."histSeqNo")::smallint = ("CH"."reopHistSeqNo")::smallint)) 
         ->  Index Scan using "CaseTypeHistEventB_pkey" on "CaseTypeHistEventB" "CTHE"  (cost=0.00..3.48 rows=1
width=69)(actual time=0.008..0.009 rows=0 loops=4) 
               Index Cond: ((("CHST"."caseType")::bpchar = ("CTHE"."caseType")::bpchar) AND
(("CHST"."eventType")::bpchar= ("CTHE"."eventType")::bpchar)) 
 Total runtime: 0.410 ms
(11 rows)