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 9035452800 for ; Wed, 22 Jun 2005 05:18: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 52373-08 for ; Wed, 22 Jun 2005 08:18:30 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.203]) by svr1.postgresql.org (Postfix) with ESMTP id CA5BA5285C for ; Wed, 22 Jun 2005 05:18:29 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so176818wra for ; Wed, 22 Jun 2005 01:18:31 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=dmxI9H3rfugbVt21J1625vrAnVbhj90hJTMtGmzXAi/y8WCGAprfuN5veX6baZPWjmAgvJ9J5jPUeDU6NJS1DDOvHzK1kM8RilKJomrrUYdvZcYxSHRpgAKRyZMK0BM0ycK0gqzue+5VuYVCODUQ8rrBjEUVsqHE11sRP0+pi4k= Received: by 10.54.29.41 with SMTP id c41mr304479wrc; Wed, 22 Jun 2005 01:18:30 -0700 (PDT) Received: by 10.54.104.3 with HTTP; Wed, 22 Jun 2005 01:18:30 -0700 (PDT) Message-ID: Date: Wed, 22 Jun 2005 10:18:30 +0200 From: Kjell Tore Fossbakk Reply-To: Kjell Tore Fossbakk To: Tobias Brox Subject: Re: Querying 19million records very slowly Cc: pgsql-performance@postgresql.org In-Reply-To: <20050622080357.GW7839@tobias> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <42B8719A.6040809@refractions.net> <20050622080357.GW7839@tobias> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.226 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/425 X-Sequence-Number: 13062 Appreciate your time, Mr Brox. I'll test the use of current_timestamp, rather than now(). I am not sure if Pg can do a match between a fixed timestamp and a datetime? time > current_timestamp - interval '24 hours', when time is yyyy-mm-dd hh-mm-ss+02, like 2005-06-22 16:00:00+02. If Pg cant do it, and current_time is faster, i could possibly convert the time field in my database to timestamp, and insert all rows as timestamp rather than a timedate. But that is some script to work over 19 mill rows, so I need to know if that will give me any more speed.. Kjell Tore. On 6/22/05, Tobias Brox wrote: > [Kjell Tore Fossbakk - Wed at 09:45:22AM +0200] > > database=3D> explain analyze select count(*) from test where p1=3D53 an= d > > time > now() - interval '24 hours' ; >=20 > Sorry to say that I have not followed the entire thread neither read the > entire email I'm replying to, but I have a quick hint on this one (ref my > earlier thread about timestamp indices) - the postgresql planner will > generally behave smarter when using a fixed timestamp (typically generate= d > by the app server) than logics based on now(). >=20 > One of my colleagues also claimed that he found the usage of > localtimestamp faster than now(). >=20 > --=20 > Tobias Brox, +86-13521622905 > Nordicbet, IT dept >