Re: PENDING_LIST_CLEANUP_SIZE - maximum size of GIN pending list Re: HEAD seems to generate larger WAL regarding GIN index

On Wed, Sep 24, 2014 at 11:10 AM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> (2014/09/13 2:42), Fujii Masao wrote:
>>
>> On Wed, Sep 10, 2014 at 10:37 PM, Alvaro Herrera
>> <alvherre(at)2ndquadrant(dot)com> wrote:
>>>
>>> Fujii Masao wrote:
>>>>
>>>> On Wed, Sep 10, 2014 at 12:15 PM, Etsuro Fujita
>>>> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>
>>>
>>>>> PENDING_LIST_CLEANUP_SIZE and work_mem, for this setting.
>>>>> Wouldn't it be easy-to-use to have only one parameter,
>>>>> PENDING_LIST_CLEANUP_SIZE? How about setting PENDING_LIST_CLEANUP_SIZE
>>>>> to
>>>>> work_mem as the default value when running the CREATE INDEX command?
>>>>
>>>>
>>>> That's an idea. But there might be some users who want to change
>>>> the cleanup size per session like they can do by setting work_mem,
>>>> and your idea would prevent them from doing that...
>>>>
>>>> So what about introduing pending_list_cleanup_size also as GUC?
>>>> That is, users can set the threshold by using either the reloption or
>>>> GUC.
>>>
>>>
>>> Yes, I think having both a GUC and a reloption makes sense -- the GUC
>>> applies to all indexes, and can be tweaked for individual indexes using
>>> the reloption.
>>
>>
>> Agreed.
>>
>>> I'm not sure about the idea of being able to change it per session,
>>> though. Do you mean that you would like insert processes use a very
>>> large value so that they can just append new values to the pending list,
>>> and have vacuum use a small value so that it cleans up as soon as it
>>> runs? Two things: 1. we could have an "autovacuum_" reloption which
>>> only changes what autovacuum does; 2. we could have autovacuum run
>>> index cleanup actions separately from actual vacuuming.
>>
>>
>> Yes, I was thinking something like that. But if autovacuum
>> has already been able to handle that, it's nice. Anyway,
>> as you pointed out, it's better to have both GUC and reloption
>> for the cleanup size of pending list.
>
>
> OK, I'd vote for your idea of having both the GUC and the reloption. So, I
> think the patch needs to be updated. Fujii-san, what plan do you have about
> the patch?

Please see the attached patch. In this patch, I introduced the GUC parameter,
pending_list_cleanup_size. I chose 4MB as the default value of the parameter.
But do you have any better idea about that default value?

BTW, I moved the CommitFest entry of this patch to next CF 2014-10.

Regards,

--
Fujii Masao