On 10/22/09 12:25 PM, "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> wrote: > Kevin Buckham <kbuckham@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
