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.
I realized timestamp() can be called with 'CURRENT_TIMESTAMP', which of
course is not cachable, but when called with a real date, it seems it
would be cachable. However, I seem to remember that the timezone
setting can effect the output, and therefore it isn't cachable, or
something like that.
While the actual conversion call it very minor, there is code in
backend/optimizer/utils/clauses::simplify_op_or_func() that has:
if (!proiscachable) return NULL;
This prevents index usage for non-cachable functions, as shown below.
The first only does only a date() conversion, the second adds an
interval, which results in a timestamp() conversion. Notice this uses a
sequential scan. The final one avoids timestamp but just adding '1' to
the date value:
test=> EXPLAIN SELECT * FROM test WHERE x = DATE('2001-01-01'); NOTICE: QUERY PLAN: Index Scan USING
i_testON 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
Can someone explain the rational between which timestamp/date calls are
cachable and which are not, and whether the cachablility really relates
to index usage or is this just a problem with our having only one
cachable setting for each function? I would like to understand this so
I can formulate a TODO item to document it.
-- 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