From: | Ang Chin Han <angch(at)pollcities(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Hash Join not using hashed index? |
Date: | 2000-06-29 02:41:01 |
Message-ID: | 20000629104101.A932@pollcities.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, Jun 28, 2000 at 10:56:17AM -0400, Tom Lane wrote:
> Ang Chin Han <angch(at)pollcities(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-06-29 03:15:12 | Re: Hash Join not using hashed index? |
Previous Message | Nitin Bahadur | 2000-06-29 02:29:48 | SQL stored procedures and JDBC problem |