> 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 sameresult for same parameters even within a query. random(), timeofday(),nextval() are
examples.2.fully cachable: function guarantees same result for same parametersno matter when invoked. This setting
allowsa call with constantparameters to be constant-folded on sight.3. query cachable: function guarantees same result
forsame parameterswithin a single query, or more precisely within a singleCommandCounterIncrement interval. This
correspondsto the actualbehavior of functions that execute SELECTs, and it's sufficiently strongto allow the function
resultto be used in an indexscan, which is whatwe 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,
Pennsylvania19026