Re: Any better plan for this query?..

I've simply restarted a full test with hashjoin OFF. Until 32
concurrent users things are going well. Then since 32 users response
time is jumping to 20ms, with 64 users it's higher again, and with 256
users reaching 700ms, so TPS is dropping from 5.000 to ~200..

With hashjoin ON it's not happening, and I'm reaching at least 11.000
TPS on fully busy 32 cores.

I should not use prepare/execute as the test conditions should remain "generic".

About scalability issue - there is one on 8.3.7, because on 32 cores
with such kind of load it's using only 50% CPU and not outpassing
6.000 TPS, while 8.4 uses 90% CPU and reaching 11.000 TPS..

On the same time while I'm comparing 8.3 and 8.4 - the response time
is 2 times lower in 8.4, and seems to me the main gain for 8.4 is
here.

I'll publish all details, just need a time :-)

Rgds,
-Dimitri

On 5/7/09, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Thu, May 7, 2009 at 4:20 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
>> Hi Simon,
>>
>> may you explain why REINDEX may help here?.. - database was just
>> created, data loaded, and then indexes were created + analyzed.. What
>> may change here after REINDEX?..
>>
>> With hashjoin disabled was a good try!
>> Running this query "as it" from 1.50ms we move to 0.84ms now,
>> and the plan is here:
>>
>> QUERY
>> PLAN
>> ------------------------------------------------------------------------------------------------------------------------------------------------------
>> Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual
>> time=0.225..0.229 rows=20 loops=1)
>> Sort Key: h.horder
>> Sort Method: quicksort Memory: 30kB
>> -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176) (actual
>> time=0.056..0.205 rows=20 loops=1)
>> Merge Cond: (s.ref = h.ref_stat)
>> -> Index Scan using stat_ref_idx on stat s
>> (cost=0.00..49.25 rows=1000 width=45) (actual time=0.012..0.079
>> rows=193 loops=1)
>> -> Sort (cost=4345.89..4351.72 rows=2329 width=135) (actual
>> time=0.041..0.043 rows=20 loops=1)
>> Sort Key: h.ref_stat
>> Sort Method: quicksort Memory: 30kB
>> -> Index Scan using history_ref_idx on history h
>> (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.013..0.024
>> rows=20 loops=1)
>> Index Cond: (ref_object = '0000000001'::bpchar)
>> Total runtime: 0.261 ms
>> (12 rows)
>>
>> Curiously planner expect to run it in 0.26ms
>>
>> Any idea why planner is not choosing this plan from the beginning?..
>> Any way to keep this plan without having a global or per sessions
>> hashjoin disabled?..
>
> can you work prepared statements into your app? turn off hash join,
> prepare the query, then turn it back on.
>
> merlin
>