Re: optimizing selects on time-series data in Pg

> Sean, Aditya,
>
> > > Add backtick support for postgresql.conf:
> > > I want to be able to do stuff like:
>
> > I remember reading a post that suggested using a (perl) script to generate
> > the postgresql.conf based on on the output of uname -- since we already use
> > a sh script in /usr/local/etc/rc.d, it would be trivial to incorporate
> > it...
>
> I'd still rather have an interactive script, which would allow for
> setting of many variables which cannot be easily set according to
> system info (sort_mem, for example).

Not sure that'd be a problem though if there was basic backtick
support. For example, I think the best approach to go would be to
have this processor set an environment variable called PGTUNEDIR that
contains the path to the directory with PostgreSQL's "tuning scripts"
and have PGTUNEDIR unshifted onto the front of PATH that way the
following scripts are equally valid:

effective_cache_size = `$PGTUNEDIR/effective_cache_size.sh`
effective_cache_size = `effective_cache_size.sh`

With a streaming processor, it's trivial to hookup STDOUT to the newly
created static configuration file making it mind numbingly trivial to
have advanced scripts/programs that are more sophisticated than
/bin/sh create tuning values... basically, anything that's executable
and can send bits to stdout will work. :) Since it's an outside
script, it is trivial to have the effective_cache_size script take
into account the OS via uname, the OS version (sysctl, /proc, etc),
various tuning parameters, etc. Using this program at initdb time
could also be done in favor of at startup, but it's just as easy to
make changing that behavior a pg_ctl switch. Anyway, I think this
plays well into tuning PostgreSQL, 'cause as I've stated... it ain't
a black art.... I just wish PostgreSQL's cost estimates were in terms
of microseconds and not disk fetch units. *sigh*

-sc

--
Sean Chittenden