On Wed, Oct 13, 2010 at 07:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Neil Whelchel <neil.whelchel@gmail.com> writes:
> I concur with Mark's question about whether your UPDATE pushed the table
> size across the limit of what would fit in RAM.
Yeah, you said you have ~2GB of ram, just counting the bytes and the
number of rows (not including padding or overhead) puts you around
~670MB. Some quick testing here on a 64 bit box :
=> create table log (batch_id int, t_stamp timestamp without time zone
not null default now(), raw_data numeric, data_value numeric,
data_value_delta numeric, journal_value numeric, journal_data numeric,
machine_id integer not null, group_number integer) with oids;
CREATE TABLE
Time: 34.310 ms
=> insert into log (batch_id, data_value, data_value_delta,
journal_value, journal_data, group_number, machine_id, raw_data)
select 1, 1, 1, 1, 1, 1, 1, 1 from generate_series(1, 10050886);
INSERT 0 10050886
Time: 32818.529 ms
=> SELECT pg_size_pretty(pg_total_relation_size('log'));
pg_size_pretty
----------------
969 MB
=> update log set raw_data = raw_data+1;
UPDATE 10050886
Time: 65805.741 ms
=> SELECT pg_size_pretty(pg_total_relation_size('log'));
pg_size_pretty
----------------
1939 MB
=> SELECT count(*) from log;
count
----------
10050886
(1 row)
Time: 11181.005 ms
=> SELECT count(*) from log;
count
----------
10050886
(1 row)
Time: 2825.569 ms
This box has ~6GB ram.
BTW did anyone else hear the below in a Valeris voice?
> And the numbers are not all that bad, so let's throw a sabot into the gears:
> crash:~# time psql -U test test -c "UPDATE log SET raw_data=raw_data+1"