Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: Tom LaneDate: 2005-05-30 17:15:08
Subject: Re: Postgresql and xeon.
Previous:From: Eric LauzonDate: 2005-05-30 17:02:12
Subject: Re: Postgresql and xeon.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group