I'm having severe performance issues with a conceptually simple

database. The database has one table, containing weather observations.

The table currently has about 13.5 million rows, and is being updated

constantly. (The database is running on a dual 550MHz PIII with 512MB RAM.

I have PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

on RedHat 7.2)

On the whole, queries are of the form:

SELECT ? FROM obs WHERE station = ?

AND valid_time < ? AND valid_time > ?

or:

SELECT ? FROM obs WHERE station IN (?, ?, ...)

AND valid_time < ? AND valid_time > ?

Queries like these are taking around 4 to 5 minutes each, which seems

excessively slow to me (or are my expectations far too optimistic?).

For instance:

SELECT station, air_temp FROM obs

WHERE station = 'EGBB'

AND valid_time > '28/8/03 00:00'

AND valid_time < '28/10/03 00:00'

takes 4 mins 32 secs.

An EXPLAIN of the above query says:

NOTICE: QUERY PLAN:

Index Scan using obs_pkey on obs (cost=0.00..9.01 rows=1 width=20)

A simple "SELECT count(*) from obs" query takes around that sort of time

too.

I have run "vacuumdb --analyze obs", to little effect.

How can I speed this up? Where am I going wrong? Is there a problem with

the table structure, or the indexes? Does the continual updating of the

database (at the rate of somewhere between 1-3 entries per second) cause

problems?

The table and indexes are defined as follows:

Table "obs"

Attribute | Type | Modifier

----------------------------+--------------------------+----------

valid_time | timestamp with time zone |

metar_air_temp | double precision |

relative_humidity | double precision |

pressure_change | double precision |

ceiling | double precision |

metar_dew_point | double precision |

metar_gusts | double precision |

wet_bulb_temperature | double precision |

past_weather | text |

visibility | double precision |

metar_visibility | double precision |

precipitation | double precision |

station | character(10) |

pressure_msl | double precision |

metar_min_temperature_6hr | double precision |

precipitation_period | double precision |

metar_wet_bulb | double precision |

saturation_mixing_ratio | double precision |

metar_pressure | double precision |

metar_sky_cover | text |

dew_point | double precision |

wind_direction | double precision |

actual_time | timestamp with time zone |

gust_speed | double precision |

high_cloud_type | text |

precipitation_24hr | double precision |

metar_precipitation_24hr | double precision |

pressure_tendency | text |

metar_relative_humidity | double precision |

low_cloud_type | text |

metar_max_temperature_6hr | double precision |

middle_cloud_type | text |

air_temp | double precision |

low_and_middle_cloud_cover | text |

metar_wind_dir | double precision |

metar_weather | text |

snow_depth | double precision |

metar_snow_depth | double precision |

min_temp_12hr | double precision |

present_weather | text |

wind_speed | double precision |

snow_cover | text |

metar_wind_speed | double precision |

metar_ceiling | double precision |

max_temp_12hr | double precision |

mixing_ratio | double precision |

pressure_change_3hr | double precision |

total_cloud | integer |

max_temp_24hr | double precision |

min_temp_24hr | double precision |

snow_amount_6hr | double precision |

Indices: obs_pkey,

obs_station,

obs_valid_time

Index "obs_pkey"

Attribute | Type

------------+--------------------------

valid_time | timestamp with time zone

station | character(10)

unique btree

Index "obs_station"

Attribute | Type

-----------+---------------

station | character(10)

btree

Index "obs_valid_time"

Attribute | Type

------------+--------------------------

valid_time | timestamp with time zone

btree

(I suspect the obs_valid_time index is redundant, because of the

obs_pkey index - is that right?)

I'd be grateful for any advice and any clues to help speed this up.

Many thanks,

Dave

---------------------------(end of broadcast)---------------------------

TIP 3: if posting/reading through Usenet, please send an appropriate

subscribe-nomail command to ma*******@postgresql.org so that your

message can get through to the mailing list cleanly