From: Tobias Brox <tobias@nordicbet.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: timestamp indexing
Date: Mon, 30 May 2005 19:08:16 +0200
Message-ID: <20050530170816.GA9222@oppetid.no> (raw)
In-Reply-To: <20050530135429.GA78082@winnie.fuhr.org>
References: <20050530091951.GO26100@tobias.exoweb.net>
<20050530135429.GA78082@winnie.fuhr.org>
[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" :)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tobias@nordicbet.com, mike@fuhr.org
Subject: Re: timestamp indexing
In-Reply-To: <20050530170816.GA9222@oppetid.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox