> > > 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?
They are doing trying to add one day to a date field:
test=> EXPLAIN SELECT * FROM test WHERE x = DATE('2001-01-01'); NOTICE: QUERY PLAN: Index
ScanUSING i_test ON test (cost=0.00..3.01 ROWS=1 width=208) EXPLAIN test=> EXPLAIN SELECT * FROM
testWHERE x = DATE('2001-01-01') + INTERVAL '1 DAY'; NOTICE: QUERY PLAN: Seq Scan ON test
(cost=0.00..26.00ROWS=5 width=208)
^^^^^^^^
EXPLAIN test=> EXPLAIN SELECT * FROM test WHERE x = DATE('2001-01-01') + 1; NOTICE: QUERY PLAN:
Index Scan USING i_test ON test (cost=0.00..3.01 ROWS=1 width=208) EXPLAIN
Seems it is an operator that returns a timestamp.
>
> 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).
I have added this to the TODO list.
-- 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