public inbox for [email protected]help / color / mirror / Atom feed
pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. 6+ messages / 3 participants [nested] [flat]
* pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. @ 2024-09-17 06:00 Tatsuo Ishii <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tatsuo Ishii @ 2024-09-17 06:00 UTC (permalink / raw) To: [email protected] 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. @ 2025-03-31 15:40 Christoph Berg <[email protected]> parent: Tatsuo Ishii <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Christoph Berg @ 2025-03-31 15:40 UTC (permalink / raw) To: Tatsuo Ishii <[email protected]>; +Cc: [email protected] 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.202... Christoph ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. @ 2025-03-31 20:11 David Rowley <[email protected]> parent: Christoph Berg <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David Rowley @ 2025-03-31 20:11 UTC (permalink / raw) To: Christoph Berg <[email protected]>; +Cc: Tatsuo Ishii <[email protected]>; [email protected] On Tue, 1 Apr 2025 at 04:40, Christoph Berg <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. @ 2025-03-31 20:40 Christoph Berg <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Christoph Berg @ 2025-03-31 20:40 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Tatsuo Ishii <[email protected]>; [email protected] 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. @ 2025-03-31 22:09 David Rowley <[email protected]> parent: Christoph Berg <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David Rowley @ 2025-03-31 22:09 UTC (permalink / raw) To: Christoph Berg <[email protected]>; +Cc: Tatsuo Ishii <[email protected]>; [email protected] On Tue, 1 Apr 2025 at 09:40, Christoph Berg <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. @ 2025-04-01 00:48 Tatsuo Ishii <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Tatsuo Ishii @ 2025-04-01 00:48 UTC (permalink / raw) To: [email protected]; +Cc: [email protected]; [email protected] From: David Rowley <[email protected]> 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 <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-04-01 00:48 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-17 06:00 pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN. Tatsuo Ishii <[email protected]> 2025-03-31 15:40 ` Christoph Berg <[email protected]> 2025-03-31 20:11 ` David Rowley <[email protected]> 2025-03-31 20:40 ` Christoph Berg <[email protected]> 2025-03-31 22:09 ` David Rowley <[email protected]> 2025-04-01 00:48 ` Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox