Re: slow query performance - Mailing list pgsql-general

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