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: | 3FA0D942.6040802@myrealbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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:
> 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.
That is no test. Postgresql will always fetch the entire table.
>
> I have run "vacuumdb --analyze obs", to little effect.
Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html 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.
Shridhar
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Weaver | 2003-10-30 10:05:17 | Re: slow query performance |
Previous Message | Karsten Hilbert | 2003-10-30 09:24:34 | Re: sql trees move some element |