Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e5SEuN186318 for ; Wed, 28 Jun 2000 10:56:24 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA15411; Wed, 28 Jun 2000 10:56:18 -0400 (EDT) To: Ang Chin Han cc: pgsql-sql@postgresql.org Subject: Re: Hash Join not using hashed index? In-reply-to: <20000628152057.A30233@pollcities.com> References: <20000628132411.B29825@pollcities.com> <14589.962175604@sss.pgh.pa.us> <20000628152057.A30233@pollcities.com> Comments: In-reply-to Ang Chin Han message dated "Wed, 28 Jun 2000 15:20:57 +0800" Date: Wed, 28 Jun 2000 10:56:17 -0400 Message-ID: <15408.962204177@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200006/259 Ang Chin Han 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