Re: outer joins take forever

I believe Tom mentioned this sometime ago. If you are picking most of the rows then a seq_scan is preferable to a lookup through the index. In your case you are touching 100% of customer and almost 100% of neicstats, or at least that's what the optimizer thinks.
Try vacuum_analyzing the tables in case the optimizer is fooled by older stats.

cheers,
t.

On 31 May 2001, Seth wrote:

> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>