Thread: slow query performance
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.
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
email: zen13097 (AT) zen [DOT] co {DOT} uk
Have you tried REINDEX on obs_pkey?
On Wed, Oct 29, 2003 at 09:40:43AM +0000, Dave Weaver wrote:
>
> 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.
>
> 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
> email: zen13097 (AT) zen [DOT] co {DOT} uk
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato