On Sun, 14 May 2023 00:10:00 +0200
Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
> On 5/12/23 23:36, Melanie Plageman wrote:
> > Thanks for continuing to work on this.
> >
> > Are you planning to modify what is displayed for memory usage in
> > EXPLAIN ANALYZE?
Yes, I already start to work on this. Tracking spilling memory in
spaceUsed/spacePeak change the current behavior of the serialized HJ because it
will increase the number of batch much faster, so this is a no go for v16.
I'll try to accumulate the total allocated (used+not used) spill context memory
in instrumentation. This is gross, but it avoids to track the spilling memory
in its own structure entry.
> We could do that, but we can do that separately - it's a separate and
> independent improvement, I think.
+1
> Also, do you have a proposal how to change the explain output? In
> principle we already have the number of batches, so people can calculate
> the "peak" amount of memory (assuming they realize what it means).
We could add the batch memory consumption with the number of batches. Eg.:
Buckets: 4096 (originally 4096)
Batches: 32768 (originally 8192) using 256MB
Memory Usage: 192kB
> I think the main problem with adding this info to EXPLAIN is that I'm
> not sure it's very useful in practice. I've only really heard about this
> memory explosion issue when the query dies with OOM or takes forever,
> but EXPLAIN ANALYZE requires the query to complete.
It could be useful to help admins tuning their queries realize that the current
number of batches is consuming much more memory than the join itself.
This could help them fix the issue before OOM happen.
Regards,