Re: Setting visibility map in VACUUM's second phase

On Fri, Dec 7, 2012 at 12:05 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Dec 6, 2012 at 1:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think taking a second whack at setting the visibility bit is a fine
>> idea, but let's drop all the rest of this premature optimization.
>
> +1.
>
> If there's any optimization needed here, we should try to do it by
> remembering relevant details from the first vacuum pass in
> backend-private memory, rather than by changing the on-disk format.
>

Yeah, I talked about that approach on the other thread. I thought we
can store the page LSN in the backend private memory for all such
pages and compare that with the current page LSN to know if the page
got an intermediate action to demand a recheck for all-visibility. But
I agree to keep these aggressive optimizations to side for now and
revisit them if necessary.

> One other thought: I'm wondering if we shouldn't try to push the work
> of setting the all-visible bit into heap_page_prune(). That would
> allow HOT pruning to set the bit. For example, consider an
> all-visible page. A tuple is HOT-updated and the page becomes
> not-all-visible. Now the page is pruned, removing the old tuple and
> changing the line pointer to a redirect. Presto, page is all-visible
> again.
>

+1

I had submitted a patch for that way back in 2008 or 2009, but blame
me for not pursuing to the end.
http://archives.postgresql.org/message-id/2e78013d0812042257x175e5a45w5edeaff14f7249ac@mail.gmail.com

Alex Hunsaker had reviewed that patch and confirmed a significant
improvement in the vacuum time. I think the patch needed some rework,
but I dropped the ball or got busy with other things while waiting on
others. If you think its useful to have, I will do the necessary work
and submit for the next commitfest.

Excerpts from Alex's comments:

"The only major difference was with this patch vacuum time (after the
first select after some hot updates) was significantly reduced for my
test case (366ms vs 16494ms).

There was no noticeable (within noise) select or update slow down.

I was able to trigger WARNING: PD_ALL_VISIBLE flag once while running
pgbench but have not be able to re-create it... (should I keep
trying?)"

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee