Thread: Join question

Join question

From
Mike Friesen
Date:
Hi,

I have two tables which have an identical column and their indexes are
based on this column.  I am trying to do a join on these two tables, but
it takes forever for postgresql to join these two tables.

The command i'm using is:
select * from icpr,iprc where icpr.prod_no='23' AND
icpr.prod_no=iprc.prod_no;

Can anyone tell me what I'm doing wrong?  I've tried to cluster both
tables based on their indexes, but it's still extremely slow..

Any help would be greatly appreciated,

Mike



Re: Join question

From
Tom Lane
Date:
Mike Friesen <mfriesen@pmcanada.com> writes:
> select * from icpr,iprc where icpr.prod_no='23' AND
> icpr.prod_no=iprc.prod_no;

> Can anyone tell me what I'm doing wrong?

Probably you're getting a poor plan for the query (likely, it's
using a nested loop).  Have you done VACUUM ANALYZE lately for
these tables?  If so, what does EXPLAIN show for the query?

            regards, tom lane