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 7871452814 for ; Mon, 30 May 2005 23:06:47 -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 57849-03 for ; Tue, 31 May 2005 02:06:43 +0000 (GMT) Received: from exobox.exoweb.net (unknown [221.122.43.98]) by svr1.postgresql.org (Postfix) with SMTP id 1B27A52805 for ; Mon, 30 May 2005 23:06:32 -0300 (ADT) Received: (qmail 8451 invoked from network); 31 May 2005 02:06:25 -0000 Received: from exo185.exoweb.net (HELO tobias.nordicbet.invalid) (192.168.0.185) by 0 with SMTP; 31 May 2005 02:06:25 -0000 Received: by tobias.nordicbet.invalid (Postfix, from userid 500) id 5E10A6F76D; Tue, 31 May 2005 10:06:25 +0800 (CST) Date: Tue, 31 May 2005 10:06:25 +0800 From: Tobias Brox To: Tom Lane Cc: Tobias Brox , Michael Fuhr , pgsql-performance@postgresql.org Subject: Re: timestamp indexing Message-ID: <20050531020625.GA26100@tobias.exoweb.net> References: <20050530091951.GO26100@tobias.exoweb.net> <20050530135429.GA78082@winnie.fuhr.org> <20050530170816.GA9222@oppetid.no> <24839.1117475874@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <24839.1117475874@sss.pgh.pa.us> Organization: Group Nordicbet User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/451 X-Sequence-Number: 12615 [Tom Lane - Mon at 01:57:54PM -0400] > Your followup hasn't shown up here yet, I'll check up on that and resend it. > but if the query is written like > WHERE timestampcol >= now() - interval 'something' > then the pre-8.0 planner is not capable of making a good estimate of the > selectivity of the WHERE clause. > One solution is to fold the timestamp > computation to a constant on the client side. I don't think there are any of that in the production; we always make the timestamps on the client side. As to my original problem, I looked up on table clustering on google. Right, for report performance, we store some aggregates in the table which are updated several times. If I've understood it correctly, the row will physically be moved to the tail of the table every time the attribute is updated. I understand that it may make sense to do a full table scan if a random 10% of the rows should be selected. Forcing the usage of the index caused a tiny improvement of performance, but only after running it some few times to be sure the index got buffered :-) -- Tobias Brox, Beijing