From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Aditya <aditya(at)grot(dot)org>, sfpug(at)postgresql(dot)org |
Subject: | Re: Freebsd vs linux and hardware question |
Date: | 2003-09-20 00:37:22 |
Message-ID: | 200309191737.22892.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dror Matalon | 2003-09-20 19:02:34 | Re: Freebsd vs linux and hardware question |
Previous Message | Aditya | 2003-09-19 23:39:50 | Re: Freebsd vs linux and hardware question |