Re: [PERFORM] encouraging index-only scans

On 06/09/13 13:10, Robert Haas wrote:
> On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Actually, I now realize it is more complex than that, and worse. There
>> are several questions to study to understand when pg_class.relallvisible
>> is updated (which is used to determine if index-only scans are a good
>> optimization choice), and when VM all-visible bits are set so heap pages
>> can be skipped during index-only scans:
>>
>> 1) When are VM bits set:
>> vacuum (non-full)
>> analyze (only some random pages)
> Analyze doesn't set visibility-map bits. It only updates statistics
> about how many are set.
>
>> The calculus we should use to determine when we need to run vacuum has
>> changed with index-only scans, and I am not sure we ever fully addressed
>> this.
> Yeah, we didn't. I think the hard part is figuring out what behavior
> would be best. Counting inserts as well as updates and deletes would
> be a simple approach, but I don't have much confidence in it. My
> experience is that having vacuum or analyze kick in during a bulk-load
> operation is a disaster. We'd kinda like to come up with a way to
> make vacuum run after the bulk load is complete, maybe, but how would
> we identify that time, and there are probably cases where that's not
> right either.
>
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.

Cheers,
Gavin