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 C46CDD1B73C for ; Mon, 26 Jul 2004 15:37:52 -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 41088-07 for ; Mon, 26 Jul 2004 18:37:51 +0000 (GMT) Received: from exchange2.netarx.com (unknown [66.147.137.11]) by svr1.postgresql.org (Postfix) with ESMTP id 0DC1CD1B580 for ; Mon, 26 Jul 2004 15:37:47 -0300 (ADT) Received: from [10.0.0.227] ([10.0.0.227]) by exchange2.netarx.com with Microsoft SMTPSVC(5.0.2195.6713); Mon, 26 Jul 2004 14:37:48 -0400 Message-ID: <41054F7C.3020108@netarx.com> Date: Mon, 26 Jul 2004 14:37:48 -0400 From: "Harmon S. Nine" User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.1) Gecko/20040722 Debian/1.4-2 StumbleUpon/1.79 X-Accept-Language: en MIME-Version: 1.0 To: Stephan Szabo , pgsql-performance@postgresql.org Subject: Re: Timestamp-based indexing References: <410519F6.5020806@netarx.com> <20040726085324.E68237@megazone.bigpanda.com> In-Reply-To: <20040726085324.E68237@megazone.bigpanda.com> Content-Type: multipart/alternative; boundary="------------020900070905040507070108" X-OriginalArrivalTime: 26 Jul 2004 18:37:48.0640 (UTC) FILETIME=[A67A8E00:01C4733F] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200407/179 X-Sequence-Number: 7570 This is a multi-part message in MIME format. --------------020900070905040507070108 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit THAT WAS IT!! Thank you very much. Is there a way to change the type of "CURRENT_TIMESTAMP" to "timestamp without time zone" so that casting isn't needed? BTW, isn't this a bug? -- Harmon Stephan Szabo wrote: >On Mon, 26 Jul 2004, Harmon S. Nine wrote: > > > >>However, we can't get the planner to do an timestamp-based index scan. >> >>Anyone know what to do? >> >> > >I'd wonder if the type conversion is causing you problems. >CURRENT_TIMESTAMP - INTERVAL '10 minutes' is a timestamp with time zone >while the column is timestamp without time zone. Casting >CURRENT_TIMESTAMP to timestamp without time zone seemed to make it able to >choose an index scan on 7.4. > > > --------------020900070905040507070108 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit THAT WAS IT!!

Thank you very much.
Is there a way to change the type of "CURRENT_TIMESTAMP" to "timestamp without time zone" so that casting isn't needed?


BTW, isn't this a bug?

-- Harmon


Stephan Szabo wrote:
On Mon, 26 Jul 2004, Harmon S. Nine wrote:

  
However, we can't get the planner to do an timestamp-based index scan.

Anyone know what to do?
    

I'd wonder if the type conversion is causing you problems.
CURRENT_TIMESTAMP - INTERVAL '10 minutes' is a timestamp with time zone
while the column is timestamp without time zone.  Casting
CURRENT_TIMESTAMP to timestamp without time zone seemed to make it able to
choose an index scan on 7.4.

  

--------------020900070905040507070108--