On Mon, Mar 14, 2011 at 8:52 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Mar 11, 2011 at 8:07 PM, Bruce Momjian <bruce@momjian.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.
aside: I find 'on commit drop' tables to be quite useless, especially
in plpgsql due to performance issues. it's much better to organize
data around a permanent table organized around txid_current(), with
something swooping along periodically and cleaning it up.
merlin
