From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ang Chin Han <angch@pollcities.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hash Join not using hashed index?
Date: Wed, 28 Jun 2000 10:56:17 -0400
Message-ID: <15408.962204177@sss.pgh.pa.us> (raw)
In-Reply-To: <20000628152057.A30233@pollcities.com>
References: <20000628132411.B29825@pollcities.com>
<14589.962175604@sss.pgh.pa.us>
<20000628152057.A30233@pollcities.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, angch@pollcities.com
Subject: Re: Hash Join not using hashed index?
In-Reply-To: <15408.962204177@sss.pgh.pa.us>
References: <20000628132411.B29825@pollcities.com> <14589.962175604@sss.pgh.pa.us> <20000628152057.A30233@pollcities.com> <15408.962204177@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox