On Fri, Apr 21, 2023 at 9:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Jehan-Guillaume de Rorthais <jgdr@dalibo.com> writes:
> > After catching up with this thread, where pending bugs are listed and discussed,
> > I wonder if the current patches trying to lower the HashJoin memory explosion[1]
> > could be added to the "Older bugs affecting stable branches" list of
> > https://wiki.postgresql.org/wiki/PostgreSQL_16_Open_Items as I think they
> > deserve some discussion/triage for v16?
>
> They do not. That patch is clearly nowhere near ready to commit, and
> even if it was, I don't think we'd consider it post-feature-freeze.
> Any improvement in this space would be a feature, not a bug fix,
> despite anyone's attempts to label it a bug fix.
So, I think this may be a bit harsh. The second patch in the set only
moves hash join batch buffile creation into a more granular memory
context to make it easier to identify instances of this bug (which
causes OOMs). It is missing a parallel hash join implementation and a
bit more review. But it is not changing any behavior.
If using a separate memory context solely for the purpose of accounting
is considered an anti-pattern, we could use some arithmetic like
hash_agg_update_metrics() to calculate how much space is taken up by
these temporary file buffers. Ultimately, either method is a relatively
small change (both LOC and impact AFAICT).
Currently, it isn't possible for a user to understand what is consuming
so much memory when hash join batch file buffers substantially exceed
the size of the actual hashtable. This memory usage is not displayed in
EXPLAIN ANALYZE or anywhere else. I think adding a debugging message
with some advice for is a reasonable concession to the user. This may
not constitute a bug "fix", but I don't really see how this is a
feature.
- Melanie