Re: Slow count(*) again...

On Wed, Oct 13, 2010 at 07:49, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Neil Whelchel <neil(dot)whelchel(at)gmail(dot)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"