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

Re: slow query performance

From: Shridhar Daithankar <shridhar_daithankar(at)myrealbox(dot)com>
To: Dave Weaver <davew(at)wsieurope(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: slow query performance
Date: 2003-10-30 09:26:26
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Dave Weaver wrote:
  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:
>   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.

That is no test. Postgresql will always fetch the entire table.
> I have run "vacuumdb --analyze obs", to little effect.

Check for general 
tuning tips and do a vacuum full if table is updated frequently. Also reindex 
the indexes after vacuum. It will take quite some time though.

See if that helps.


In response to


pgsql-general by date

Next:From: Dave WeaverDate: 2003-10-30 10:05:17
Subject: Re: slow query performance
Previous:From: Karsten HilbertDate: 2003-10-30 09:24:34
Subject: Re: sql trees move some element

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