Re: effective_io_concurrency increasing

On 2017-06-19 15:21:20 -0700, Peter Geoghegan wrote:
> On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > Unfortunately, it is only implemented in very narrow circumstances. You
> > have to be doing bitmap index scans of many widely scattered rows to make it
> > useful. I don't think that this is all that common of a situation. The
> > problem is that at every point in the scan, it has to be possible to know
> > what data block it is going to want N iterations in the future, so you can
> > inform the kernel to pre-fetch it. That is only easy to know for bitmap
> > scans.
>
> I think that you could prefetch in index scans by using the
> pointers/downlinks in the immediate parent page of the leaf page that
> the index scan currently pins. The sibling pointer in the leaf itself
> is no good for this, because there is only one block to prefetch
> available at a time.
>
> I think that this is the way index scan prefetch is normally
> implemented. Index scans will on average have a much more random
> access pattern than what is typical for bitmap heap scans, making this
> optimization more compelling, so hopefully someone will get around to
> this.

I think for index based merge and nestloop joins, it'd be hugely
beneficial to do prefetching on the index, but more importantly on the
heap level. Not entirely trivial to do however.

- Andres