Message-ID: <443AEF9B.3000909@drivefaster.net>
Date: Mon, 10 Apr 2006 17:51:55 -0600
From: Dan Harris <fbsd@drivefaster.net>
User-Agent: Thunderbird 1.5 (Macintosh/20060122)
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Encouraging multi-table join order
References: <443AAC26.4070802@drivefaster.net> <1543.1144710756@sss.pgh.pa.us>
In-Reply-To: <1543.1144710756@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> That's very strange --- the estimated cost of the seqscan is high enough
> that the planner should have chosen a nestloop with inner indexscan on
> the big table.  I'm not sure about the join-order point, but the hash
> plan for the first join seems wrong in any case.
>
> Um, you do have an index on eventactivity.incidentid, right?  What's the
> datatype(s) of the incidentid columns?  What happens to the plan if you
> turn off enable_hashjoin and enable_mergejoin?
>
> 			regards, tom lane
>   
Yes, eventactivity.incidentid is indexed.  The datatype is varchar(40).  
Although, by checking this, I noticed that k_h.incidentid was 
varchar(100).  Perhaps the difference in length between the keys caused 
the planner to not use the fastest method?  I have no defense as to why 
those aren't the same.. I will make them so and check.

Here's the EXPLAIN analyze with enable_hashjoin = off and 
enable_mergejoin = off :

Limit  (cost=4226535.73..4226544.46 rows=698 width=82) (actual 
time=74339.016..74356.521 rows=888 loops=1)
   ->  Unique  (cost=4226535.73..4226544.46 rows=698 width=82) (actual 
time=74339.011..74354.073 rows=888 loops=1)
         ->  Sort  (cost=4226535.73..4226537.48 rows=698 width=82) 
(actual time=74339.003..74344.031 rows=3599 loops=1)
               Sort Key: eventmain.entrydate, eventmain.incidentid, 
eventgeo.eventlocation, eventactivity.recordtext
               ->  Nested Loop  (cost=0.00..4226502.76 rows=698 
width=82) (actual time=921.325..74314.959 rows=3599 loops=1)
                     ->  Nested Loop  (cost=0.00..4935.61 rows=731 
width=72) (actual time=166.354..14638.308 rows=1162 loops=1)
                           ->  Nested Loop  (cost=0.00..2482.47 rows=741 
width=50) (actual time=150.396..7348.013 rows=1162 loops=1)
                                 ->  Index Scan using k_h_id_idx on k_h  
(cost=0.00..217.55 rows=741 width=14) (actual time=129.540..1022.243 
rows=1162 loops=1)
                                       Index Cond: (id = 33396)
                                       Filter: ((entrydate >= 
'2006-01-01 00:00:00'::timestamp without time zone) AND (entrydate < 
'2006-04-08 00:00:00'::timestamp without time zone))
                                 ->  Index Scan using 
eventgeo_incidentid_idx on eventgeo  (cost=0.00..3.04 rows=1 width=36) 
(actual time=5.260..5.429 rows=1 loops=1162)
                                       Index Cond: 
((eventgeo.incidentid)::text = ("outer".incidentid)::text)
                           ->  Index Scan using eventmain_incidentid_idx 
on eventmain  (cost=0.00..3.30 rows=1 width=22) (actual 
time=5.976..6.259 rows=1 loops=1162)
                                 Index Cond: 
((eventmain.incidentid)::text = ("outer".incidentid)::text)
                     ->  Index Scan using eventactivity1 on 
eventactivity  (cost=0.00..5774.81 rows=20 width=52) (actual 
time=29.768..51.334 rows=3 loops=1162)
                           Index Cond: (("outer".incidentid)::text = 
(eventactivity.incidentid)::text)
                           Filter: ((((' '::text || (recordtext)::text) 
|| ' '::text) ~~ '%HAL%'::text) AND (entrydate >= '2006-01-01 
00:00:00'::timestamp without time zone) AND (entrydate < '2006-04-08 
00:00:00'::timestamp without time zone))