On 11/01/2010 07:35 PM, Patrick Ben Koetter wrote:
Out of curiosity I started to play around with Postfix and PostgreSQL.
PostgreSQL recommends "prepared statements" to speed up queries (by ~%20).
From the 8.0 manual:
Prepared statements have the largest performance advantage when a single
session is being used to execute a large number of similar statements.
The performance difference will be particularly significant if the
statements are complex to plan or rewrite, for example, if the query
involves a join of many tables or requires the application of several
rules. *If the statement is relatively simple to plan and rewrite but
relatively expensive to execute, the performance advantage of prepared
statements will be less noticeable.*
It is doubtful whether a simple key lookup query - such as postfix does
- benefits from PSs.
If the postgres database in question is used primarily to lookup postfix
maps, every possible value will be cached in RAM for 99% of the time
anyway - this gives incomparably larger advantages than writing faster
queries.
As I understand it "prepared statements" must be defined once when a DB
session starts and they will be available only to the particular client that
requested the "prepared statement". Any subsequent client connecting will have
to PREPARE a "prepared statement" for itself.
A prepared statement remains in memory during a session, yes.
I see I can get around multiple PREPARE statements if I use the Postfix
proxymap daemon, but how would I send the initial PREPARE query?
That's untrivial, since even a proxymap connection doesn't live forever.
All postfix processes are recycled after a period of time.
If the Pl/pgSQL language allows it, you could write a SP that checks if
the statement is already prepared, and then execute it.
This will have a lot more overhead than the potential gain from
preparing it.
You should have absolutely no delusions about the performance cost of
this extra check - just writing a stored procedure that runs the SELECT
will win every single time.
--
J.
