On Mon, Jun 19, 2017 at 4:35 PM, Andres Freund <andres@xxxxxxxxxxx> wrote: >> 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. Speaking of nestloop join, and on a similar note, we could do some caching on the inner side of a nestloop join. We already track if the outer side access path of a nestloop join preserves sort order within the optimizer. It might not be that hard to teach the optimizer to generate a plan where, when we know that this has happened, and we know that the outer side is not unique, the final plan hints to the executor to opportunistically cache every lookup on the inner side. 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. -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
