Re: optimizing selects on time-series data in Pg

Aidtya,

> Here's what I did:
>
> - I did a vacuum
>
> - did a select count(*) for a few different virtualhosts to see how many
> rows were being dealt with
>
> - run explain analyze on each of those select count(*) and noted the
> difference, ie. the planner decided that a select on a virtual host with
> many more rows was better served by sequential scan than index scan

From the look of it, the database is doing the best it can on your hardware
and current tuning setup. I don't think you can speed this up on the SQL
side, unless you implement an aggregate caching scheme.

It's possible that you could move the break point btw. seq scan and index scan
slightly by increasing shared_buffers and effective_cache_size. What are
those variables now? And what's your hardware?

--
Josh Berkus
Aglio Database Solutions
San Francisco