Re: 'now' vs now() performance

Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> writes:
> The docs say that 'now' is turned into a constant right away. Is this
> overhead/poor planning simply because 'now' gets converted to a
> constant so much earlier in the process?

Yes. Note the estimated numbers of rows in the different plans. In
general, a one-sided inequality (col > something) will *not* get turned
into an indexscan unless the planner can see that 'something' is close
enough to the end of the range of 'col' that the indexscan will pull
only a reasonably small number of columns. When the 'something' is not
determinable at plan time, the estimated number of rows will be large
enough to discourage an indexscan.

When you're certain that an indexscan is what you want, you can fake out
the planner by formulating the query as a range query with two variable
endpoints; for example

message_timestamp > now() AND
message_timestamp < (now() + '1000 years'::interval)

(adjusting this to 'date' datatype is left as an exercise for the
student). The planner still doesn't know what's going on, but its
guess for a range query is a lot smaller than for an open-interval
query; you should get an indexscan from it.

regards, tom lane