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
lookupthrough the index. In your case you are touching 100% of customer and almost 100% of neicstats, or at least
that'swhat 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@postgresql.org so that your
> message can get through to the mailing list cleanly
>