Re: [PERFORM] encouraging index-only scans

On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian <bruce@momjian.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