Re: timestamped archive data index searches

"Stephen Birch" <sgbirch(at)hotmail(dot)com> writes:
> I also tried the following, which explain said is also using a sequence scan
> :-(

> SELECT sum(vol) FROM det
> WHERE tstamp > (current_timestamp - '5 seconds'::interval);

This doesn't work (in 7.2 and before) because the planner doesn't think
current_timestamp is a constant. You can get around that with a custom
function that hides the current_timestamp computation and is marked
isCachable --- this is a cheat but works well enough in interactive
queries. (It'd not work inside plpgsql unfortunately.) See past
archived discussions --- searching for isCachable should turn up
examples.

Beginning in 7.3 there will be a finer-grain notion of constant
functions so that the planner can optimize this sort of thing as-is.

regards, tom lane