121+ million record table perf problems - Mailing list pgsql-performance

I need some help on recommendations to solve a perf problem.

I've got a table with ~121 million records in it.  Select count on it
currently takes ~45 minutes, and an update to the table to set a value on
one of the columns I finally killed after it ran 17 hours and had still
not completed.  Queries into the table are butt slow, and

System:   SUSE LINUX 10.0 (X86-64)
Postgresql:   PostgreSQL 8.2.1
Index type:   btree

A select count took ~48 minutes before I made some changes to the
postgresql.conf, going from default values to these:
shared_buffers = 24MB
work_mem = 256MB
maintenance_work_mem = 512MB
random_page_cost = 100
stats_start_collector = off
stats_row_level = off

As a test I am trying to do an update on state using the following queries:
update res set state=5001;
select count(resid) from res;

The update query that started this all I had to kill after 17hours.  It
should have updated all 121+ million records.  That brought my select
count down to 19 minutes, but still a far cry from acceptable.

The system has 2GB of RAM (more is alreads on order), but doesn't seem to
show problems in TOP with running away with RAM.  If anything, I don't
think it's using enough as I only see about 6 processes using 26-27 MB
each) and is running on a single disk (guess I will likely have to at the
minimum go to a RAID1).  Workload will primarily be comprised of queries
against the indicies (thus why so many of them) and updates to a single
record from about 10 clients where that one records will have md5, state,
rval, speed, audit, and date columns updated.  Those updates don't seem to
be a problem, and are generally processed in bulk of 500 to 5000 at a
time.

Here is the schema for the table giving me problems:

CREATE TABLE res
(
   res_id integer NOT NULL DEFAULT nextval('result_id_seq'::regclass),
   res_client_id integer NOT NULL,
   "time" real DEFAULT 0,
   error integer DEFAULT 0,
   md5 character(32) DEFAULT 0,
   res_tc_id integer NOT NULL,
   state smallint DEFAULT 0,
   priority smallint,
   rval integer,
   speed real,
   audit real,
   date timestamp with time zone,
   gold_result_id integer,
   CONSTRAINT result_pkey PRIMARY KEY (res_id),
   CONSTRAINT unique_res UNIQUE (res_client_id, res_tc_id)
)
WITHOUT OIDS;
ALTER TABLE res OWNER TO postgres;

CREATE INDEX index_audit
   ON res
   USING btree
   (audit);

CREATE INDEX index_event
   ON res
   USING btree
   (error);

CREATE INDEX index_priority
   ON res
   USING btree
   (priority);

CREATE INDEX index_rval
   ON res
   USING btree
   (rval);

CREATE INDEX index_speed
   ON res
   USING btree
   (speed);

CREATE INDEX index_state
   ON res
   USING btree
   (state);

CREATE INDEX index_tc_id
   ON res
   USING btree
   (res_tc_id);

CREATE INDEX index_time
   ON res
   USING btree
   ("time");