Re: Any better plan for this query?..

On 5/18/09 3:32 PM, "Dimitri" <dimitrik(dot)fr(at)gmail(dot)com> wrote:

> On 5/18/09, Scott Carey <scott(at)richrelevance(dot)com> wrote:
>> Great data Dimitri!'
>
> Thank you! :-)
>
>>
>> I see a few key trends in the poor scalability:
>>
>> The throughput scales roughly with %CPU fairly well. But CPU used doesn't
>> go past ~50% on the 32 core tests. This indicates lock contention.
>>
>
> You should not look on #1 STATs, but on #2 - they are all with the
> latest "fixes" - on all of them CPU is used well (90% in pic on
> 32cores).
> Also, keep in mind these cores are having 2 threads, and from Solaris
> point of view they are seen as CPU (so 64 CPU) and %busy is accounted
> as for 64 CPU
>

Well, if the CPU usage is actually higher, then it might not be lock waiting
-- it could be spin locks or context switches or cache coherency overhead.
Postgres may also not be very SMT friendly, at least on the hardware tested
here.

(what was the context switch rate? I didn't see that in the data, just
mutex spins).

The scalability curve is definitely showing something. Prepared statements
were tried, as were most of the other suggestions other than one:

What happens if the queries are more complicated (say, they take 15ms server
side with a more complicated plan required)? That is a harder question to
answer