X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CA7F452806 for ; Mon, 30 May 2005 14:02:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33648-03 for ; Mon, 30 May 2005 17:02:45 +0000 (GMT) Received: from lynet.oppetid.no (oppetid.no [80.65.51.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5F46052809 for ; Mon, 30 May 2005 14:02:44 -0300 (ADT) Received: by lynet.oppetid.no (Postfix, from userid 1019) id 89D6111A2AA; Mon, 30 May 2005 19:08:16 +0200 (CEST) Date: Mon, 30 May 2005 19:08:16 +0200 From: Tobias Brox To: Michael Fuhr Cc: Tobias Brox , pgsql-performance@postgresql.org Subject: Re: timestamp indexing Message-ID: <20050530170816.GA9222@oppetid.no> References: <20050530091951.GO26100@tobias.exoweb.net> <20050530135429.GA78082@winnie.fuhr.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050530135429.GA78082@winnie.fuhr.org> User-Agent: Mutt/1.3.28i Organization: Group Nordicbet X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/440 X-Sequence-Number: 12604 [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" :)