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:
>
> 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 Scan USING i_test ON test (cost=0.00..3.01 ROWS=1 width=208)

EXPLAIN
test=> EXPLAIN SELECT * FROM test WHERE x = DATE('2001-01-01') +
INTERVAL '1 DAY';
NOTICE: QUERY PLAN:

Seq Scan ON test (cost=0.00..26.00 ROWS=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(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