> > 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:
The functions marked as non-cachable are those that are converting from
data types (such as text for which the input may need to be evaluated
for (at least) that transaction.
What kind of queries against constants are they doing that can't use
SQL-standard syntax to avoid a conversion from another data type?
timestamp('stringy time')
may not be good, but I would think that
timestamp 'timey time'
should let the optimizer use indices just fine. It *could* do some more
constant folding if we had a distinction between functions with
indeterminate side effects (e.g. random()) as opposed to those who just
need to be evaluated once per transaction (say, date/time conversion
functions needing the time zone evaluated).
- Thomas