X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 15D12D1B448 for ; Wed, 21 Jan 2004 20:39:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 36558-02 for ; Wed, 21 Jan 2004 16:38:30 -0400 (AST) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by svr1.postgresql.org (Postfix) with ESMTP id D97AAD1D500 for ; Wed, 21 Jan 2004 16:38:27 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 1AjP7M-000127-0X; Wed, 21 Jan 2004 20:38:28 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id BE08517D37; Wed, 21 Jan 2004 20:38:27 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 20DC816459; Wed, 21 Jan 2004 20:38:23 +0000 (GMT) From: Richard Huxton To: josh@agliodbs.com, "Arnau" , Subject: Re: Queries with timestamps Date: Wed, 21 Jan 2004 20:38:22 +0000 User-Agent: KMail/1.5 References: <029701c3e04b$ea608930$3c0aa8c0@iberica.andromeiberica.com> <200401211106.06506.josh@agliodbs.com> In-Reply-To: <200401211106.06506.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401212038.22391.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/213 X-Sequence-Number: 5387 On Wednesday 21 January 2004 19:06, Josh Berkus wrote: > Arnau, > > > As the number of rows grows the time needed to execute this query > > takes longer. What'd I should do improve the performance of this query? > > Tip #1) add an index to the timestamp column > Tip #2) make sure that you VACUUM and ANALYZE regularly > Tip #3) You will get better performance if you pass the "current_date - 1 > month" as a constant from the client instead of in the query. This is a > known issue, expected to be fixed in 7.5. (I think Tip 3 is already fixed in 7.3, or I misunderstand what Josh is saying) Note that this is timestamp-related and not "timestamp with time zone" related. Most of the time you want the latter anyway. If you can use with time zones and drop the cast you might well find the index is being used... EXPLAIN ANALYSE SELECT * FROM log_core WHERE log_ts > CURRENT_DATE - '1 week'::interval; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Index Scan using log_core_ts_idx on log_core (cost=0.00..18.73 rows=239 width=117) (actual time=0.79..0.79 rows=0 loops=1) Index Cond: (log_ts > ((('now'::text)::date - '7 days'::interval))::timestamp with time zone) Total runtime: 1.03 msec (3 rows) It seems to help an accurate estimate of number-of-rows if you put an upper and lower limit in. -- Richard Huxton Archonet Ltd