Re: Timestamp conversion can't use index

Based on Tom's comments and this email, I am adding this to the TODO
list:

* Add new pg_proc cachable settings to specify whether function can be evaluated only once or once per query


---------------------------------------------------------------------------

> > Someone reported to me that they can't get their queries to use indexes.
> > It turns out this is because timestamp() has pg_proc.proiscachable set
> > to false in many cases.  Date() also has this in some cases.
> 
> Please let me add a reference to this email from Tom Lane:
> 
>     http://fts.postgresql.org/db/mw/msg.html?mid=1041918
> 
> It specifically states:
>     
>     [More complete] reasonable [cachable] definitions would be:
>     
>     1. noncachable: must be called every time; not guaranteed to return same
>     result for same parameters even within a query.  random(), timeofday(),
>     nextval() are examples.
>     
>     2. fully cachable: function guarantees same result for same parameters
>     no matter when invoked.  This setting allows a call with constant
>     parameters to be constant-folded on sight.
>     
>     3. query cachable: function guarantees same result for same parameters
>     within a single query, or more precisely within a single
>     CommandCounterIncrement interval.  This corresponds to the actual
>     behavior of functions that execute SELECTs, and it's sufficiently strong
>     to allow the function result to be used in an indexscan, which is what
>     we really care about.
> 
> Item #2 clearly mentions constant folding, I assume by the optimizer. 
> What has me confused is why constant folding is needed to perform index
> lookups.  Can't the executor call the function and then do the index
> lookup?  Is this just a failing in our executor?  Is there a reason
> #1-type noncachable functions can't use indexes?  Is the timezone
> related here?
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026