From: André Volpato <andre.volpato@ecomtecnologia.com.br>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres not using array
Date: Thu, 21 Aug 2008 10:53:33 -0300
Message-ID: <48AD735D.6040403@ecomtecnologia.com.br> (raw)
In-Reply-To: <48AC8292.5000708@ecomtecnologia.com.br>
References: <48AC8292.5000708@ecomtecnologia.com.br>
André Volpato escreveu:
>
> David Wilson escreveu:
>> On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
>> <andre.volpato@ecomtecnologia.com.br> wrote:
>>
>>
>>> The CPU is 100% used since a few hours ago. Can anyone tell why?
>>>
>>
>> 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.
>>
>
> 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
only for a short time...
So, what is slowing down is the CPU (Intel(R) Pentium(R) Dual CPU
E2160 @ 1.80GHz)
In practice, I have noticed that dual 1.8 is worse than single 3.0. We
have another server wich
is a Pentium D 3.0 GHz, that runs faster.
Explain output:
HashAggregate (cost=19826.23..19826.96 rows=73 width=160) (actual
time=11826.754..11826.754 rows=0 loops=1)
-> Subquery Scan b2 (cost=19167.71..19817.21 rows=722 width=160)
(actual time=11826.752..11826.752 rows=0 loops=1)
Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,
'qtdI'::text, 'P'::bpchar) >= 1::numeric)
-> Limit (cost=19167.71..19248.89 rows=2165 width=48) (actual
time=415.157..621.043 rows=28923 loops=1)
-> HashAggregate (cost=19167.71..19248.89 rows=2165
width=48) (actual time=415.155..593.309 rows=28923 loops=1)
-> Bitmap Heap Scan on bds_beneficiario b
(cost=832.53..18031.61 rows=56805 width=48) (actual time=68.259..160.912
rows=56646 loops=1)
Recheck Cond: ((benef_referencia >= 200805)
AND (benef_referencia <= 200806))
-> Bitmap Index Scan on ibds_beneficiario2
(cost=0.00..818.33 rows=56805 width=0) (actual time=63.293..63.293
rows=56646 loops=1)
Index Cond: ((benef_referencia >=
200805) AND (benef_referencia <= 200806))
Total runtime: 11827.374 ms
Postgres read the array in less than 1 sec, and the other 10s he takes
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
mostly read, with
a few connections and heavy queryes.
Does it worth a multi-core ?
--
[]´s, ACV
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andre.volpato@ecomtecnologia.com.br
Subject: Re: Postgres not using array
In-Reply-To: <48AD735D.6040403@ecomtecnologia.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox