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

>>> On Wed, Oct 10, 2007 at  3:32 PM, in message <23650.1192048377@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I'd be very hesitant
> to make it choose a plan that is fast only if there were exactly zero
> such rows and is slow otherwise.

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
wasexpecting for the "slow" option.  This ten times faster, and I can't see why it would not be usable with an outer
join.

bigbird=# explain analyze
bigbird-# SELECT
bigbird-#     "CH"."caseNo",
bigbird-#     "CH"."countyNo",
bigbird-#     "CH"."chargeNo",
bigbird-#     "CH"."statuteCite",
bigbird-#     "CH"."sevClsCode",
bigbird-#     "CH"."modSevClsCode",
bigbird-#     "CH"."descr",
bigbird-#     "CH"."offenseDate",
bigbird-#     "CH"."pleaCode",
bigbird-#     "CH"."pleaDate",
bigbird-#     "CH"."chargeSeqNo",
bigbird-#     "CHST"."eventDate" AS "reopEventDate",
bigbird-#     "CTHE"."descr" AS "reopEventDescr"
bigbird-#   FROM "Charge" "CH"
bigbird-#   LEFT OUTER JOIN "CaseHist" "CHST"
bigbird-#       ON ( "CHST"."countyNo" = "CH"."countyNo"
bigbird(#        AND "CHST"."caseNo" = "CH"."caseNo"
bigbird(#        AND "CHST"."histSeqNo" = "CH"."reopHistSeqNo"
bigbird(#          )
bigbird-#   JOIN "CaseTypeHistEvent" "CTHE"
bigbird-#       ON ( "CHST"."eventType" = "CTHE"."eventType"
bigbird(#        AND "CHST"."caseType" = "CTHE"."caseType"
bigbird(#        AND "CHST"."countyNo" = "CTHE"."countyNo"
bigbird(#          )
bigbird-#   WHERE (
bigbird(#         ("CH"."caseNo" = '2004CF002575')
bigbird(#     AND ("CH"."countyNo" = 13))
bigbird-#   ORDER BY
bigbird-#     "chargeNo",
bigbird-#     "chargeSeqNo"
bigbird-# ;
                                                                                                     QUERY PLAN
                                   

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=126.69..126.70 rows=1 width=168) (actual time=36.854..36.855 rows=2 loops=1)
   Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
   ->  Nested Loop Left Join  (cost=0.00..126.68 rows=1 width=168) (actual time=36.465..36.623 rows=2 loops=1)
         Join Filter: ((d."countyNo")::smallint = (c."countyNo")::smallint)
         ->  Nested Loop  (cost=0.00..123.44 rows=1 width=185) (actual time=24.264..24.408 rows=2 loops=1)
               ->  Index Scan using "ControlRecord_pkey" on "ControlRecord" c  (cost=0.00..4.27 rows=1 width=2) (actual
time=9.424..9.427rows=1 loops=1) 
                     Index Cond: (13 = ("countyNo")::smallint)
               ->  Nested Loop  (cost=0.00..119.16 rows=1 width=185) (actual time=14.835..14.975 rows=2 loops=1)
                     ->  Nested Loop  (cost=0.00..115.67 rows=1 width=131) (actual time=8.346..8.463 rows=2 loops=1)
                           ->  Index Scan using "Charge_pkey" on "Charge" "CH"  (cost=0.00..10.69 rows=4 width=112)
(actualtime=5.723..8.228 rows=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.052..0.053 rows=0 loops=4) 
                                 Index Cond: ((13 = ("CHST"."countyNo")::smallint) AND ('2004CF002575'::bpchar =
("CHST"."caseNo")::bpchar)AND (("CHST"."histSeqNo")::smallint = ("CH"."reopHistSeqNo")::smallint)) 
                     ->  Index Scan using "CaseTypeHistEventB_pkey" on "CaseTypeHistEventB" b  (cost=0.00..3.48 rows=1
width=69)(actual time=3.248..3.250 rows=1 loops=2) 
                           Index Cond: ((("CHST"."caseType")::bpchar = (b."caseType")::bpchar) AND
(("CHST"."eventType")::bpchar= (b."eventType")::bpchar)) 
         ->  Index Scan using "CaseTypeHistEventD_CaseType" on "CaseTypeHistEventD" d  (cost=0.00..3.23 rows=1
width=17)(actual time=6.103..6.103 rows=0 loops=2) 
               Index Cond: (((d."caseType")::bpchar = (b."caseType")::bpchar) AND ((d."eventType")::bpchar =
(b."eventType")::bpchar))
 Total runtime: 46.072 ms
(18 rows)

-Kevin