Date: Tue, 18 Apr 2006 14:56:01 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Cris Carampa <cris119@operamail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: index is not used if I include a function that returns current
	time in my query
Message-ID: <20060418195601.GE49405@pervasive.com>
References: <e1l8v1$183$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <e1l8v1$183$1@news.hub.org>
User-Agent: Mutt/1.5.11

Interesting.... what's EXPLAIN ANALYZE show if you SET
enable_seqscan=off; ?

You should also consider upgrading to 8.1...

On Thu, Apr 13, 2006 at 12:25:02PM +0200, Cris Carampa wrote:
> 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,
> 
> -- 
> Cris Carampa (spamto:cris119@operamail.com)
> 
> potevo chiedere come si chiama il vostro cane
> il mio ? un po' di tempo che si chiama Libero
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461