Re: Any better plan for this query?..

Hi,

Le 13 mai 09 à 18:42, Scott Carey a écrit :
>> will not help, as each client is *not* disconnecting/reconnecting
>> during the test, as well PG is keeping well even 256 users. And TPS
>> limit is reached already on 64 users, don't think pooler will help
>> here.
>
> Actually, it might help a little. Postgres has a flaw that makes
> backends
> block on a lock briefly based on the number of total backends --
> active or
> completely passive. Your tool has some (very small) user-side delay
> and a
> connection pooler would probably allow 64 of your users to
> efficiently 'fit'
> in 48 or so connection pooler slots.

It seems you have think time, and I'm only insisting on what Scott
said, but having thinktime means a connection pool can help. Pgbouncer
is a good choice because it won't even attempt to parse the queries,
and it has a flexible configuration.

>>> 3. Prepared statements
>> yes, I'm preparing this test.

It's possible to use prepared statement and benefit from pgbouncer at
the same time, but up until now it requires the application to test
whether its statements are already prepared at connection time,
because the application is not controlling when pgbouncer is reusing
an existing backend or giving it a fresh one.

As I think I need this solution too, I've coded a PG module to scratch
that itch this morning, and just published it (BSD licenced) on
pgfoundry:
http://preprepare.projects.postgresql.org/README.html
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/

With this module and the proper pgbouncer setup (connect_query='SELECT
prepare_all();') the application has no more to special case the fresh-
backend-nothing-prepared case, it's all transparent, just replace your
SELECT query with its EXECUTE foo(x, y, z) counter part.

I've took the approach to setup the prepared statements themselves
into a table with columns name and statement, this latter one
containing the full PREPARE SQL command. There's a custom variable
preprepare.relation that has to be your table name (shema qualified).
Each statement that you then put in there will get prepared when you
SELECT prepare_all();

Hope this helps, regards,
--
dim