Re: optimizing vacuum truncation scans

On 22 July 2015 at 17:11, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
>
>> On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> > Attached is a patch that implements the vm scan for truncation. It
>> > introduces a variable to hold the last blkno which was skipped during
>> the
>> > forward portion. Any blocks after both this blkno and after the last
>> > inspected nonempty page (which the code is already tracking) must have
>> been
>> > observed to be empty by the current vacuum. Any other process
>> rendering the
>> > page nonempty are required to clear the vm bit, and no other process
>> can set
>> > the bit again during the vacuum's lifetime. So if the bit is still
>> set, the
>> > page is still empty without needing to inspect it.
>>
>> Urgh. So if we do this, that forever precludes having HOT pruning set
>> the all-visible bit.
>
>
> I wouldn't say forever, as it would be easy to revert the change if
> something more important came along that conflicted with it.
>

I think what is being said here is that someone is already using this
technique, or if not, then we actively want to encourage them to do so as
an extension or as a submission to core.

In that case, I think the rely-on-VM technique sinks again, sorry Jim,
Jeff. Probably needs code comments added.

That does still leave the prefetch technique, so all is not lost.

Can we see a patch with just prefetch, probably with a simple choice of
stride? Thanks.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services