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)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
