Hi, Dimitri <dimitrik.fr@xxxxxxxxx> writes: >>> So, why I don't use prepare here: let's say I'm testing the worst >>> stress case :-) Imagine you have thousands of such kind of queries - >>> you cannot prepare all of them! :-) >> >> Thousands? Surely there'll be a dozen or three of most common queries, >> to which you pass different parameters. You can prepare thoseu > > Ok, and if each client just connect to the database, execute each kind > of query just *once* and then disconnect?.. - cost of prepare will > kill performance here if it's not reused at least 10 times within the > same session. In a scenario which looks like this one, what I'm doing is using pgbouncer transaction pooling. Now a new connection from client can be served by an existing backend, which already has prepared your statement. So you first SELECT name FROM pg_prepared_statements; to know if you have to PREPARE or just EXECUTE, and you not only maintain much less running backends, lower fork() calls, but also benefit fully from preparing the statements even when you EXECUTE once per client connection. > Well, I know, we always can do better, and even use stored procedures, > etc. etc. Plain SQL stored procedure will prevent PostgreSQL to prepare your queries, only PLpgSQL functions will force transparent plan caching. But calling this PL will cost about 1ms per call in my tests, so it's not a good solution. It's possible to go as far as providing your own PostgreSQL C module where you PREPARE at _PG_init() time and EXECUTE in a SQL callable function, coupled with pgbouncer it should max out the perfs. But maybe you're not willing to go this far. Anyway, is hammering the server with always the same query your real need or just a simplified test-case? If the former, you'll see there are good ways to theorically obtain better perfs than what you're currently reaching, if the latter I urge you to consider some better benchmarking tools, such as playr or tsung. https://area51.myyearbook.com/trac.cgi/wiki/Playr http://tsung.erlang-projects.org/ http://pgfouine.projects.postgresql.org/tsung.html http://archives.postgresql.org/pgsql-admin/2008-12/msg00032.php Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
