Re: Speedup truncations of temporary relation forks

On Sun, Jun 1, 2025 at 5:51 PM Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Sun, Jun 1, 2025 at 5:31 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Sun, Jun 1, 2025 at 7:52 AM Michael Paquier <michael(at)paquier(dot)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

+1

(I haven't figured out how to force postgres to
> create a visibility map for temp table within the transaction block).

I haven't tested this, but I think if you do bulk copy into a table
which should mark pages all visible and after that if you delete some
tuple from pages logically it should try to update the status to not
all visible in vm?

--
Regards,
Dilip Kumar
Google