Huh!!
I just tried this and it didn't work for me, it was very slow. I did an
EXPLAIN and it was back to sequence scan again.
Steve
>From: merlyn@stonehenge.com (Randal L. Schwartz)
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] timestamped archive data index searches
>Date: 20 Jul 2002 12:51:00 -0700
>
> >>>>> "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!
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
