beginner join optimization question

From: "Peter Su" <psu(at)jprc(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: beginner join optimization question
Date: 1998-06-24 19:27:31
Message-ID: 002101bd9fa6$21f035e0$da9356cf@hugo.jprc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Browse pgsql-sql by date

  From Date Subject
Next Message Mariana Travassos Aguiar da Silva 1998-06-24 21:03:16 Help
Previous Message Marin D 1998-06-24 14:51:31 Re: [SQL] foreign keys