Re: 7.4 performance issue

Christophe Musielak <cmusielak@akio-software.com> writes:
>                            ->  Seq Scan on threads t
> (cost=0.00..19431.23
> rows=82667 width=583)
>                                  Filter: (((mailbox_id = 2) OR
> (mailbox_id
> = 3) OR (mailbox_id = 5) OR (mailbox_id = 20) OR (mailbox_id = 21) OR
> (mailbox_id = 13) OR                (mailbox_id = 22) OR (mailbox_id =
> 23)
> OR (mailbox_id = 24) OR (mailbox_id = 25) OR (mailbox_id = 26)
>             OR (mailbox_id = 19)) AND (desktop = 2))

Although it's hard to be sure without EXPLAIN ANALYZE output, it seems
like an index on threads(mailbox_id) might help here.  You have a bunch
of other indexes on threads that are useless for this query :-( ...
I hope they are some good for other queries, else they're just slowing
down updates.

Also a larger value of sort_mem might help.  At least by the planner's
estimates, the other big hit is the use of a merge join between emails
and threads.  I think a hash join would work better, but it's not going
to pick that unless the inner relation will fit in sort_mem.

            regards, tom lane