Re: Avoiding repeated ON COMMIT truncation for temporary tables

On Fri, Mar 11, 2011 at 8:07 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Currently, if you create a temporary table with the ON COMMIT action of
> DELETE ROWS, the table will truncated for every commit, whether there is
> any data in the table or not.
>
> I measured the overhead using this test:
>
>        $ (echo 'CREATE TEMPORARY TABLE TEST2 (x int);'; jot -b 'SELECT 1;'
>        10000) | time psql  test > /dev/null
>                6.93 real         0.93 user         0.78 sys
>        $ (echo 'CREATE TEMPORARY TABLE TEST2 (x int) ON COMMIT DELETE ROWS;';
>        jot -b 'SELECT 1;' 10000) | time psql  test > /dev/null
>                7.93 real         1.02 user         0.72 sys
>
> The overhead measures 14%.  Is there a simple way to avoid the repeated
> truncation overhead of such cases?  Is this a TODO?

We might be able to make PreCommit_on_commit_actions() exit quickly
without doing anything if MyXactAccessedTempRel is false. I haven't
tested that solution and am not 100% confident that it's safe, but if
it is I believe it would address your concern.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company