Re: Timestamp conversion can't use index

> 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