Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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
