optimizing vacuum truncation scans

After a large bulk load aborted near the end, I decided to vacuum the main
table so as to not leave a huge chunk of free space in the middle of it,
before re-running the bulk load. This vacuum took a frustratingly long
time, as the backwards scan over the table to truncate the space did not
trigger read-ahead (or read-behind) in the file system. (Which was ext4).

In the hind-sight, the best thing to do would have been to open a psql
session and take an access-share lock on the main table, so that it would
prevent the truncation from occurring. Leaving cleaned, reusable space at
the end of the table to be refilled upon the next (successful) run of the
bulk load. But it would be nice if the system could do some optimization
for me, as hind sight never comes in time.

I did literally the simplest thing I could think of as a proof of concept
patch, to see if it would actually fix things. I just jumped back a
certain number of blocks occasionally and prefetched them forward, then
resumed the regular backward scan. The patch and driving script are
attached.

It was very successful in this test. I've report the fastest time to do
the truncation for each setting of the prefetch size out of about 10 runs
each, after removing a handful of cases where buffer pins prevented it from
doing a full-size truncation. (I used the minimum, because the server has
other duties what would occasionally kick in and cause a spike in time for
a few runs). 0 setting disables this feature.

JJFETCH trunc_time(s) 0 355.78 1 350.5 2 277.61 4 213.9 8 172.79 16
138.87 32 133.02 64 76.25 128 47.63 256 35.28 512 20.44 1024 18.6
2048 15.63 4096 15.34 8192 14.72 16384 13.45 32768 13.13

For context, the forward-scan part of this vacuum took about 80 sec (IQR 75
to 88).

Do we want something like this at all? If so, what would we have to do to
implement it for real? I thought that maybe the back-off amount could
start at one block and double each time, until it hits a maximum. And that
the maximum would be either seg_size, or 1/8 of effective_cache_size,
whichever is lower. (I don't think we would want to prefetch more than a
fraction of effective_cache_size, or else it could have already been
displaced by the time the scan gets back to it).

Also, what would be the best way to drill a hole through bufmgr.c into md.c
so that the prefetch could specify an entire range, rather than looping
over each individual block?

What would have to be done to detect people running on SSD and disable the
feature, if anything?

I'll add this to next commitfest as WIP patch.

Cheers,

Jeff