On 10/22/09 12:25 PM, "Kevin Grittner" <kevin.gritt...@wicourts.gov> wrote:

> Kevin Buckham <kbuck...@applocation.net> wrote:
> 
>> Our primary location table is clustered by "reporttime" (bigint).
>> Many of the queries we need to perform are of the nature : "get me
>> all positions from a given device for yesterday".  Similar queries
>> are "get me the most recent 10 positions from a given device".
> 
> Have you looked at table partitioning?  You would then only need to
> cluster the most recent partition or two.  I *seems* like a good fit
> for your application.
> 
> http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html
> 
> -Kevin

Partitioning by time should help a lot here as Kevin says.

Also, you might want to experiment with things like pg_reorg:
http://reorg.projects.postgresql.org/
http://pgfoundry.org/projects/reorg/
http://reorg.projects.postgresql.org/pg_reorg.html

Which is basically an online, optimized cluster or vacuum full.  However it
has several caveats.  I have not used it in production myself, just
experiments with it.


> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to