database performance and query performance question - Mailing list pgsql-performance

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