Received: from kosh.pintoo.com (kosh.pollcities.com [202.186.13.219]) by hub.org (8.10.1/8.10.1) with ESMTP id e5T2jx112200 for ; Wed, 28 Jun 2000 22:45:59 -0400 (EDT) Received: (from angch@localhost) by kosh.pintoo.com (8.9.3/8.9.3) id KAA01028; Thu, 29 Jun 2000 10:41:01 +0800 Date: Thu, 29 Jun 2000 10:41:01 +0800 From: Ang Chin Han To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: Hash Join not using hashed index? Message-ID: <20000629104101.A932@pollcities.com> References: <20000628132411.B29825@pollcities.com> <14589.962175604@sss.pgh.pa.us> <20000628152057.A30233@pollcities.com> <15408.962204177@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0.1i In-Reply-To: <15408.962204177@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Wed, Jun 28, 2000 at 10:56:17AM -0400 X-Archive-Number: 200007/8 On Wed, Jun 28, 2000 at 10:56:17AM -0400, Tom Lane wrote: > Ang Chin Han writes: > 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 ;-)) There _was_ a btree index, before I added the extra hash index: pintoo=# \dcountry_pkey Index "country_pkey" Attribute | Type ------------+---------- country_id | smallint unique btree (primary key) > > 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? No evidence, but I was hoping that having a prehashed country_id would speed things up a bit, since the seq scan on country could be redundant, requring only a seq scan on city and a index (hash) lookup on country. Or maybe this is a related question (just curious): pintoo=# explain select country_id from country order by country_id; NOTICE: QUERY PLAN: Sort (cost=15.63..15.63 rows=253 width=2) -> Seq Scan on country (cost=0.00..5.53 rows=253 width=2) pintoo=# explain select name from country order by name; NOTICE: QUERY PLAN: Sort (cost=15.63..15.63 rows=253 width=12) -> Seq Scan on country (cost=0.00..5.53 rows=253 width=12) If there is already in b-tree index on country_id, why bother re-sorting it, when it could be output'd by traversing the tree? Comparing with an unindexed column, we can see that the index is not used at all.