Thread

Commits

Fix failing regression test on x86-32 machines
- 3dbdf86c6364 18.0 landed
Add memory/disk usage for Window aggregate nodes in EXPLAIN.
- 95d6e9af07d2 18.0 cited

pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.

Tatsuo Ishii <ishii@postgresql.org> — 2024-09-17T06:00:38Z

Add memory/disk usage for Window aggregate nodes in EXPLAIN.

This commit is similar to 1eff8279d and expands the idea to Window
aggregate nodes so that users can know how much memory or disk the
tuplestore used.

This commit uses newly introduced tuplestore_get_stats() to inquire this
information and add some additional output in EXPLAIN ANALYZE to
display the information for the Window aggregate node.

Reviewed-by: David Rowley, Ashutosh Bapat, Maxim Orlov, Jian He
Discussion: https://postgr.es/m/20240706.202254.89740021795421286.ishii%40postgresql.org

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/95d6e9af07d2e5af2fdd272e72b5b552bad3ea0a

Modified Files
--------------
src/backend/commands/explain.c        | 68 +++++++++++++++++++++++++----------
src/test/regress/expected/explain.out | 38 ++++++++++++++++++++
src/test/regress/sql/explain.sql      |  9 +++++
3 files changed, 97 insertions(+), 18 deletions(-)

Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.

Christoph Berg <myon@debian.org> — 2025-03-31T15:40:20Z

Re: Tatsuo Ishii
> Add memory/disk usage for Window aggregate nodes in EXPLAIN.

This is failing for PG18 on Debian unstable on 32-bit i386:

******** build/src/test/regress/regression.diffs ********
diff -U3 /build/reproducible-path/postgresql-18-18~~devel.20250331/src/test/regress/expected/explain.out /build/reproducible-path/postgresql-18-18~~devel.20250331/build/src/test/regress/results/explain.out
--- /build/reproducible-path/postgresql-18-18~~devel.20250331/src/test/regress/expected/explain.out	2025-03-31 06:17:21.000000000 +0000
+++ /build/reproducible-path/postgresql-18-18~~devel.20250331/build/src/test/regress/results/explain.out	2025-03-31 15:18:19.914783369 +0000
@@ -792,7 +792,7 @@
 ----------------------------------------------------------------------------------
  WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
    Window: w1 AS ()
-   Storage: Disk  Maximum Storage: NkB
+   Storage: Memory  Maximum Storage: NkB
    ->  Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
  Planning Time: N.N ms
  Execution Time: N.N ms

This is the first build of PG18 on i386, so I cannot say if it broke with that commit or later.

Full log: https://buildd.debian.org/status/fetch.php?pkg=postgresql-18&arch=i386&ver=18%7E%7Edevel.20250331-1&stamp=1743434305&raw=0

Christoph

Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.

David Rowley <dgrowleyml@gmail.com> — 2025-03-31T20:11:13Z

On Tue, 1 Apr 2025 at 04:40, Christoph Berg <myon@debian.org> wrote:
> -   Storage: Disk  Maximum Storage: NkB
> +   Storage: Memory  Maximum Storage: NkB
>     ->  Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)

We'll probably just need to bump that 2000 row count to something a
bit more for 32-bit.

Any chance you could share the output of:

explain (analyze,buffers off,costs off) select sum(n) over() from
generate_series(1,2000) a(n);

Could you maybe also do a binary search for the number of rows where
it goes to disk by adjusting the 2000 up in some increments until the
Storage method is disk? (Not that I think we should set it to the
minimum, but it would be good to not set it too much higher than we
need to)

David

Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.

Christoph Berg <myon@debian.org> — 2025-03-31T20:40:31Z

Re: David Rowley
> Any chance you could share the output of:
> 
> explain (analyze,buffers off,costs off) select sum(n) over() from
> generate_series(1,2000) a(n);

PostgreSQL 18devel on x86-linux, compiled by gcc-14.2.0, 32-bit

=# explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n);
                                        QUERY PLAN                                        
──────────────────────────────────────────────────────────────────────────────────────────
 WindowAgg (actual time=1.248..1.731 rows=2000.00 loops=1)
   Window: w1 AS ()
   Storage: Memory  Maximum Storage: 63kB
   ->  Function Scan on generate_series a (actual time=0.301..0.536 rows=2000.00 loops=1)
 Planning Time: 0.066 ms
 Execution Time: 1.913 ms
(6 rows)


> Could you maybe also do a binary search for the number of rows where
> it goes to disk by adjusting the 2000 up in some increments until the
> Storage method is disk? (Not that I think we should set it to the
> minimum, but it would be good to not set it too much higher than we
> need to)

The test has a `set work_mem = 64;` which I used here:

=# explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2047) a(n);
                                        QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────
 WindowAgg (actual time=1.037..1.429 rows=2047.00 loops=1)
   Window: w1 AS ()
   Storage: Memory  Maximum Storage: 64kB
   ->  Function Scan on generate_series a (actual time=0.262..0.457 rows=2047.00 loops=1)
 Planning Time: 0.058 ms
 Execution Time: 1.594 ms
(6 rows)

=# explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2048) a(n);
                                        QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────
 WindowAgg (actual time=2.073..2.686 rows=2048.00 loops=1)
   Window: w1 AS ()
   Storage: Disk  Maximum Storage: 65kB
   ->  Function Scan on generate_series a (actual time=0.624..1.064 rows=2048.00 loops=1)
 Planning Time: 0.064 ms
 Execution Time: 2.934 ms
(6 rows)

(With the default work_mem, the tipping point is around 149500)

Christoph

Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.

David Rowley <dgrowleyml@gmail.com> — 2025-03-31T22:09:11Z

On Tue, 1 Apr 2025 at 09:40, Christoph Berg <myon@debian.org> wrote:
> =# explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2048) a(n);
>                                         QUERY PLAN
> ──────────────────────────────────────────────────────────────────────────────────────────
>  WindowAgg (actual time=2.073..2.686 rows=2048.00 loops=1)
>    Window: w1 AS ()
>    Storage: Disk  Maximum Storage: 65kB

Thank you for testing that. I've just pushed a patch to bump it up to 2500.

I suspect the buildfarm didn't catch this due to the tuplestore
consuming enough memory in MEMORY_CONTEXT_CHECKING builds.

David

Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.

Tatsuo Ishii <ishii@postgresql.org> — 2025-04-01T00:48:24Z

From: David Rowley <dgrowleyml@gmail.com>
Subject: Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.
Date: Tue, 1 Apr 2025 11:09:11 +1300
Message-ID: <CAApHDvoOHfFYXUryAymxiZjvyvhEt0ueeBOJRUOJWn1W7e3eyA@mail.gmail.com>

> On Tue, 1 Apr 2025 at 09:40, Christoph Berg <myon@debian.org> wrote:
>> =# explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2048) a(n);
>>                                         QUERY PLAN
>> ──────────────────────────────────────────────────────────────────────────────────────────
>>  WindowAgg (actual time=2.073..2.686 rows=2048.00 loops=1)
>>    Window: w1 AS ()
>>    Storage: Disk  Maximum Storage: 65kB
> 
> Thank you for testing that. I've just pushed a patch to bump it up to 2500.
> 
> I suspect the buildfarm didn't catch this due to the tuplestore
> consuming enough memory in MEMORY_CONTEXT_CHECKING builds.

David,
Christoph,

Thank you for fixing this!
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp