Stephen Frost <sfr...@snowman.net> writes: > That assumption is certainly something I feel we should consider a valid > and important use-case. I'd think a lot of the time your typical website > is going to be using a dedicated pooler for connections and a dedicated > database where having those queries cache'd would be good.
Using pgbouncer without setting server_reset_query is possible and allow to reuser prepared queries. I abuse the feature in some environment where prepare takes ~42ms and execute 5ms, as all the data is in RAM. > I recall seeing a module that even set things up so you feed it all the > queries that you're going to run and it plans them all out for you when > you start up the pooler. Been meaning to look into it more, but.. Yeah, for this same project I wanted the application code to stop having to check whether the session given already has the queries prepared. As pgbouncer will take new connections here and there (which is a good idea), you have to check for that. Enters preprepare: http://preprepare.projects.postgresql.org/README.html http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/ http://packages.debian.org/sid/postgresql-8.4-preprepare There's no release yet and the code is still under CVS, but I could see about moving it to github some day. Well, maybe I also should implement support for 9.0. > The whole problem with search_path and role is very frustrating. We've > taken to just hacking things to be dynamic SQL whenever it's > role-specific, but that's a really poor solution. I wonder if it would > be possible to have the function and prepare'd plan caches be key'd off > of the search_path and role too..? So if you change one of those you > end up having to re-plan it, but then that's also cached, etc.. By default pgbouncer maintains a different pool per database and role, so you should be partly covered here. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
