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 76A3852867 for ; Tue, 28 Jun 2005 03:04:31 -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 29516-08 for ; Tue, 28 Jun 2005 06:04:28 +0000 (GMT) Received: from mail.nordicbet.com (mail.nordicbet.com [193.69.167.130]) by svr1.postgresql.org (Postfix) with ESMTP id 764EA52838 for ; Tue, 28 Jun 2005 03:04:26 -0300 (ADT) Received: from [221.122.43.98] (helo=tobias.nordicbet.invalid) (Authenticated Sender=tobias@nordicbet.com) by mail.nordicbet.com with esmtpa (Exim 4.50 #1 (Debian)) id 1Dn9BR-0003oc-0K; Tue, 28 Jun 2005 08:03:28 +0200 Received: by tobias.nordicbet.invalid (Postfix, from userid 500) id CDC4FE0E4C; Tue, 28 Jun 2005 14:03:33 +0800 (CST) Date: Tue, 28 Jun 2005 14:03:33 +0800 From: Tobias Brox To: kjelle@bingon.no Cc: pgsql-performance@postgresql.org Subject: Re: Too slow querying a table of 15 million records Message-ID: <20050628060333.GH7652@tobias.exoweb.net> References: <19310.193.213.27.25.1119378838.squirrel@webmail.s7.itpays.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <19310.193.213.27.25.1119378838.squirrel@webmail.s7.itpays.net> 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: 200506/536 X-Sequence-Number: 13173 [kjelle@bingon.no - Tue at 08:33:58PM +0200] > I use FreeBSD 4.11 with PostGreSQL 7.3.8. (...) > database=> explain select date_trunc('hour', time),count(*) as total from > test where p1=53 and time > now() - interval '24 hours' group by > date_trunc order by date_trunc ; I haven't looked through all your email yet, but this phenomena have been up at the list a couple of times. Try replacing "now() - interval '24 hours'" with a fixed time stamp, and see if it helps. pg7 will plan the query without knowledge of what "now() - interval '24 hours'" will compute to. This should be fixed in pg8. -- Tobias Brox, +86-13521622905 Nordicbet, IT dept