Re: Hash Join not using hashed index?

Ang Chin Han <angch@pollcities.com> writes:
>> The planner could choose to use an indexscan on a hash index
>> as an input for the join, but it'd only be likely to do so
>> if there is a restriction clause matching the index.  In your
>> example you have only a join WHERE clause.

> Well, in my original query, there was, but the plan's the same.
> Probably the clause wasn't restrictive enough (" and region < n").

If it was like that then a hash index wouldn't have been applicable
anyway; hashes are only good for strict equality checks.  If you want
something that can do ordering checks you need a btree index.

(There are good reasons why btree is the default index type ;-))

> Original cost est:
> Hash Join  (cost=8.85..16.76 rows=75 width=18)
>   -> Seq Scan on city  (cost=0.00..1.75 rows=75 width=16) 
>   -> Hash  (cost=5.53..5.53 rows=253 width=2)
>        -> Seq Scan on country  (cost=0.00..5.53 rows=253 width=2) 

> I guess the problem is that country-city is a one-to-many relation,
> BUT I've more countries than cities (note the # of rows above), thus
> throwing the planner off...

Off what?  This looks like a pretty reasonable plan to me, given the
fairly small table sizes.  Do you have evidence that another plan
type would be quicker for this problem?
        regards, tom lane