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 40321529D1 for ; Tue, 28 Jun 2005 03:35:38 -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 37787-08 for ; Tue, 28 Jun 2005 06:35:36 +0000 (GMT) Received: from sccmmhc91.asp.att.net (sccmmhc91.asp.att.net [204.127.203.211]) by svr1.postgresql.org (Postfix) with ESMTP id 85BEA5294D for ; Tue, 28 Jun 2005 03:35:35 -0300 (ADT) Received: from juju.arbash-meinel.com ([12.214.18.81]) by sccmmhc91.asp.att.net (sccmmhc91) with ESMTP id <20050628063512m9100nghhie>; Tue, 28 Jun 2005 06:35:32 +0000 Received: by juju.arbash-meinel.com (Postfix, from userid 505) id 35DE95606A; Tue, 28 Jun 2005 01:35:12 -0500 (CDT) Received: from [192.168.1.11] (mail [192.168.1.1]) by juju.arbash-meinel.com (Postfix) with ESMTP id 10B4D55FC7; Tue, 28 Jun 2005 01:35:08 -0500 (CDT) Message-ID: <42C0EF9B.3070308@arbash-meinel.com> Date: Tue, 28 Jun 2005 01:35:07 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tobias Brox Cc: kjelle@bingon.no, pgsql-performance@postgresql.org Subject: Re: Too slow querying a table of 15 million records References: <19310.193.213.27.25.1119378838.squirrel@webmail.s7.itpays.net> <20050628060333.GH7652@tobias.exoweb.net> In-Reply-To: <20050628060333.GH7652@tobias.exoweb.net> X-Enigmail-Version: 0.92.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig76D67AF24F1A8A72FBCD848C" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.055 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/545 X-Sequence-Number: 13182 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig76D67AF24F1A8A72FBCD848C Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tobias Brox wrote: >[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. > > > The grandparent was a mailing list double send. Notice the date is 1 week ago. It has already been answered (though your answer is still correct). John =:-> --------------enig76D67AF24F1A8A72FBCD848C Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCwO+bJdeBCYSNAAMRAi1CAKDWsx+D5csdT6oEtuRyeuTB/fhsqACfbGto JR4yWVJ4QQmfzHOfqqns+q0= =trN6 -----END PGP SIGNATURE----- --------------enig76D67AF24F1A8A72FBCD848C--