Re: timestamped archive data index searches

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

Tom> "Stephen Birch" <sgbirch@hotmail.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);

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

I've found this to work:

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

I don't know where I got that trick from, but it works fine.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!