Message-ID: <48AD735D.6040403@ecomtecnologia.com.br>
Date: Thu, 21 Aug 2008 10:53:33 -0300
From: =?ISO-8859-1?Q?Andr=E9_Volpato?=
 <andre.volpato@ecomtecnologia.com.br>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Postgres not using array
References: <48AC8292.5000708@ecomtecnologia.com.br>
In-Reply-To: <48AC8292.5000708@ecomtecnologia.com.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Andr=E9 Volpato escreveu:
>
> David Wilson escreveu:
>> On Wed, Aug 20, 2008 at 2:30 PM, Andr=E9 Volpato
>> <andre.volpato@ecomtecnologia.com.br> wrote:
>>
>> =20
>>> The CPU is 100% used since a few hours ago. Can anyone tell why?
>>>    =20
>>
>> Sounds like you've just got a CPU bound query. The data may even all
>> be in cache.
>>
>> Some information on database size, along with EXPLAIN results for your=

>> queries, would help here.
>>  =20
>
> The query itself runs smoothly, almost with no delay.
>

You where right about the cache.
After some experiences, I noticed that the arrays are being used, but=20
only for a short time...
So, what is slowing down is the CPU (Intel(R) Pentium(R) Dual  CPU =20
E2160  @ 1.80GHz)

In practice, I have noticed that dual 1.8 is worse than single 3.0. We=20
have another server wich
is a Pentium D 3.0 GHz, that runs faster.

Explain output:
 HashAggregate  (cost=3D19826.23..19826.96 rows=3D73 width=3D160) (actual=
=20
time=3D11826.754..11826.754 rows=3D0 loops=3D1)
   ->  Subquery Scan b2  (cost=3D19167.71..19817.21 rows=3D722 width=3D16=
0)=20
(actual time=3D11826.752..11826.752 rows=3D0 loops=3D1)
         Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,=20
'qtdI'::text, 'P'::bpchar) >=3D 1::numeric)
         ->  Limit  (cost=3D19167.71..19248.89 rows=3D2165 width=3D48) (a=
ctual=20
time=3D415.157..621.043 rows=3D28923 loops=3D1)
               ->  HashAggregate  (cost=3D19167.71..19248.89 rows=3D2165 =

width=3D48) (actual time=3D415.155..593.309 rows=3D28923 loops=3D1)
                     ->  Bitmap Heap Scan on bds_beneficiario b =20
(cost=3D832.53..18031.61 rows=3D56805 width=3D48) (actual time=3D68.259..=
160.912=20
rows=3D56646 loops=3D1)
                           Recheck Cond: ((benef_referencia >=3D 200805) =

AND (benef_referencia <=3D 200806))
                           ->  Bitmap Index Scan on ibds_beneficiario2 =20
(cost=3D0.00..818.33 rows=3D56805 width=3D0) (actual time=3D63.293..63.29=
3=20
rows=3D56646 loops=3D1)
                                 Index Cond: ((benef_referencia >=3D=20
200805) AND (benef_referencia <=3D 200806))
 Total runtime: 11827.374 ms

Postgres read the array in less than 1 sec, and the other 10s he takes=20
100% of CPU usage,
wich is, in this case, one of the two cores at 1.8GHz.

I am a bit confused about what CPU is best for Postgres. Our apps is=20
mostly read, with
a few connections and heavy queryes.
Does it worth a multi-core ?

--=20

[]=B4s, ACV