Skip site navigation (1) Skip section navigation (2)

slow query performance

From: "Dave Weaver" <davew(at)wsieurope(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: slow query performance
Date: 2003-10-30 09:12:32
Message-ID: 200310300912.h9U9CX430770@server-2.twdl.co.uk (view raw or flat)
Thread:
Lists: pgsql-general
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



Responses

pgsql-general by date

Next:From: Gianni MarianiDate: 2003-10-30 09:13:34
Subject: Re: Sending email from PL/pgsql
Previous:From: Pavel StehuleDate: 2003-10-30 09:04:37
Subject: Re: Sending email from PL/pgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group