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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026