Date: Sat, 14 Sep 2024 17:12:01 +0900 (JST)
Message-Id: <20240914.171201.2241093933296787263.ishii@postgresql.org>
To: ashutosh.bapat.oss@gmail.com
Cc: dgrowleyml@gmail.com, orlovmg@gmail.com, jian.universality@gmail.com,
 pgsql-hackers@postgresql.org
Subject: Re: Add memory/disk usage for WindowAgg nodes in EXPLAIN
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <CAExHW5vjV1=oLsX8Hw8+M3Vf2Fuurq8HPUa4Ms8mC5H5DZPjcA@mail.gmail.com>
References: <CAExHW5vRPRLvsZYLmNGcDLkPDWDHXGSWYjox-to-OsCVFETd3w@mail.gmail.com>
	<20240913.183153.727679591062671857.ishii@postgresql.org>
	<CAExHW5vjV1=oLsX8Hw8+M3Vf2Fuurq8HPUa4Ms8mC5H5DZPjcA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20240914.171201.2241093933296787263.ishii%40postgresql.org>
Precedence: bulk

>> > or the case when the last usage fit in memory but an earlier
>> > usage spilled to disk.
>>
>> In my understanding once tuplestore changes the storage type to disk,
>> it never returns to the memory storage type in terms of
>> tuplestore_get_stats.  i.e. once state->usedDisk is set to true, it
>> never goes back to false. So the test case is not necessary.
>> David, am I correct?
> 
> I understand that. I am requesting a testcase to test that same logic.

Maybe something like this? In the example below there are 2
partitions. the first one has 1998 rows and the second one has 2
rows. Assuming that work_mem is 64kB, the first one does not fit the
memory and spills to disk. The second partition fits memory. However as
state->usedDisk remains true, explain shows "Storage: Disk".

test=# explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n));
n                                         QUERY PLAN                             
             
--------------------------------------------------------------------------------
-------------
 WindowAgg (actual time=1.958..473328.589 rows=2000 loops=1)
   Storage: Disk  Maximum Storage: 65kB
   ->  Sort (actual time=1.008..1.277 rows=2000 loops=1)
         Sort Key: ((a.n < 3))
         Sort Method: external merge  Disk: 48kB
         ->  Function Scan on generate_series a (actual time=0.300..0.633 rows=2
000 loops=1)
 Planning Time: 0.069 ms
 Execution Time: 474515.476 ms
(8 rows)

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp