It might be worth checking out the pre_prepare module: http://preprepare.projects.postgresql.org/README.html
Cheers, Ken On Mon, Nov 01, 2010 at 08:45:17PM +0100, Jeroen Geilman wrote: > On 11/01/2010 08:40 PM, Patrick Ben Koetter wrote: >> Jeroen, >> >> thanks for the detailed answer. Please read my annotations below. >> >> * Jeroen Geilman<jer...@adaptr.nl>: >> >>> 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. > >
