database performance and query performance question

Our database has slowed right down. We are not getting any performance from
our biggest table "forecastelement".
The table has 93,218,671 records in it and climbing.
The index is on 4 columns, origianlly it was on 3. I added another to see
if it improve performance. It did not.
Should there be less columns in the index?
How can we improve database performance?
How should I improve my query?

PWFPM_DEV=# \d forecastelement
Table "public.forecastelement"
Column | Type | Modifiers
----------------+-----------------------------+-----------
version | character varying(99) | not null
origin | character varying(10) | not null
timezone | character varying(99) | not null
region_id | character varying(20) | not null
wx_element | character varying(99) | not null
value | character varying(99) | not null
flag | character(3) | not null
units | character varying(99) | not null
valid_time | timestamp without time zone | not null
issue_time | timestamp without time zone | not null
next_forecast | timestamp without time zone | not null
reception_time | timestamp without time zone | not null
Indexes:
"forecastelement_vrwi_idx" btree
(valid_time,region_id.wx_element.issue_time)

explain analyze select DISTINCT ON (valid_time)
to_char(valid_time,'YYYYMMDDHH24MISS') as valid_time,value from
(select valid_time,value,"time"(valid_time) as
hour,reception_time,
issue_time from forecastelement where
valid_time between '2002-09-02 04:00:00' and
'2002-09-07 03:59:59' and region_id = 'PU-REG-WTO-00200'
and wx_element = 'TEMP_VALEUR1' and issue_time between
'2002-09-02 05:00:00' and '2002-09-06 05:00:00'
and origin = 'REGIONAL' and "time"(issue_time) =
'05:00:00'
order by issue_time,reception_time DESC,valid_time) as
foo where
(date(valid_time) = date(issue_time)+1 -1 or
date(valid_time) = date(issue_time)+1 or
(valid_time between '2002-09-07 00:00:00' and '2002-09-07
03:59:59'
and issue_time = '2002-09-06 05:00:00')) order by valid_time
,issue_time DESC;

USING INDEX
"forecastelement_vrwi_idx" btree (valid_time, region_id, wx_element,
issue_time)
Unique (cost=116.75..116.76 rows=1 width=83) (actual
time=9469.088..9470.002 rows=115 loops=1)
-> Sort (cost=116.75..116.75 rows=1 width=83) (actual
time=9469.085..9469.308 rows=194 loops=1)
Sort Key: to_char(valid_time, 'YYYYMMDDHH24MISS'::text), issue_time
-> Subquery Scan foo (cost=116.72..116.74 rows=1 width=83)
(actual time=9465.979..9467.735 rows=194 loops=1)
-> Sort (cost=116.72..116.73 rows=1 width=30) (actual
time=9440.756..9440.981 rows=194 loops=1)
Sort Key: issue_time, reception_time, valid_time
-> Index Scan using forecastelement_vrwi_idx on
forecastelement (cost=0.00..116.71 rows=1 width=30) (actual
time=176.510..9439.470 rows=194 loops=1)
Index Cond: ((valid_time >= '2002-09-02
04:00:00'::timestamp without time zone) AND (valid_time <= '2002-09-07
03:59:59'::timestamp without time zone) AND ((region_id)::text =
'PU-REG-WTO-00200'::text) AND ((wx_element)::text = 'TEMP_VALEUR1'::text)
AND (issue_time >= '2002-09-02 05:00:00'::timestamp without time zone) AND
(issue_time <= '2002-09-06 05:00:00'::timestamp without time zone))
Filter: (((origin)::text = 'REGIONAL'::text) AND
("time"(issue_time) = '05:00:00'::time without time zone) AND
((date(valid_time) = ((date(issue_time) + 1) - 1)) OR (date(valid_time) =
(date(issue_time) + 1)) OR ((valid_time >= '2002-09-07 00:00:00'::timestamp
without time zone) AND (valid_time <= '2002-09-07 03:59:59'::timestamp
without time zone) AND (issue_time = '2002-09-06 05:00:00'::timestamp
without time zone))))
Total runtime: 9470.404 ms

We are running postgresql-7.4-0.5PGDG.i386.rpm .
on a Dell Poweredge 6650.
system
OS RHAS 3.0
cpu 4
memory 3.6 GB
disk 270 GB raid 5

postgresql.conf
max_connections = 64
shared_buffers = 4000
vacuum_mem = 32768
effective_cache_size = 312500
random_page_cost = 2