Re: Tuplesort merge pre-reading

On 12/09/16 10:13, Peter Geoghegan wrote:
> On Sun, Sep 11, 2016 at 8:47 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
[...]
> I don't know what the difference is between accessing 10 pages
> randomly, and accessing a random set of 10 single pages sequentially,
> in close succession. As Tom would say, that's above my pay grade. I
> suppose it comes down to how close "close" actually is (but in any
> case, it's all very fudged).

If you select ten pages at random and sort them, then consecutive reads
of the sorted list are more likely to access pages in the same block or
closely adjacent (is my understanding).

eg

blocks: XXXX XXXX XXXX XXXX XXXX
pages: 0 1 2 3 4 5 6 7 8 9

if the ten 'random pages' were selected in the random order:
6 1 7 8 4 2 9 3 0
Consecutive reads would always read new blocks, but the sorted list
would have blocks read sequentially.

In practice, it would be rarely this simple. However, if any of the
random pages where in the same block, then that block would only need to
be fetched once - similarly if 2 of the random pages where in
consecutive blocks, then the two blocks would be logically adjacent
(which means they are likely to be physically close together, but not
guaranteed!).

[...]

Cheers,
Gavin