Temporary table retains old contents on update eventually causing slow temp file usage. - Mailing list pgsql-performance

Hi,

It would seem that doing any changes on a temp table forces a copy of
the entire contents of the table to be retained in memory/disk. Is
this  happening due to MVCC?  Is there a way to change this behavior?
It could be very useful when you have really huge temp tables that
need to be updated a few times before they can be dropped.

Below is an example of the problem.  I'll create a temp table, insert
600 rows (just a bunch of urls, you can use anything really), then
update the table a few times without actually changing anything.  Of
course this test case really doesn't show the extent of the problem,
because its such a small amount of data involved.  When I have a temp
table of about 150 megs and do more then a few updates on it, it
forces postgresql to use the disk making things really slow.
Originally the entire temp table fit into RAM.

I tried using savepoints and releasing them to see if it would make
any difference and it did not, which isn't unexpected.  Could
pg_relation_size() be incorrect in this case?

Cheers,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com



test=# begin;
BEGIN
test=# create temp table test_urls (u text);
CREATE TABLE
test=# insert into test_urls (u) select url from url limit 600;
INSERT 0 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
             73728
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
            147456
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
            212992
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
            286720
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
            352256
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
            425984
(1 row)