Re: Experimenting with hash join prefetch

On Wed, Apr 10, 2019 at 2:10 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> Here is an example of times for a trivial join on my laptop. Note
> that this is prefetching only the probing phase, not while building
> which should also be possible. I didn't get around to trying deeper
> prefetch pipelines as discussed earlier, but those seemed to produce
> diminishing returns with hardcoded tests like in the earlier message.

It would be interesting to see how this does with moderately-long text
keys, say 32 or 64 byte strings, and with actually-long text keys, say
several kB, and then with gigantic text keys, say several MB. At some
point the key probably gets large enough that computing the hash value
for the next key evicts the current key from the relevant CPU cache,
and if I had to guess, at that point prefetching will become a loser.
But I don't know where that point is. If it turns out for example
that this technique is a winner for pass-by-value datatypes and a
loser for pass-by-reference datatypes, or that it's a winner always,
or some sort of simple rule like that, awesome! But if it turns out
that there's no simple rule that we can use to know whether it wins or
loses, then that might make things a little tricky.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company