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 23:15:12 -0400
Message-ID: <18724.962248512@sss.pgh.pa.us> (raw)
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>
Ang Chin Han <angch@pollcities.com> writes:
> 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;
> Sort (cost=15.63..15.63 rows=253 width=2)
> -> Seq Scan on country (cost=0.00..5.53 rows=253 width=2)
> 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?
In fact the planner does consider both of those alternatives (at
least in 7.0), but it estimates that they are *slower* than doing
it in what appears to be the hard way. Accessing an index is not
particularly cheap.
You can investigate what the planner thinks of these different
alternatives using the rather crude tool of disabling particular
scan types. For example:
regression=# create table country (country_id int2);
CREATE
regression=# create index country_id_index on country (country_id);
CREATE
regression=# explain select country_id from country order by country_id;
NOTICE: QUERY PLAN:
Index Scan using country_id_index on country (cost=0.00..60.00 rows=1000 width=2)
(Of course, since I haven't vacuumed this newly-created table, these
cost numbers are bogus; they are based on default assumptions about
table size, which are 1000 rows in 10 disk pages IIRC. With those
particular numbers an indexscan does look the cheapest. Anyway,
we're coming to the point now:)
regression=# set enable_indexscan = off;
SET VARIABLE
regression=# explain select country_id from country order by country_id;
NOTICE: QUERY PLAN:
Sort (cost=69.83..69.83 rows=1000 width=2)
-> Seq Scan on country (cost=0.00..20.00 rows=1000 width=2)
EXPLAIN
The planner chooses the indexscan here because 60.00 < 69.83, so it
thinks the indexscan is faster. But different values for the table
size and number of rows can easily drive it to make the other choice.
You could experiment with changing enable_indexscan and enable_seqscan
to see what the planner's estimates are for your tables and then
measure what the actual runtime is like for each plan type.
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: <18724.962248512@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> <20000629104101.A932@pollcities.com> <18724.962248512@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