pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ang Chin Han <angch@pollcities.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hash Join not using hashed index?
Date: Thu, 29 Jun 2000 10:41:01 +0800
Message-ID: <20000629104101.A932@pollcities.com> (raw)
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>

On Wed, Jun 28, 2000 at 10:56:17AM -0400, Tom Lane wrote:
> Ang Chin Han <angch@pollcities.com> 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.



view thread (6+ messages)  latest in thread

Message-ID: <20000629104101.A932@pollcities.com>
Permalink:  ../20000629104101.A932@pollcities.com/
Also on:    postgresql.org/message-id/20000629104101.A932@pollcities.com

 · 

reply

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: angch@pollcities.com, tgl@sss.pgh.pa.us
  Subject: Re: Hash Join not using hashed index?
  In-Reply-To: <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> <20000629104101.A932@pollcities.com>

* 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