On Sat, 22 May 2021 at 02:00, David Christensen
<david.christensen@crunchydata.com> wrote:
> I can confirm that this fixes the issue in our case (both in the actual query and in the minimal reproduction case).
Thank you for checking that.
I looked at the patch again and realised that if we don't make the
result cache singlerow == true with a unique join that since Nested
Loop just skips to the next outer tuple after it matches to an inner
tuple, that the only chance we'll get to mark a cache entry as
complete will be for outer tuples that have no matching inner tuple.
That's the only time we'll run the inner scan to completion with
unique joins. That would mean that we'd never get any cache hits for
sets of parameters that do have some matching inner rows. Remember a
cache hit is can only use the cache entry if the entry is marked as
complete. Otherwise, there might be missing tuples. Such a scenario
might be common with ANTI joins, but since we don't currently detect
unique joins for those, that's a case we'll never hit.
I ended up pushing a patch that just does not consider a Result Cache
path for unique joins where the parameterisation is not the entire
join condition. That'll mean the plan will change in your test case
when beta2 comes out.
David