Re: [PERFORM] encouraging index-only scans

On 9/5/13 8:29 PM, Gavin Flower wrote:
> How about a 'VACUUM AFTER' command (part of the 'BEGIN' transaction syntax?) that would:
>
>  1. only be valid in a transaction
>  2. initiate a vacuum after the current transaction completed
>  3. defer any vacuum triggered due to other criteria
>
> If the transaction was rolled back: then if there was a pending vacuum, due to other reasons, it would then be
actioned.
>
> On normal transaction completion, then if there was a pending vacuum it would be combined with the one in the
transaction.
>
> Still would need some method of ensuring any pending vacuum was done if the transaction hung, or took too long.

I *really* like the idea of BEGIN VACUUM AFTER, but I suspect it would be of very limited usefulness if it didn't
accountfor currently running transactions.
 

I'm thinking we add a vacuum_after_xid field somewhere (pg_class), and instead of attempting to vacuum inside the
backendat commit time the transaction would set that field to it's XID unless the field already had a newer XID in it.
 

autovac would then add all tables where vacuum_after_xid < the oldest running transaction to it's priority list.
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net