X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EF826D1F768 for ; Sat, 8 May 2004 20:34:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 19423-05 for ; Sat, 8 May 2004 20:33:51 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id CB044D1ED75 for ; Sat, 8 May 2004 20:33:48 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 8D0B2356F3; Sat, 8 May 2004 16:33:51 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 8BA8A356EE; Sat, 8 May 2004 16:33:51 -0700 (PDT) Date: Sat, 8 May 2004 16:33:51 -0700 (PDT) From: Stephan Szabo To: Gaetano Mendola Cc: pgsql-admin@postgresql.org Subject: Re: cast not IMMUTABLE? In-Reply-To: Message-ID: <20040508162937.S66447@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/73 X-Sequence-Number: 13389 On Fri, 7 May 2004, Gaetano Mendola wrote: > Hi all, > I have a table with ~ 3e+6 rows on it. > > I do select on this table in this way: > > > (1) select * from user_logs where login_time::date = now()::date; > > > consider that login_time is a TIMESTAMPTZ with an index on it. > > If I use the select in this way: > > select * from user_logs where login_time = now(); > > the the index is used. > > I'm trying to use define and index in order to help the query (1): > > > test# create index idx on user_logs ( (login_time::date) ); > ERROR: functions in index expression must be marked IMMUTABLE > > > why that cast is not considered IMMUTABLE ? I'd think the conversion of a timestamptz -> date would be dependent on timezone which would make it not immutable.