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 2F6B9D1EBD4 for ; Thu, 6 May 2004 21:33:29 -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 23822-04 for ; Thu, 6 May 2004 21:33:06 -0300 (ADT) Received: from ptb-relay01.plus.net (ptb-relay01.plus.net [212.159.14.212]) by svr1.postgresql.org (Postfix) with ESMTP id B5F84D1F34D for ; Thu, 6 May 2004 21:32:33 -0300 (ADT) Received: from [212.159.87.229] (helo=thelabyrinth.force9.co.uk) by ptb-relay01.plus.net with esmtp (Exim) id 1BLtI4-000B8N-3E; Fri, 07 May 2004 00:32:36 +0000 Received: from ruth.localdomain (ruth.localdomain [192.168.0.1]) by thelabyrinth.force9.co.uk (Postfix) with ESMTP id 8CA3530081; Fri, 7 May 2004 00:33:00 +0100 (BST) Date: Fri, 7 May 2004 01:33:58 +0100 (BST) From: Sam Barnett-Cormack X-X-Sender: sdb@localhost.localdomain To: Gaetano Mendola Cc: pgsql-admin@postgresql.org Subject: Re: cast not IMMUTABLE? In-Reply-To: Message-ID: 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/63 X-Sequence-Number: 13379 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 ? > > > How can I define an index for the query (1) ? The way I have done such queries hs been to create functions, marked immutable, that encapsulate the cast/non-immutable internal function, if I know that it *is* really immutable, at least for my purposes, and use a functional index. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University