Re: Experimenting with hash join prefetch

Hi,

I thought it might be interesting to revive this thread because the
improvements i saw from Thomas’s work, and even just simple prefetching of
bucket headers for the probe phase in-memory (to see the effect of
prefetching), are still showing nice improvements. Here are some results
for simple prefetching in probe phase only, on Thomas's last benchmark
query (in-memory self join):
*Task clock*: -25.6%
*Page faults*: -21.46%
*Cycles*: -17.39%
*L1 dcache loads: *-13.78%
*L1 dcache load misses*: -30.1%
*LLC loads*: -36.7%
*LLC load misses*: -55.1%
*dTLB** loads*: -13.77%
*dTLB Misses: +*0.5%
*Cache references: *-9.5%
*Cache misses*: -7.9%
*IPC*: -6.4%

So, I thought it might be worth relooking at this, even if we avoid major
architectural changes in the hash join executor required by more advanced
techniques. Though it will require a lot of perf benchmarking to prove the
performance improvements, i think its doable to prove or *opposite* what we
can find with minimal architectural changes.

Also, about the Linux experience, it was for lists (pointer chasing)
prefetching (see linux thread <https://lwn.net/Articles/444346/>), which
was happening on Intel with prefetch(null) in the case of doing list
prefetching on short-sized lists, hitting the end of the list very often
(like chained hash tables). This is still noticeable in Postgres if we try
to do prefetching on intra-bucket scan, performance is relatively the same
or even worse.

Thoughts?