Re: 7.4 performance issue

Christophe Musielak <cmusielak(at)akio-software(dot)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