Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>>> normally ;-)
>>
>> In my experience when doing sorts in isolation, having more work_mem
>> is a bad thing, unless it enables you to remove a layer of
>> tape-merging. I always blamed it on the L1/L2 etc. levels of caching.
>
> Blame it on quicksort, which is quite cache-unfriendly.

The observation applies to heap sort. If you can't set work_mem large
enough to do the sort in memory, then you want to set it just barely
large enough to avoid two layers of tape sorting. Any larger than
that reduces performance rather than increasing it. Of course that
assumes you have the luxury of knowing ahead of time exactly how large
your sort will be and can set work_mem accordingly on a case by case
basis, which is unlikely in the real world.

> Perhaps PG should consider using in-memory mergesort for the bigger chunks.

Cheers,

Jeff