Re: slow self-join query

also also wik

On Sun, Mar 18, 2012 at 8:37 AM, Robert Poor <rdpoor(at)gmail(dot)com> wrote:
> On Sat, Mar 17, 2012 at 23:07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>>
>> Yeah try setting [work_mem] to something absurd like 500MB and see if the
>> plan changes.
>
>
> Suweet!  Sorting now runs in-memory, and that makes a big difference, even
> when groveling over 1M records (under 12 seconds rather than 7 hours).
>  Results in
>
>    http://explain.depesz.com/s/hNO

Well that's better. Test various sizes of work_mem to see what you
need, then maybe double it. How many simultaneous connections do you
have to this db? Different accounts? Different apps? While it might
be worth setting for a user or a db, it might or might not be a good
thing to set it to something like 512MB world-wide. On servers with
hundreds to thousands of connections, 16 or 32MB is often all you'd
want to set it to, since it's additive across all active sorts in the
db. A thousand users suddenly sorting 512MB in memory at once, can
take down your db server in seconds.

Still seems like it's doing a lot of work.