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