Re: GUC for cleanup indexes threshold.

On Mon, Feb 20, 2017 at 7:26 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Fri, Feb 17, 2017 at 3:41 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Feb 16, 2017 at 6:17 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> On 15 February 2017 at 08:07, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>>> It's a bug. Attached latest version patch, which passed make check.
>>>
>>> In its current form, I'm not sure this is a good idea. Problems...
>>>
>>> 1. I'm pretty sure the world doesn't need another VACUUM parameter
>>>
>>> I suggest that we use the existing vacuum scale factor/4 to reflect
>>> that indexes are more sensitive to bloat.
>>
>> I do not think it's a good idea to control multiple behaviors with a
>> single GUC. We don't really know that dividing by 4 will be right for
>> everyone, or even for most people. It's better to have another
>> parameter with a sensible default than to hardcode a ratio that might
>> work out poorly for some people.
>>
>>> 2. The current btree vacuum code requires 2 vacuums to fully reuse
>>> half-dead pages. So skipping an index vacuum might mean that second
>>> index scan never happens at all, which would be bad.
>>
>> Maybe. If there are a tiny number of those half-dead pages in a huge
>> index, it probably doesn't matter. Also, I don't think it would never
>> happen, unless the table just never gets any more updates or deletes -
>> but that case could also happen today. It's just a matter of
>> happening less frequently.
>

Yeah thats right and I am not sure if it is worth to perform a
complete pass to reclaim dead/deleted pages unless we know someway
that there are many such pages. Also, I think we do reclaim the
complete page while allocating a new page in btree.

> The half-dead pages are never cleaned up if the ratio of pages
> containing garbage is always lower than threshold.
>

Which threshold are you referring here?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com