Re: Index scan vs. Seq scan on timestamps

On Mon, 6 Dec 2004, Stephen Frost wrote:

> * Stephan Szabo (sszabo(at)megazone(dot)bigpanda(dot)com) wrote:
> > On Mon, 6 Dec 2004, Per Jensen wrote:
> > > select count(*)
> > > from accesslog
> > > where time between (timeofday()::timestamp - INTERVAL '30 d') and
> > > timeofday()::timestamp;
> >
> > Besides the type issue, timeofday() is volatile and thus is not allowed to
> > be turned into a constant in order to do an index scan because it's
> > allowed to return different values for every row of the input.
>
> Is there a way to say "just take the value of this function at the start
> of the transaction and then have it be constant" in a query?

I can't think of a general one unless you make some kind of session
variable functions where the get was stable. In this particular case
now() or CURRENT_TIMESTAMP is a stable at transaction start time value.

Currently you can fake the system out by using a scalar subselect or
writing a wrapper function that lies about volatility, but I don't believe
that those are considered guaranteed to keep working forever.