Re: BUG #17844: Memory consumption for memoize node

On Thu, 16 Mar 2023 at 01:12, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> During execution I looked on "avail Mem" in top output on test machine to
> check how much memory process consume. It looked different each time,
> usually hundreds of MB, sometime around 1.5GB (which is even bigger than
> table size).
> I was able to trigger OOM killer with this query and bigger test_json table
> with similar data.

Thank you for raising this and for your effort with the script to reproduce it.

> I'm wondering:
> 1) Is it a known bug ? Does it relate to json parsing somehow ?

It was unknown prior to this report. It's not related to json parsing.
It seems to be related to a bug in nodeMemoize.c where we're
evaluating the cache key expressions in the ExecutorState context. We
should really be in a more temporary context that gets reset early in
cache_lookup() before the call to prepare_probe_slot(). I'll need to
look in a bit more detail about what that context actually should be.

Another thing that came to mind is that we don't track the memory for
the cache key. So that could account for some additional memory usage
with Memoize. I have a patch locally to fix that. Likely that would be
a master-only fix, however. I doubt that's accounting for much of the
extra memory you're reporting anyway. In hindsight, we should be
tracking that, but I think at the time I was writing this code, I had
thoughts that it wasn't much memory compared to storing the cached
tuples. I now think differently.

It may be a few more days before any patch appears here.

David

> 2) Is it possible to show such memory consumption in explain (analyze,
> buffers) output for easier troubleshooting ?
>
> --
> Thanks,
> Alexey Ermakov
>