Re: Add memory/disk usage for WindowAgg nodes in EXPLAIN

On Sat, 6 Jul 2024 at 23:23, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
> So I wanted to Add memory/disk usage for WindowAgg. Patch attached.

Thanks for working on that.

> Since WindowAgg node could create multiple tuplestore for each Window
> partition, we need to track each tuplestore storage usage so that the
> maximum storage usage is determined. For this purpose I added new
> fields to the WindowAggState.

I'd recently been looking at the code that recreates the tuplestore
for each partition and thought we could do a bit better. In [1], I
proposed a patch to make this better.

If you based your patch on [1], maybe a better way of doing this is
having tuplestore.c track the maximum space used on disk in an extra
field which is updated with tuplestore_clear(). It's probably ok to
update a new field called maxDiskSpace in tuplestore_clear() if
state->status != TSS_INMEM. If the tuplestore went to disk then an
extra call to BufFileSize() isn't going to be noticeable, even in
cases where we only just went over work_mem. You could then adjust
tuplestore_space_used() to look at maxDiskSpace and return that value
if it's larger than BufFileSize(state->myfile) and state->maxSpace.
You could check if maxDiskSpace == 0 to determine if the tuplestore
has ever gone to disk. tuplestore_storage_type_name() would also need
to check maxDiskSpace and return "Disk" if that's non-zero.

David

[1] https://www.postgresql.org/message-id/CAHoyFK9n-QCXKTUWT_xxtXninSMEv+gbJN66-y6prM3f4WkEHw@mail.gmail.com