Justin Pryzby <pryzby@telsasoft.com> writes:
> On Sat, Apr 20, 2019 at 04:46:03PM -0400, Tom Lane wrote:
>> Maybe we just need to account for the per-batch buffers while estimating
>> the amount of memory used during planning. That would force this case
>> into a mergejoin instead, given that work_mem is set so small.
> Do you mean by adding disable_cost if work_mem is so small that it's estimated
> to be exceeded ?
No, my point is that ExecChooseHashTableSize fails to consider the
I/O buffers at all while estimating hash table size. It's not
immediately obvious how to factor that in, but we should.
If Tomas is right that there's also an underestimate of the number
of rows here, that might not solve Gunther's immediate problem; but
it seems like a clear costing oversight.
There's also the angle that the runtime code acts as though increasing
the number of batches is free, while it clearly isn't when you think
about the I/O buffers. So at some point we should probably stop
increasing the number of batches on the grounds of needing too many
buffers.
regards, tom lane
