Re: Poor memory context performance in large hash joins

On 2017-02-24 15:12:37 -0800, Andres Freund wrote:
> On 2017-02-24 18:04:18 -0500, Tom Lane wrote:
> > Concretely, something like the attached. This passes regression tests
> > but I've not pushed on it any harder than that.
>
> Heh, I'd just gotten something that didn't immediately crash anymore ;)
>
> Running your patch against Jeff's test-case, verified before that I
> could easily reproduce the O(N^2) cost.

Oh, that didn't take as long as I was afraid (optimized/non-assert build):

postgres[26268][1]=# SET work_mem = '13GB';
SET
Time: 2.591 ms
postgres[26268][1]=# select count(*) from foobar2 where not exists (select 1 from foobar t where t.titleid=foobar2.titleid);
┌───────┐
│ count │
├───────┤
│ 0 │
└───────┘
(1 row)

Time: 268043.710 ms (04:28.044)

Profile:
13.49% postgres [.] ExecHashTableInsert
11.16% postgres [.] BufFileRead
9.20% postgres [.] ExecHashIncreaseNumBatches
9.19% libc-2.24.so [.] __memmove_avx_unaligned_erms
6.74% postgres [.] dense_alloc.isra.0
5.53% postgres [.] ExecStoreMinimalTuple
5.14% postgres [.] ExecHashJoinGetSavedTuple.isra.0
4.68% postgres [.] AllocSetAlloc
4.12% postgres [.] AllocSetFree
2.31% postgres [.] palloc
2.06% [kernel] [k] free_pcppages_bulk

I'd previously run the test for 30min, with something like 99% of the
profile being in AllocSetFree().

- Andres