Re: join optimization problem

Toby Tremayne <toby@lyricist.com.au> writes:
> The output of explain analyze is this:

>                ->  Nested Loop  (cost=0.00..1657643.30 rows=65807 width=568) 
> (actual time=539.82..543404.25 rows=1537 loops=1)
>                      Join Filter: ("inner".thread_id = 
> ("outer".thread_id)::double precision)

Hm.  It would appear that you've been inconsistent about the datatype of
thread_id.  Current PG releases are not capable of using anything smarter
than a nested-loop join when there is a forced datatype coercion
involved in the join condition.  Try making your column datatypes the
same.

Also, do you really need a LEFT JOIN, or would plain inner join do?
It's hard for me to imagine a zero-message thread.  If you do have such
things, do you care whether this query finds them?  (I'm not sure that
eliminating the LEFT would really help any, but it couldn't hurt, since
LEFT restricts the planner's choices.)
        regards, tom lane