Re: Speedup truncations of temporary relation forks

On Sun, Jun 1, 2025 at 7:52 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Sat, May 31, 2025 at 01:26:14PM +0700, Daniil Davydov wrote:
> > Not yet. I proceed from the assumption that if the temp_buffers
> > parameter is set to a large value (some users set it to more than a
> > gigabyte), then the vast majority of time is spent iterating through
> > the local buffers.
> > Thus, if we reduce the number of iterations from N to (for example)
> > N/10, we can get a 10x increase in performance. Of course, this is a
> > super rough assumption, but I think you understand my point.
> > In the near future I will prepare a patch for the idea above and try
> > to do some measurements. If there is a significant difference, I will
> > definitely let you know.
> >
> > Anyway, first I suggest committing the current patch.
>
> I doubt that it would be a good idea to apply a patch "just" because
> it looks like a good idea.  It is important to prove that something is
> a good idea first.

I think it makes sense to do the optimization for temporary tables as
well, I tried testing with the below test case[1] and I can see ~18%
improvement with the patch.

On head it is taking ~78 ms to truncate whereas with patch it is just
taking 66ms.

[1]
set temp_buffers ='8GB';
show temp_buffers;
BEGIN;
CREATE TEMPORARY TABLE test(a int, b varchar);
INSERT INTO test select i, repeat('a', 100) from
generate_series(1,1000000) as i;
ANALYZE ;
select relpages from pg_class where relname='test';
TRUNCATE TABLE test;
ROLLBACK;

--
Regards,
Dilip Kumar
Google