Re: [PERFORM] encouraging index-only scans

<div class="moz-cite-prefix">On 06/09/13 13:10, Robert Haas wrote:<br /></div><blockquote
cite="mid:CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com"type="cite"><pre wrap="">On Thu, Sep 5,
2013at 8:14 PM, Bruce Momjian <a class="moz-txt-link-rfc2396E"
href="mailto:bruce@momjian.us"><bruce@momjian.us></a>wrote:
 
</pre><blockquote type="cite"><pre wrap="">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)
</pre></blockquote><pre wrap="">
Analyze doesn't set visibility-map bits.  It only updates statistics
about how many are set.

</pre><blockquote type="cite"><pre wrap="">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.
</pre></blockquote><pre wrap="">
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.

</pre></blockquote><font size="-1">How about a 'VACUUM AFTER' command</font> (part of the 'BEGIN' transaction syntax?)
thatwould:<br /><ol><li>only be valid in a transaction<li>initiate a vacuum after the current transaction
completed<li>deferany vacuum triggered due to other criteria<br /></ol> If the transaction was rolled back: then if
therewas a pending vacuum, due to other reasons, it would then be actioned.<br /><br /> On normal transaction
completion,then if there was a pending vacuum it would be combined with the one in the transaction.<br /><br /> Still
wouldneed some method of ensuring any pending vacuum was done if the transaction hung, or took too long.<br /><br /><br
/>Cheers,<br /> Gavin<br />