outer joins take forever

From: spivey_seth(at)yahoo(dot)com (Seth)
To: pgsql-general(at)postgresql(dot)org
Subject: outer joins take forever
Date: 2001-05-31 16:26:43
Message-ID: ced533a.0105310826.5ad5eb66@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm attempting to do an OUTER JOIN of two tables

neicstats (6841 rows)
customer (5062 rows)

I'm using the command

select * from neicstats left outer join customer on (
neicstats.cli_num = customer.cust_no );

which seems to run forever.

I've created indexes on both cli_num and cust_no but 'explain' seems
to indicate they're not being used -

explain select * from neicstats left outer join customer on (
neicstats.cli_num = customer.cust_no );
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..2297525.72 rows=285698 width=532)
-> Seq Scan on neicstats (cost=0.00..206.87 rows=6687 width=140)
-> Seq Scan on customer (cost=0.00..267.62 rows=5062 width=392)

What methods can I use to speed up this query? Why does it take so
long?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gerald Gutierrez 2001-05-31 17:16:09 Re: dumping strategy
Previous Message Andrew Sullivan 2001-05-31 15:56:18 Getting trigger names on a table