public inbox for [email protected]
help / color / mirror / Atom feedFrom: Christoph Berg <[email protected]>
To: David Rowley <[email protected]>
Cc: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Subject: Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.
Date: Mon, 31 Mar 2025 22:40:31 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvoZ4TeT2bSEaN+Nx94R0neBr1mmyDBx0Z5ZQXVUunu7JQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAApHDvoZ4TeT2bSEaN+Nx94R0neBr1mmyDBx0Z5ZQXVUunu7JQ@mail.gmail.com>
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
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: pgsql: Add memory/disk usage for Window aggregate nodes in EXPLAIN.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox