On Wed, Feb 19, 2003 at 11:28:13AM -0500, Tom Lane wrote:
> "Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes:
> > Interestingly this could be used to create a speedy vacuum - that is,
> > create a new table with a temporary name that is invisible to the
> > database (similar to dropped columns), then taking into account the disk
> > space left on the device, pick the last X pages from the old table and
> > write to the new table. Then truncate the file containing the table at
> > point X and repeat until finished. Finally kill the old table and make
> > the new one visible.
>
> And if you crash midway through?
Messy definitly.
> > Why does vacuum bother with reordering rows?
>
> It's designed to be fast when there's not very much data motion required
> (ie, you only need to pull a relatively small number of rows off the end
> to fill in the holes elsewhere).
>
> I have not seen any actual evidence that doing it any other way would be
> faster. Yes, it's reading the source tuples backwards instead of
> forwards, but that's at most a third of the total I/O load (you've also
> got tuple output and WAL writes to think about). It's not clear that
> any kernel read-ahead optimization could get a chance to work anyhow.
Well, consider that it's reading every single page in the table from the end
down to halfway (since every tuple was updated). If you went back in chunks
of 128K then the kernel may get a chance to cache the following blocks. On a
disk, once you are reading a sector, reading the next 128 sectors is
essentially free. The marginal cost of more sectors is almost zero.
It could be argued that the kernel should be noticing that you're scanning
backward and start its read a meg or two before where you asked. But there
are so many levels of cache (both hardware and software) that may have to
play along.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.
