On 11/01/2010 08:40 PM, Patrick Ben Koetter wrote:
Jeroen,
thanks for the detailed answer. Please read my annotations below.
* Jeroen Geilman<[email protected]>:
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.
Agreed. I doubt that too, but I don't know a better approach to prove that
except for trying and measuring.
You're obviously free to do that - but as Victor said, postfix doesn't
support preparing statements, so you'd have to hack the driver :)
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.
So the best approach is to ensure all tables can be loaded into memory i.e.
provide enough $work_mem in pgSQL?
Even the indexes would be enough. It depends on how big your dataset is.
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.
Do I understand you correctly? Are you saying the potential gain is not worth
the effort?
I am saying exactly what I am saying ;)
Given that A. postfix does not support preparing the select_query, and
B. indexing properly will provide much bigger gains than any other
measure (orders of magnitude bigger gains), and C. the manual suggests
that using prepared statements is much less beneficial for simple
queries, odds are that it's not going to be worth the effort.
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.
Thus.
--
J.
