I am working on the performance problem with the database application.
I've got several PLpgSQL functions that are doing bulk processing of the data.
They are calling some other functions in the loop which, in their own turn, calling third level functions. These, third level functions use some static data.
It is not constant but for for the particular installation it practically never changes. It is currently stored in couple static tables which are used in joins by queries. If I can eliminate these joins, I can shave off 150-170 ms for every query execution which adds up to 2-2.5 minutes for the top level.

If someone know a way to somehow reads this static data into a set of environment variables which can later be used by all underlying functions without passing them in as parameters (there are too many to pass),
please speak up. Your input will be greatly appreciated.

You can define variables in postgresql.conf

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Customized Options

In postgresql.conf you could define: This can then be selected inside a SQL statement:Setting the variable classes in postgresql.conf requires a server restart though

Hm-m-m, How can I use it in a query, though?
For example right now I have a query that selects events that occurred within a test period of the specified timestamp:

Code:

SELECT e.* FROM events e, metric_parameters p
WHERE e.event_ts BETWEEN $1 - p.value::INTERVAL AND $1 + p.value::INTERVAL
AND p.key = 'test_period';

If I rewrite the query this way:

Code:

SELECT e.* FROM events e
WHERE e.event_ts BETWEEN $1 - INTERVAL '15 minute' AND $1 + INTERVAL '15 minute';

I am gaining 25% on performance.

I don't understand your question. I was showing an example using it in a query.

Did you try casting the variable?

Thank you, it works.