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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
