From: | Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> |
---|---|
To: | Dave Weaver <davew(at)wsieurope(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: slow query performance |
Date: | 2003-10-31 10:31:30 |
Message-ID: | 3FA23A02.8010008@paradise.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dave,
Apologies if this has been suggested before, but maybe :
- interchanging the key order for the "obs_pkey" index and
- clustering the "obs" table on "station"
might make these queries go a bit better?
Alternatively if maintaining a cluster on station is infeasable, you
could consider a collection of partial indexes on valid_time for each
station:
create index obs_valid_time _stat1 on obs(valid_time) where station =
'station 1';
(etc for each station)...
regards
Mark
Dave Weaver wrote:
>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 > ?
>
>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)
>
> Index "obs_pkey"
> Attribute | Type
>------------+--------------------------
> valid_time | timestamp with time zone
> station | character(10)
>unique btree
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | "Miquel van Smoorenburg" | 2003-10-31 10:51:18 | Re: SCSI vs. IDE performance test |
Previous Message | Mark Kirkwood | 2003-10-31 09:55:33 | ATA disks and RAID controllers for database servers |