Message-ID: <443E66F5.2010100@crpa.it>
Date: Thu, 13 Apr 2006 16:57:57 +0200
From: Cristian Veronesi <c.veronesi@crpa.it>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040114
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: index is not used if I include a function that returns current time
	in my query
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hello, postgresql 7.4.8 on SuSE Linux here.

I have a table called DMO with a column called ORA_RIF defined as
"timestamp without time zone" ;

I created an index on this table based on this column only.

If I run a query against a text literal the index is used:

> explain select * from dmo where ora_rif>'2006-01-01';
                                   QUERY PLAN
-----------------------------------------------------------------------------------------
  Index Scan using dmo_ndx02 on dmo  (cost=0.00..1183.23 rows=736 width=156)
    Index Cond: (ora_rif > '2006-01-01 00:00:00'::timestamp without time
zone)

If I try to use a function that returns the current time instead, a
sequential scan is always performed:

> explain select * from dmo where ora_rif>localtimestamp;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Seq Scan on dmo  (cost=0.00..1008253.22 rows=2703928 width=156)
    Filter: (ora_rif > ('now'::text)::timestamp(6) without time zone)

> explain select * from dmo where ora_rif>localtimestamp::timestamp 
without time zone;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Seq Scan on dmo  (cost=0.00..1008253.22 rows=2703928 width=156)
    Filter: (ora_rif > ('now'::text)::timestamp(6) without time zone)

... etc. ...

(tried with all datetime functions with and without cast)

I even tried to write a function that explicitly returns a "timestamp
without time zone" value:

create or replace function f () returns timestamp without time zone
as '
declare
   x timestamp without time zone ;
begin
   x := ''2006-01-01 00:00:00'';
   return x ;
end ;
' language plpgsql ;

But the result is the same:

> explain select * from dmo ora_rif>f();
                                  QUERY PLAN
-----------------------------------------------------------------------------
  Seq Scan on dmo  (cost=0.00..987973.76 rows=2703928 width=156)
    Filter: (ora_rif > f())

Any suggestion?

Kind regards,

-- 
Cristian Veronesi - C.R.P.A. S.p.A. - Reggio Emilia, Italy

The first thing you need to learn about databases is that
they are not just a fancy file system for storing data.