Date: Mon, 31 Mar 2025 22:40:31 +0200
From: Christoph Berg <myon@debian.org>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Tatsuo Ishii <ishii@postgresql.org>, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Add memory/disk usage for Window aggregate nodes in
 EXPLAIN.
Message-ID: <Z-r9v0CUDyLeM-RB@msg.df7cb.de>
References: <E1sqRGQ-001THo-5o@gemulon.postgresql.org>
 <Z-q3ZAM4OhE-4UiI@msg.df7cb.de>
 <CAApHDvoZ4TeT2bSEaN+Nx94R0neBr1mmyDBx0Z5ZQXVUunu7JQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAApHDvoZ4TeT2bSEaN+Nx94R0neBr1mmyDBx0Z5ZQXVUunu7JQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Z-r9v0CUDyLeM-RB%40msg.df7cb.de>
Precedence: bulk

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