Re: Freebsd vs linux and hardware question

Aditya,

> the main problem is that the data is a time-series and now() isn't
> immutable under Postgres 7.2 meaning that indices on a timestamp field
> don't work...ie.
>
> select * from weblogs where ts > (now() - interval '7 days');

Yes, but there's an easy way around this ... simply call SELECT (NOW() - '7
days'); from your client software, and pass that as a static timestamp to the
query.

> will always use a sequential scan since now() isn't cacheable. Worse yet,
> there is no "immutable" flag for functions under 7.2 so we can't wrap now()
> in a function marked immutable...(thanks to Stephan Szabo who explained
> this to me a few months ago on sfpug)

True. And you don't want to mark it as ISCACHABLE, since under 7.2 that could
mean getting a value in memory that is minutes or hours old.

> a new database machine would allow us to use a newer version of Postgres
> which could use an index and would avoid having to transfer an entire table
> from the NAS for a sequential scan...(the network interface is a
> bottle-neck in that case)

Good to know .... sometime I'd like to talk to you about the cost/benefit of
using a NAS for Postgres.

--
Josh Berkus
Aglio Database Solutions
San Francisco