Re: Visibility map and freezing

Simon Riggs wrote:
> On Tue, 2009-01-20 at 11:03 +0200, Heikki Linnakangas wrote:
>>> - Are there some conditions where whole-table-scanning vacuum is
>> more
>>> effective than vacuums using visibility map? If so, we should
>> switch
>>> to full-scan *automatically*, without relying on user
>> configurations.
>>
>> Hmm, the only downside I can see is that skipping a page here and
>> there could defeat the OS read-ahead. Perhaps we should call
>> posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic
>> to only skip pages when there's at least N consecutive pages that can
>> be skipped.
>
> I would rather we didn't skip any pages at all unless the gains are
> significant. Skipping the odd page makes no difference from a
> performance perspective but may have a robustness impact.
>
> "Significant gains" should take into account the size of both heap and
> indexes, and recognise that we still scan whole indexes in either case.

That sounds pretty complex, approaching what the planner does. I'd
rather keep it simple.

Attached is a simple patch to only start skipping pages after 20
consecutive pages marked as visible in the visibility map. This doesn't
do any "look-ahead", so it will always scan the first 20 pages of a
table before it starts to skip pages, and whenever there's even one page
that needs vacuuming, the next 19 pages will also be vacuumed.

We could adjust that figure 20 according to table size. Or by
seq_page_cost/random_page_cost. But I'm leaning towards a simple
hard-coded value for now.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com