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 e5S70B153250 for ; Wed, 28 Jun 2000 03:00:11 -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 DAA14592; Wed, 28 Jun 2000 03:00:05 -0400 (EDT) To: Ang Chin Han cc: pgsql-sql@postgresql.org Subject: Re: Hash Join not using hashed index? In-reply-to: <20000628132411.B29825@pollcities.com> References: <20000628132411.B29825@pollcities.com> Comments: In-reply-to Ang Chin Han message dated "Wed, 28 Jun 2000 13:24:11 +0800" Date: Wed, 28 Jun 2000 03:00:04 -0400 Message-ID: <14589.962175604@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200006/251 Hash joins don't have anything to do with hash indexes. A hash join is a join that makes use of a temporary hashtable built on-the-fly *in memory* for that join. 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. Plain btree indexes on city.country_id and country.country_id might work better --- at least they'd offer the option of a merge join without doing explicit sort. regards, tom lane