Thread: Avoiding repeated ON COMMIT truncation for temporary tables
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 'SELECT1;' 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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
Robert Haas 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. Added to TODO: Prevent temporary tables created with ON COMMIT DELETE ROWS fromrepeatedly truncating the table on every commit if the tableis alreadyempty * http://archives.postgresql.org/pgsql-hackers/2011-03/msg00842.php -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +