Re: timestamp indexing

From: Tobias Brox <tobias(at)nordicbet(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: Tobias Brox <tobias(at)nordicbet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: timestamp indexing
Date: 2005-05-30 17:08:16
Message-ID: 20050530170816.GA9222@oppetid.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

[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" :)

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-05-30 17:15:08 Re: Postgresql and xeon.
Previous Message Eric Lauzon 2005-05-30 17:02:12 Re: Postgresql and xeon.