Re: Speedup truncations of temporary relation forks

Hi,

On Sun, Jun 1, 2025 at 5:31 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Sun, Jun 1, 2025 at 7:52 AM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > 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;

Thank you very much for your help!
I had also done some performance measurements :
set temp_buffers ='1GB';
BEGIN;
CREATE TEMP TABLE test (id INT) ON COMMIT DELETE ROWS;
INSERT INTO test SELECT generate_series(1, 30000000);
DELETE FROM test WHERE id % 10000000 = 0; -- force postgres to create fsm
ANALYZE test;
COMMIT;

*postgres was running on ramdisk with disabled swapoff*

Thus, we are creating a 1 GB table, so that the local buffers are
completely full and contain only the pages of this table.
To measure the time, I hardcoded calls of GetCurrentTimestamp and
TimestampDifference.

I got ~7% improvement with the patch. Note, that table had only 2
forks - main and fsm (I haven't figured out how to force postgres to
create a visibility map for temp table within the transaction block).

--
Best regards,
Daniil Davydov