Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-30 Thread Simon Riggs
On Fri, 2008-05-30 at 14:22 +0530, Pavan Deolasee wrote: > For large tables, two heap scans along with several additional page > writes doesn't seem to the cost we can afford, especially in IO-bound > application. IMHO a timed wait is not such a bad thing. Note that its > all about VACUUM which i

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-30 Thread Simon Riggs
On Thu, 2008-05-29 at 09:57 +0530, Pavan Deolasee wrote: > On Thu, May 29, 2008 at 2:02 AM, Simon Riggs <[EMAIL PROTECTED]> 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. > > > > Le

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-30 Thread Simon Riggs
On Fri, 2008-05-30 at 14:50 +0530, Pavan Deolasee wrote: > On Fri, May 30, 2008 at 2:41 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > > > What I still > > don't accept is that an unconstrained wait is justifiable. You've just > > said its a minor detail, but that's not the way I see it. It might

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-30 Thread Pavan Deolasee
On Fri, May 30, 2008 at 3:31 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > > Perhaps we can start first scan, check xid after we scan each few > blocks. Once we find the xid is older, then we know the size of the > second scan can be limited to only those blocks already scanned. So the > two endpo

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-30 Thread Pavan Deolasee
On Fri, May 30, 2008 at 1:56 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > > > Been thinking some more about this. You're right that the second scan > could re-dirty many pages and is probably something to avoid. Right. IMHO it would help us a lot. > The main > issue I see is that you don't real

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-30 Thread Pavan Deolasee
On Fri, May 30, 2008 at 2:41 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > What I still > don't accept is that an unconstrained wait is justifiable. You've just > said its a minor detail, but that's not the way I see it. It might be a > second, but it might be an hour or more. > I am suggesting

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Simon Riggs
On Thu, 2008-05-29 at 09:57 +0530, Pavan Deolasee wrote: > On Thu, May 29, 2008 at 2:02 AM, Simon Riggs <[EMAIL PROTECTED]> 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. > > > > Le

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Pavan Deolasee
On Thu, May 29, 2008 at 2:02 AM, Simon Riggs <[EMAIL PROTECTED]> 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 t

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Simon Riggs
On Wed, 2008-05-28 at 16:55 -0400, Gregory Stark wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > So the idea is to have one pass per VACUUM, but make that one pass do > > the first pass of *this* VACUUM and the second pass of the *last* > > VACUUM. > > I think that's exactly the same as

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Gregory Stark
"Simon Riggs" <[EMAIL PROTECTED]> writes: > So the idea is to have one pass per VACUUM, but make that one pass do > the first pass of *this* VACUUM and the second pass of the *last* > VACUUM. I think that's exactly the same as the original suggestion of having HOT pruning do the second pass of th

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Simon Riggs
On Wed, 2008-05-28 at 16:56 +0530, Pavan Deolasee wrote: > 2. It then waits for all the existing transactions to finish to make > sure that everyone can see the change in the pg_class row I'm not happy that the VACUUM waits. It might wait a very long time and cause worse overall performance than

Re: [HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Gregory Stark
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > 1. Before VACUUM starts, it updates the pg_class row of the target > table, noting that VACUUM_IN_PROGRESS for the target table. If I understand correctly nobody would be able to re-use any line-pointers when a vacuum is in progress? I find that a bi

[HACKERS] Avoiding second heap scan in VACUUM

2008-05-28 Thread Pavan Deolasee
Tom brought this up during the PGCon developer meet. After thinking a bit about it, I think it's actually possible to avoid the second heap scan, especially now that we've HOT. If we can remove the second pass, not only would that speed up vacuum, but also reduce lots of redundant read and write IO