Re: timestamp indexing

On Mon, May 30, 2005 at 05:19:51PM +0800, Tobias Brox wrote:
>
> We have a production database with transaction-style data, in most of the
> tables we have a timestamp attribute "created" telling the creation time of
> the table row. Naturally, this attribute is always increasing.

The message subject is "timestamp indexing" but you don't mention
whether you have an index on the timestamp column. Do you?

> By now we are hitting the limit where the table data does not fit in caches
> anymore. We have a report section where there are constantly requests for
> things like "sum up all transactions for the last two weeks", and those
> requests seem to do a full table scan, even though only the last parts of
> the table is needed - so by now those reports have started to cause lots of
> iowait.

Could you post an example query and its EXPLAIN ANALYZE output? If
the query uses a sequential scan then it might also be useful to see
the EXPLAIN ANALYZE output with enable_seqscan turned off. Since
caching can cause a query to be significantly faster after being run
several times, it might be a good idea to run EXPLAIN ANALYZE three
times and post the output of the last run -- that should put the
queries under comparison on a somewhat equal footing (i.e., we don't
want to be misled about how much faster one query is than another
simply because one query happened to use more cached data on a
particular run).

How many records are in the tables you're querying? Are you regularly
vacuuming and analyzing the database or the individual tables? Are
any of the tables clustered? If so, on what indexes and how often
are you re-clustering them? What version of PostgreSQL are you using?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/