Re: timestamp indexing

[Michael Fuhr - Mon at 07:54:29AM -0600]
> The message subject is "timestamp indexing" but you don't mention
> whether you have an index on the timestamp column. Do you?

Yes. Sorry for not beeing explicit on that.

> 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).

The actual statement was with 6 or 7 joins and very lengthy. I reduced
it to a simple single join query which still did a sequential scan
rather than an index scan (as expected), and I believe I already did a
follow-up mail including "explain analyze". All "explain analyze" in my
previous mail was run until the resulting execution time had stabilized,
relatively. I will try with "set enable_seqscan off" when I get back to
the office.

> How many records are in the tables you're querying?

Also answered on in my follow-up.

> Are you regularly
> vacuuming and analyzing the database or the individual tables?

Vacuum is run nightly, and I also did a manual "vacuum analyze table" on
the table in question.

> Are
> any of the tables clustered? If so, on what indexes and how often
> are you re-clustering them?

Huh? :)

> What version of PostgreSQL are you using?

Also answered in my follow-up - "not yet pg8" :)