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

On Fri, 6 Sept 2024 at 16:21, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
> However, for 10, 2, 1 partitions. I see large performance
> degradation with the patched version: patched is slower than stock
> master in 1.5% (10 partitions), 41% (2 partitions) and 55.7% (1
> partition). See the attached graph.

Thanks for making the adjustments to this.

I don't think there is any need to call tuplestore_updatemax() from
within writetup_heap(). That means having to update the maximum space
used every time a tuple is written to disk. That's a fairly massive
overhead.

Instead, it should be fine to modify tuplestore_updatemax() to set a
flag to true if state->status != TSS_INMEM and then record the disk
space used. That flag won't ever be set to false again.
tuplestore_storage_type_name() should just return "Disk" if the new
disk flag is set, even if state->status == TSS_INMEM. Since the
work_mem size won't change between tuplestore_clear() calls, if we've
once spilt to disk, then we shouldn't care about the memory used for
runs that didn't. Those will always have used less memory.

I did this quickly, but playing around with the attached, I didn't see
any slowdown.

Here's the results I got on my Zen2 AMD machine:

parts master yours mine mine_v_master
10000 5.01 5.12 5.09 99%
1000 4.30 4.25 4.24 101%
100 4.17 4.13 4.12 101%
10 4.16 4.12 4.10 101%
2 4.75 7.64 4.73 100%
1 4.75 8.57 4.73 100%

David