On Mon, Jun 19, 2017 at 4:51 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> This would make only the first lookup for each distinct value on the
> outer side actually do an index scan on the inner side. I can imagine
> the optimization saving certain queries from consuming a lot of memory
> bandwidth, as well as saving them from pinning and locking the same
> buffers repeatedly.
Apparently this is sometimes called block nested loop join, and MySQL
has had it for a while now:
https://en.wikipedia.org/wiki/Block_nested_loop
It doesn't necessarily require that the outer side input be sorted,
because you might end up using a hash table, etc.
--
Peter Geoghegan