Re: problem with large maintenance_work_mem settings and

Stefan Kaltenbrunner wrote:
> hubert depesz lubaczewski wrote:
>> On 3/4/06, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote:
>>
>>>forgot to mention that this is 8.1.3 compiled from source. Further
>>>testing shows that not only CREATE INDEX has some issue with large
>>>maintenance_work_mem settings :
>>
>> what does it show:
>> cat /proc/sys/kernel/shmmax
>
> 1421326592
>
> not that I think it is related to the problem at all.

I can second that. Maintenance work mem is not allocated in shared memory.

> It looks like I'm
> hitting the MaxAllocSize Limit in src/include/utils/memutils.h.

There are two issues you have mentioned. This one is more obvious: the 
limitation of the memory that can be allocated.

The other one is the very bad performance for index creation. I can only 
guess, but is sound like this is related to the recent discussion on hackers 
about issues with the qsort performance. If the theory is true, your index 
creation should be much faster with a much lower setting for 
maintenance_work_mem, so that it uses external sort.

See the discussion starting here:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00590.php

Best Regards,
Michael Paesold