Lists: | pgsql-hackers |
---|
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Avoiding repeated ON COMMIT truncation for temporary tables |
Date: | 2011-03-12 01:07:53 |
Message-ID: | 201103120107.p2C17rd22697@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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?
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Avoiding repeated ON COMMIT truncation for temporary tables |
Date: | 2011-03-14 13:52:51 |
Message-ID: | AANLkTi=d+4o9KdsdB2RroEdadfxDOqiF9Sb7zAoKyq-N@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Avoiding repeated ON COMMIT truncation for temporary tables |
Date: | 2011-03-14 16:35:43 |
Message-ID: | AANLkTimgqxj8f4qcFCiRvhMufPL5PvWV=dGUX0zSrJjn@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mon, Mar 14, 2011 at 8:52 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.
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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Avoiding repeated ON COMMIT truncation for temporary tables |
Date: | 2011-03-15 23:42:23 |
Message-ID: | 201103152342.p2FNgNE23737@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Robert Haas wrote:
> 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.
Added to TODO:
Prevent temporary tables created with ON COMMIT DELETE ROWS from
repeatedly truncating the table on every commit if the table is already
empty
* http://archives.postgresql.org/pgsql-hackers/2011-03/msg00842.php
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +