On Sun, Nov 10, 2019 at 4:09 PM Thomas Munro <thomas.munro@gmail.com> wrote:
>
> I think I see what's happening: we're running out of hash bits.
>
> > Buckets: 4194304 (originally 4194304) Batches: 32768 (originally 4096) Memory Usage: 344448kB
>
> Here it's using the lower 22 bits for the bucket number, and started
> out using 12 bits for the batch (!), and increased that until it got
> to 15 (!!). After using 22 bits for the bucket, there are only 10
> bits left, so all the tuples go into the lower 1024 batches.
>
> I'm not sure how exactly this leads to wildly varying numbers of
> repartioning cycles (the above-quoted example did it 3 times, the
> version that crashed into MaxAllocSize did it ~10 times).
>
> Besides switching to 64 bit hashes so that we don't run out of
> information (clearly a good idea), what other options do we have? (1)
> We could disable repartitioning (give up on work_mem) after we've run
> out of bits; this will eat more memory than it should. (2) You could
> start stealing bucket bits; this will eat more CPU than it should,
> because you'd effectively have fewer active buckets (tuples will
> concentrated on the bits you didn't steal).
Thanks for working through this!
But now we're at the end of my understanding of how hash tables and
joins are implemented in PG; is there a wiki page or design that might
give me some current design description of how the buckets and batches
work with the hash so I can keep following along?
James