Re: now() and ::date

Ray Ontko <rayo(at)ontko(dot)com> writes:
> I'm having a little trouble understanding the query optimizer
> related to a timestamp with time zone column. I have a table
> called "event" with a not null column called "event_date_time"
> of type "timestamp with time zone" that has been recently analyzed.
> My basic questions are:

> Why does "now()" disqualify use of the index?
> Why does "::date" disqualify use of the index?

The short answer: update to 7.3.1, you'll be much happier.

The long answer: indexscans can only use comparisons against values that
will be constant for the duration of the scan. For example,
select * from foo where x > random();
couldn't be implemented as an indexscan without changing its behavior.
The conceptual model is that random() is recomputed at each row and
compared to that row's value of x, and indeed that's how we have to do
it.

Prior to 7.3, the planner only understood two categories of function:
constant forever ("cachable") and totally unpredictable (not
"cachable"). Any WHERE clause containing an occurrence of the second
category of function is considered un-indexable. Unfortunately, now()
clearly doesn't fall in the first category, so it had to be treated as
the second category.

For 7.3 we've invented a third function category, which is "constant for
any one command" --- and now() is defined in a way that lets it be put
in that category. (To wit: its value doesn't advance inside a
transaction.) So in 7.3 your query is considered indexable:

regression=# explain select count(*) from event
regression-# where event_date_time >= now()::timestamp with time zone - '1 month'::interval
regression-# and event_date_time < now()::timestamp with time zone;
QUERY PLAN

------------------------------------------------------------------------------------------------------
Aggregate (cost=17.13..17.13 rows=1 width=0)
-> Index Scan using event_date_time on event (cost=0.00..17.12 rows=5 width=0)
Index Cond: ((event_date_time >= (now() - '1 mon'::interval)) AND (event_date_time < now()))
(3 rows)

The ::date case turns out to be the same issue, in a more subtle guise.
When you write the query like that, you end up invoking the
date-to-timestamp-with-time-zone conversion function. This function's
results depend on the TimeZone system parameter, and therefore it
doesn't fall in the "constant forever" category either. Before 7.3,
we couldn't index it. In 7.3 you can see exactly what's going on:

regression=# explain select count(*) from event
regression-# where event_date_time >= '2002-12-25'::date - '1 month'::interval
regression-# and event_date_time < '2002-12-25'::date;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.12..17.12 rows=1 width=0)
-> Index Scan using event_date_time on event (cost=0.00..17.11 rows=5 width=0)
Index Cond: ((event_date_time >= ('2002-11-25 00:00:00'::timestamp without time zone)::timestamp with time zone) AND (event_date_time < ('2002-12-25'::date)::timestamp with time zone))
(3 rows)

Notice how the computation '2002-12-25'::date - '1 month'::interval ---
which is interpreted as "promote date to timestamp without time zone
and do timestamp without time zone minus interval" --- is folded to a
constant, but the final conversion to timestamp with time zone is left
to be done at runtime. The foldable part isn't dependent on TimeZone
and so it's in the "constant forever" category, letting the planner
reduce it on sight.

If you don't want to update to 7.3 right now, there are tricks you can
play to fool the planner into treating these queries as indexable.
Basically you stick the noncachable computations inside a user-defined
function that you mark cachable (which is a lie, but you can get away
with it if you are careful about how you use the function). See the
PG list archives for previous discussions of this issue.

I'd recommend updating, instead...

regards, tom lane