Re: faster and faster!!

bpalmer <bpalmer(at)crimelabs(dot)net> writes:
> - I have a query:
> SELECT stamp
> FROM table
> WHERE
> timestamp > now() - INTERVAL '1 0:00'
> This query takes 13 seconds to run.

It's probably not being indexed (use EXPLAIN to check). The problem
is that "now() - INTERVAL '1 0:00'" is not considered a constant, so
the planner can't use an indexscan.

The usual workaround is to make a function like so:

tgl=# create function ago(interval) returns timestamp as
tgl-# 'select now() - $1' language 'sql' with (iscachable);
CREATE
tgl=# select ago('1 0:00');
ago
---------------------------
2001-10-01 11:31:13.62-04
(1 row)

and then write
WHERE timestamp > ago('1 0:00');

The "iscachable" attribute of the function tells the planner it's okay
to reduce the function call to a constant during planning, and then it
becomes possible to use an indexscan. Strictly speaking, marking this
function iscachable is a lie, but it works just fine in interactive
queries. (You might have trouble with it if you tried to put such
a query into a plpgsql function; you'd probably find that the function
call gets reduced sooner than you want.)

See past discussions of this issue in the archives --- a search for
"iscachable" should turn up some threads. In the long term we probably
need an intermediate concept between "cachable" and "not cachable",
along the lines of "result is constant within a query", so that the
behavior of now() can be described more accurately.

regards, tom lane