Skip site navigation (1) Skip section navigation (2)

Re: Freebsd vs linux and hardware question

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 (view raw or flat)
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

In response to

Responses

sfpug by date

Next:From: Dror MatalonDate: 2003-09-20 19:02:34
Subject: Re: Freebsd vs linux and hardware question
Previous:From: AdityaDate: 2003-09-19 23:39:50
Subject: Re: Freebsd vs linux and hardware question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group