Re: Avoiding second heap scan in VACUUM

On Thu, 2008-05-29 at 09:57 +0530, Pavan Deolasee wrote:
> On Thu, May 29, 2008 at 2:02 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >
> >
> > I'm not happy that the VACUUM waits. It might wait a very long time and
> > cause worse overall performance than the impact of the second scan.
> >
>
> Lets not get too paranoid about the wait. It's a minor detail in the
> whole theory. I would suggest that the benefit of avoiding second scan
> would be huge. Remember, its just not a scan, it also dirties those
> blocks again, forcing them write to disk. Also, if you really have a
> situation where vacuum needs to wait for very long, then you are
> already in trouble. The long running transactions would prevent
> vacuuming many tuples.

Been thinking some more about this. You're right that the second scan
could re-dirty many pages and is probably something to avoid. The main
issue I see is that you don't really know how much work will happen in
the first phase and how much would happen in the second. Avoiding the
second scan might be worth waiting for, it might not. You really have no
idea how many tuples the LRT might effect. There is no "typical" here,
it can vary from one extreme to another. Waiting could be very bad, or
no problem at all. I'd rather keep it as it is than have sometimes
better, sometimes worse behaviour.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support