Re: Any better plan for this query?..

It's absolutely great!
it'll not help here because a think time is 0.
but for any kind of solution with a spooler it's a must to try!

Rgds,
-Dimitri

On 5/13/09, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> 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