Re: Any better plan for this query?..

Although nobody wants to support it, he should try the patch that Jignesh K.
Shah (from Sun) proposed that makes ProcArrayLock lighter-weight. If it
makes 32 cores much faster, then we have a smoking gun.

Although everyone here is talking about this as an 'unoptimal' solution, the
fact is there is no evidence that a connection pooler will fix the
scalability from 16 > 32 cores.
Certainly a connection pooler will help most results, but it may not fix the
scalability problem.

A question for Dimitri:
What is the scalability from 16 > 32 cores at the 'peak' load that occurs
near 2x the CPU count? Is it also poor? If this is also poor, IMO the
community here should not be complaining about this unopimal case -- a
connection pooler at that stage does little and prepared statements will
increase throughput but not likely alter scalability.

If that result scales, then the short term answer is a connection pooler.

In the tests that Jingesh ran -- making the ProcArrayLock faster helped the
case where connections = 2x the CPU core count quite a bit.

The thread about the CPU scalability is "Proposal of tunable fix for
scalability of 8.4", originally posted by "Jignesh K. Shah"
<J(dot)K(dot)Shah(at)Sun(dot)COM>, March 11 2009.

It would be very useful to see results of this benchmark with:
1. A Connection Pooler
2. Jignesh's patch
3. Prepared statements

#3 is important, because prepared statements are ideal for queries that
perform well with low statistics_targets, and not ideal for those that
require high statistics targets. Realistically, an app won't have more than
a couple dozen statement forms to prepare. Setting the default statistics
target to 5 is just a way to make some other query perform bad.

On 5/12/09 10:53 AM, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com> wrote:

> Andres Freund escribió:
>
>> Naturally it would still be nice to be good in this not optimal workload...
>
> I find it hard to justify wasting our scarce development resources into
> optimizing such a contrived workload.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>