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. > 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? > >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? p...@rick > 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. > -- All technical questions asked privately will be automatically answered on the list and archived for public access unless privacy is explicitely required and justified. saslfinger (debugging SMTP AUTH): <http://postfix.state-of-mind.de/patrick.koetter/saslfinger/>
