Re: problem with large maintenance_work_mem settings and

Michael Paesold wrote:
> Stefan Kaltenbrunner wrote:
>
>> hubert depesz lubaczewski wrote:
>>
>>> On 3/4/06, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)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.

yes

>
> 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

I was following this thread - and it was partly a reason why I'm playing
with that(the CREATE INDEX on that table finished after about 12 hours
with a bit less 2GB for maintenance_work_mem(for comparision it took me
only about 2,5hours to create this table) .
I'm currently testing who long it takes with very low settings to force
an external sort.

Stefan