From: Peter Su <psu@jprc.com>
To: pgsql-sql@postgresql.org
Subject: beginner join optimization question
Date: Wed, 24 Jun 1998 15:27:31 -0400
Message-ID: <002101bd9fa6$21f035e0$da9356cf@hugo.jprc.com> (raw)
I have two tables, say,
t1 (id, title)
t2 (id, hash)
I want to run a query of the form:
select title from t1,t2 where t1.id=t2.id and t2.hash=15;
I have indexes on all fields, but postgres insists on doing a nested loop
join between t1 and t2 which takes a lot of time.
If I do this
select title into temp from t1,t2 where t1.id=t2.id;
select * from temp where temp.hash=15;
things go much much faster because now postgres uses a hash join.
Is there any way to synthesize this behavior without using the temp. table?
thanks,
Pete
p.s. t1 has, say, 10K records and t2 is somewhat smaller, less then a
thousand records.
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: psu@jprc.com
Subject: Re: beginner join optimization question
In-Reply-To: <002101bd9fa6$21f035e0$da9356cf@hugo.jprc.com>
References: <002101bd9fa6$21f035e0$da9356cf@hugo.jprc.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